CREATE DATABASE IF NOT EXISTS audit_system
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

USE audit_system;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(150) NOT NULL,
  ic VARCHAR(20) NOT NULL UNIQUE,
  emel VARCHAR(150) NOT NULL UNIQUE,
  role ENUM('Pengguna Biasa', 'Admin Audit', 'Pentadbir Sistem') NOT NULL DEFAULT 'Pengguna Biasa',
  jabatan VARCHAR(150) NOT NULL,
  approval_status ENUM('pending', 'active', 'rejected') NOT NULL DEFAULT 'pending',
  account_status ENUM('active', 'inactive') NOT NULL DEFAULT 'inactive',
  deactivated_at DATETIME NULL DEFAULT NULL,
  password VARCHAR(255) NOT NULL,
  reset_token VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS audit_forms (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  perkara VARCHAR(255) NOT NULL,
  tarikh_mula DATE NULL,
  tarikh_akhir DATE NULL,
  remarks TEXT DEFAULT NULL,
  audit_approval_status ENUM('perlu_kemaskini', 'lulus_diaudit') NOT NULL DEFAULT 'perlu_kemaskini',
  created_by INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_forms_user
    FOREIGN KEY (created_by) REFERENCES users(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS audit_form_assignments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  audit_form_id INT UNSIGNED NOT NULL,
  assigned_unit VARCHAR(150) NOT NULL,
  audit_approval_status ENUM('perlu_kemaskini', 'lulus_diaudit') NOT NULL DEFAULT 'perlu_kemaskini',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_assignment_form
    FOREIGN KEY (audit_form_id) REFERENCES audit_forms(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS audit_form_attachments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  audit_form_id INT UNSIGNED NOT NULL,
  attachment_path VARCHAR(255) NOT NULL,
  attachment_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_form_attachment_form
    FOREIGN KEY (audit_form_id) REFERENCES audit_forms(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS user_transaction_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL DEFAULT NULL,
  nama VARCHAR(150) NOT NULL,
  jabatan VARCHAR(150) NOT NULL,
  role VARCHAR(100) NOT NULL,
  operasi VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_transaction_logs_created_at (created_at),
  INDEX idx_user_transaction_logs_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS department_access_permissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  jabatan VARCHAR(150) NOT NULL,
  module_key VARCHAR(80) NOT NULL,
  can_create TINYINT(1) NOT NULL DEFAULT 0,
  can_read TINYINT(1) NOT NULL DEFAULT 0,
  can_update TINYINT(1) NOT NULL DEFAULT 0,
  can_delete TINYINT(1) NOT NULL DEFAULT 0,
  can_rollback TINYINT(1) NOT NULL DEFAULT 0,
  updated_by INT UNSIGNED NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_department_module_access (jabatan, module_key),
  INDEX idx_department_access_jabatan (jabatan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS department_access_permission_rollbacks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  jabatan VARCHAR(150) NOT NULL,
  snapshot LONGTEXT NOT NULL,
  created_by INT UNSIGNED NULL DEFAULT NULL,
  restored_by INT UNSIGNED NULL DEFAULT NULL,
  restored_at DATETIME NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_department_access_rollback_jabatan (jabatan),
  INDEX idx_department_access_rollback_restored (restored_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS user_access_permissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  module_key VARCHAR(80) NOT NULL,
  can_create TINYINT(1) NOT NULL DEFAULT 0,
  can_read TINYINT(1) NOT NULL DEFAULT 0,
  can_update TINYINT(1) NOT NULL DEFAULT 0,
  can_delete TINYINT(1) NOT NULL DEFAULT 0,
  can_rollback TINYINT(1) NOT NULL DEFAULT 0,
  updated_by INT UNSIGNED NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_module_access (user_id, module_key),
  INDEX idx_user_access_user (user_id),
  CONSTRAINT fk_user_access_permissions_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS password_reset_codes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  emel VARCHAR(150) NOT NULL,
  ic VARCHAR(20) NOT NULL,
  code_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_password_reset_codes_user (user_id),
  INDEX idx_password_reset_codes_expires (expires_at),
  CONSTRAINT fk_password_reset_codes_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS notifications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  link VARCHAR(255) DEFAULT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  read_at DATETIME NULL DEFAULT NULL,
  INDEX idx_notifications_user_read (user_id, is_read),
  INDEX idx_notifications_created_at (created_at),
  CONSTRAINT fk_notifications_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS audit_feedbacks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  audit_form_id INT UNSIGNED NOT NULL,
  submitted_by INT UNSIGNED NOT NULL,
  submitted_unit VARCHAR(150) NOT NULL,
  submitted_role VARCHAR(100) NOT NULL,
  assigned_to_user_id INT UNSIGNED NULL DEFAULT NULL,
  status_tindakan ENUM('Perlu Tindakan', 'Dalam Tindakan', 'Selesai') NOT NULL DEFAULT 'Perlu Tindakan',
  ringkasan VARCHAR(255) NOT NULL,
  maklum_balas TEXT NOT NULL,
  catatan_audit TEXT NULL DEFAULT NULL,
  catatan_audit_by INT UNSIGNED NULL DEFAULT NULL,
  catatan_audit_at DATETIME NULL DEFAULT NULL,
  attachment_path VARCHAR(255) NOT NULL,
  attachment_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_audit_feedback_user (audit_form_id, submitted_by),
  CONSTRAINT fk_audit_feedback_form
    FOREIGN KEY (audit_form_id) REFERENCES audit_forms(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_audit_feedback_user
    FOREIGN KEY (submitted_by) REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS audit_feedback_attachments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  audit_feedback_id INT UNSIGNED NOT NULL,
  attachment_path VARCHAR(255) NOT NULL,
  attachment_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_feedback_attachment_feedback
    FOREIGN KEY (audit_feedback_id) REFERENCES audit_feedbacks(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO users
  (nama, ic, emel, role, jabatan, approval_status, account_status, deactivated_at, password)
VALUES
  (
    'Admin Dashboard Audit',
    '900101019998',
    'admin@mainpp.gov.my',
    'Admin Audit',
    'Bahagian Audit Dalam (BAD)',
    'active',
    'active',
    NULL,
    '$2y$10$/.j/L.mE.ddE9SMQDQnYvO3Z.sw9LnST1HtdVQgWLpjqdnEtNdAyO'
  )
ON DUPLICATE KEY UPDATE
  nama = VALUES(nama),
  role = VALUES(role),
  jabatan = VALUES(jabatan),
  approval_status = 'active',
  account_status = 'active',
  deactivated_at = NULL,
  password = VALUES(password);

INSERT INTO users
  (nama, ic, emel, role, jabatan, approval_status, account_status, deactivated_at, password)
VALUES
  (
    'Pentadbir Sistem',
    '900101019997',
    'superadmin@mainpp.gov.my',
    'Pentadbir Sistem',
    'Bahagian Teknologi Maklumat (BTM)',
    'active',
    'active',
    NULL,
    '$2y$10$zeJD2M8KQsMrI6VM7BK.a.pCdH45c4rrmiUskrfNq0tnUzD6nCxa.'
  )
ON DUPLICATE KEY UPDATE
  nama = VALUES(nama),
  role = VALUES(role),
  jabatan = VALUES(jabatan),
  approval_status = 'active',
  account_status = 'active',
  deactivated_at = NULL,
  password = VALUES(password);
