CREATE DATABASE IF NOT EXISTS hazard_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hazard_db;
CREATE TABLE IF NOT EXISTS admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  source_user_id VARCHAR(100) NULL,
  full_name VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin', 'pelapor', 'verifikator') NOT NULL DEFAULT 'pelapor',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  must_change_password TINYINT(1) NOT NULL DEFAULT 0,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_username (username),
  UNIQUE KEY uq_source_user_id (source_user_id)
) ENGINE=InnoDB;

-- Migrasi aman untuk instalasi versi sebelumnya.
ALTER TABLE admin_users MODIFY role VARCHAR(20) NOT NULL DEFAULT 'pelapor';
UPDATE admin_users SET role = 'verifikator' WHERE role = 'operator';
UPDATE admin_users SET role = 'pelapor' WHERE role = 'viewer';
ALTER TABLE admin_users MODIFY role ENUM('admin', 'pelapor', 'verifikator') NOT NULL DEFAULT 'pelapor';
ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS must_change_password TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active;
UPDATE admin_users SET must_change_password = 0;
ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS source_user_id VARCHAR(100) NULL AFTER username;
UPDATE admin_users SET source_user_id = username WHERE role = 'pelapor' AND source_user_id IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_source_user_id ON admin_users (source_user_id);

INSERT INTO admin_users (username, full_name, password_hash, role)
VALUES ('admin', 'Administrator', '$2y$10$YdUajNLGsGL9WW35AJ9rwuvOiNtTSabMBcaVhE.y8pu58kE8BY.Jm', 'admin')
ON DUPLICATE KEY UPDATE username = VALUES(username);

CREATE TABLE IF NOT EXISTS hazard_reports (
  indexID VARCHAR(100) NOT NULL,
  noReg VARCHAR(100) NULL, siteName VARCHAR(150) NULL, userID VARCHAR(100) NULL,
  dateReported DATE NULL, timeReported TIME NULL, departmentID VARCHAR(150) NULL,
  location VARCHAR(255) NULL, reportType VARCHAR(150) NULL, reportTypeDetail VARCHAR(255) NULL,
  identification TEXT NULL, description TEXT NULL, hazardPhoto TEXT NULL, needAction VARCHAR(100) NULL,
  temporaryAction TEXT NULL, correctivePhoto TEXT NULL, correctiveAction TEXT NULL,
  acceptBy VARCHAR(150) NULL, acceptDate DATETIME NULL, followupBy VARCHAR(150) NULL,
  followupDate DATETIME NULL, note TEXT NULL, statusID VARCHAR(100) NULL,
  statusVerification VARCHAR(150) NULL, lastUpdate DATETIME NULL, updatedBy VARCHAR(150) NULL,
  company VARCHAR(150) NULL, pelapor VARCHAR(150) NULL, subDept_pelapor VARCHAR(150) NULL,
  site_pelapor VARCHAR(150) NULL, bulan VARCHAR(50) NULL, roleasID VARCHAR(100) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (indexID), INDEX idx_user (userID), INDEX idx_date (dateReported), INDEX idx_site (siteName),
  INDEX idx_department (departmentID), INDEX idx_status (statusID), INDEX idx_report_type (reportType),
  INDEX idx_location (location), INDEX idx_company (company), INDEX idx_pelapor (pelapor)
) ENGINE=InnoDB;

-- Membuat akun Pelapor untuk data yang sudah ada sebelum fitur akun otomatis dipasang.
INSERT IGNORE INTO admin_users
  (username, source_user_id, full_name, password_hash, role, is_active, must_change_password)
SELECT LEFT(userID, 100), LEFT(userID, 100), COALESCE(NULLIF(MAX(pelapor), ''), userID),
  '$2y$10$Cbpc7LrZJOwV/g8L6EK9ruLjkdu1ur4ebTiE65tgHFyCwulmOu2wq',
  'pelapor', 1, 0
FROM hazard_reports
WHERE userID IS NOT NULL AND TRIM(userID) <> ''
GROUP BY userID;