
CREATE DATABASE sikompro;
USE sikompro;
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
password VARCHAR(255),
role VARCHAR(20),
gaji_harian DECIMAL(12,2) NOT NULL DEFAULT 0,
foto_profil VARCHAR(255) NULL
);
INSERT INTO users VALUES
(NULL,'admin',NULL,MD5('admin'),'admin',125000,NULL),
(NULL,'pelaksana',NULL,MD5('pelaksana'),'pelaksana',100000,NULL),
(NULL,'teknisi',NULL,MD5('teknisi'),'teknisi',90000,NULL);

CREATE TABLE komplain(
id INT AUTO_INCREMENT PRIMARY KEY,
admin_id INT NULL,
pelaksana_id INT NULL,
teknisi_id INT NULL,
wilayah_id INT NULL,
cluster_id INT NULL,
lokasi VARCHAR(100),
lokasi_detail VARCHAR(150) NULL,
deskripsi_komplain TEXT NULL,
kerusakan INT,
kategori_kerusakan VARCHAR(30) NULL,
waktu INT,
prioritas DOUBLE,
status VARCHAR(50),
foto_before TEXT,
foto_after TEXT,
pelaksana_keterangan TEXT NULL,
hasil_teknisi TEXT NULL,
hasil_pelaksana TEXT NULL,
catatan_teknisi TEXT NULL,
catatan_pelaksana TEXT NULL,
catatan_admin TEXT NULL,
keterangan TEXT NULL,
admin_keterangan TEXT NULL,
tanggal DATE,
tanggal_update DATETIME NULL
);

CREATE TABLE log_aktivitas(
id INT AUTO_INCREMENT PRIMARY KEY,
komplain_id INT NOT NULL,
user_type VARCHAR(20) NOT NULL,
user_id INT NOT NULL,
aktivitas VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE absensi(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
role VARCHAR(20) NULL,
tanggal DATE NOT NULL,
jam_masuk DATETIME NULL,
jam_pulang DATETIME NULL,
latitude VARCHAR(50) NULL,
longitude VARCHAR(50) NULL,
foto VARCHAR(255) NULL,
status VARCHAR(30) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_absensi_user_tanggal (user_id,tanggal)
);

CREATE TABLE wilayah(
id INT AUTO_INCREMENT PRIMARY KEY,
nama VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_wilayah_nama (nama)
);

CREATE TABLE cluster(
id INT AUTO_INCREMENT PRIMARY KEY,
wilayah_id INT NOT NULL,
nama VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_cluster_wilayah (wilayah_id,nama)
);

INSERT INTO wilayah (nama) VALUES
('BSD'),
('Bogor'),
('Medan'),
('Malang');
