-- =====================================================================
-- IAMS SEED DATA: Complete System Test Environment
-- Password for ALL users: 'password'
-- (Hash: $2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u8S1HLKpGm)
-- =====================================================================

USE `iams_db`;

-- Disable foreign key checks temporarily for clean insertion
SET FOREIGN_KEY_CHECKS = 0;

-- Clear existing data (Optional: comment out if you want to append)
TRUNCATE TABLE `cap_evidences`;
TRUNCATE TABLE `corrective_action_plans`;
TRUNCATE TABLE `accountability_scores`;
TRUNCATE TABLE `lad_reports`;
TRUNCATE TABLE `closing_meeting_findings`;
TRUNCATE TABLE `closing_meetings`;
TRUNCATE TABLE `query_evidences`;
TRUNCATE TABLE `query_responses`;
TRUNCATE TABLE `audit_queries`;
TRUNCATE TABLE `kka_evidences`;
TRUNCATE TABLE `working_papers`;
TRUNCATE TABLE `opening_meetings`;
TRUNCATE TABLE `engagement_checklists`;
TRUNCATE TABLE `engagements`;
TRUNCATE TABLE `annual_audit_plans`;
TRUNCATE TABLE `users`;
TRUNCATE TABLE `auditable_entities`;
TRUNCATE TABLE `audit_logs`;

-- =====================================================================
-- 1. ROLES (Ensure the 5 mandatory roles exist)
-- =====================================================================
INSERT INTO `roles` (`id`, `role_key`, `role_name`, `description`) VALUES
(1, 'controlling_officer', 'Pegawai Pengawal (KSU)', 'Ultimate responsibility for ministry finances.'),
(2, 'audit_committee', 'Jawatankuasa Audit (PS 3.2)', 'Oversees internal audit function.'),
(3, 'head_internal_audit', 'Ketua Audit Dalam (PS 3.1)', 'Leads the internal audit unit.'),
(4, 'auditor', 'Auditor (Ketua/Pembantu)', 'Executes fieldwork and prepares KKA.'),
(5, 'auditee', 'Auditee (Representative)', 'Department representative responding to Kuiri.');

-- =====================================================================
-- 2. AUDITABLE ENTITIES (Audit Universe)
-- =====================================================================
INSERT INTO `auditable_entities` (`id`, `entity_code`, `entity_name`, `entity_type`, `fin_l`, `fin_i`, `perf_l`, `perf_i`, `comp_l`, `comp_i`, `media_l`, `media_i`, `risk_score`, `risk_band`) VALUES
(1, 'BAHE', 'Bahagian Hal Ehwal Eksekutif', 'Division', 4, 5, 3, 4, 5, 5, 4, 4, 25, 'High'),
(2, 'UNIT KEW', 'Unit Kewangan & Pengurusan', 'Branch', 3, 4, 2, 3, 3, 3, 1, 2, 16, 'High'),
(3, 'UNIT ASET', 'Unit Pengurusan Aset & Stor', 'Branch', 2, 2, 3, 3, 2, 2, 1, 1, 9, 'Medium'),
(4, 'PROJ ICT', 'Projek Transformasi ICT', 'Project', 4, 4, 5, 5, 3, 3, 5, 5, 25, 'High');

-- =====================================================================
-- 3. USERS (5 Roles + 2 Auditees linked to specific entities)
-- =====================================================================
INSERT INTO `users` (`id`, `staff_id`, `email`, `password_hash`, `full_name`, `role_id`, `entity_id`, `is_active`) VALUES
(1, 'KSU001', 'ksu@iams.gov.my', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u8S1HLKpGm', 'YBhg. Datuk KSU', 1, NULL, 1),
(2, 'AUP002', 'aup@iams.gov.my', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u8S1HLKpGm', 'Tan Sri Pengerusi Audit', 2, NULL, 1),
(3, 'KAD003', 'kad@iams.gov.my', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u8S1HLKpGm', 'Dato\' Ketua Audit Dalam', 3, NULL, 1),
(4, 'AUD004', 'auditor@iams.gov.my', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u8S1HLKpGm', 'Encik Auditor Kanan', 4, NULL, 1),
(5, 'BAHE01', 'ppd.bahe@iams.gov.my', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u8S1HLKpGm', 'Puan Penolong Pengarah BAHE', 5, 1, 1),
(6, 'KEW002', 'ppd.kew@iams.gov.my', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2u8S1HLKpGm', 'Encik Penolong Unit Kewangan', 5, 2, 1);

-- =====================================================================
-- 4. ANNUAL AUDIT PLANS (RAT)
-- =====================================================================
INSERT INTO `annual_audit_plans` (`id`, `entity_id`, `target_year`, `planned_quarter`, `lead_auditor_id`, `allocated_man_days`, `status`) VALUES
(1, 1, YEAR(CURDATE()), 'Q1', 4, 15.00, 'APPROVED'),   -- BAHE Audit (Approved by KSU)
(2, 2, YEAR(CURDATE()), 'Q2', 4, 10.00, 'SUBMITTED'),  -- Kewangan Audit (Submitted to Audit Comm)
(3, 4, YEAR(CURDATE()), 'Q3', 4, 20.00, 'DRAFT');      -- ICT Project (Draft)

-- =====================================================================
-- 5. ENGAGEMENTS (Active Audits)
-- =====================================================================
INSERT INTO `engagements` (`id`, `rat_plan_id`, `entity_id`, `engagement_code`, `scope_description`, `start_date`, `end_date`, `status`, `created_by`) VALUES
(1, 1, 1, 'AUDIT-BAHE-2024-01', 'Audit pengurusan kewangan dan perolehan BAHE bagi tahun 2023.', DATE_SUB(CURDATE(), INTERVAL 15 DAY), DATE_ADD(CURDATE(), INTERVAL 15 DAY), 'IN_PROGRESS', 4),
(2, 2, 2, 'AUDIT-KEW-2024-02', 'Audit pematuhan Pekeliling Perbendaharaan di Unit Kewangan.', DATE_ADD(CURDATE(), INTERVAL 30 DAY), DATE_ADD(CURDATE(), INTERVAL 60 DAY), 'PLANNED', 4);

-- =====================================================================
-- 6. ENGAGEMENT CHECKLISTS & OPENING MEETINGS
-- =====================================================================
INSERT INTO `engagement_checklists` (`engagement_id`, `category`, `responses`, `status`, `completed_by`) VALUES
(1, 'CASH', '{"cash_1": "Comply", "cash_2": "Non-Comply", "cash_3": "Comply"}', 'COMPLETED', 4),
(1, 'PROCUREMENT', '{"proc_1": "Non-Comply", "proc_2": "Non-Comply", "proc_3": "Comply"}', 'COMPLETED', 4);

INSERT INTO `opening_meetings` (`engagement_id`, `meeting_date`, `meeting_time`, `venue`, `attendees`, `minutes`, `agreed_timelines`) VALUES
(1, DATE_SUB(CURDATE(), INTERVAL 14 DAY), '10:00:00', 'Bilik Mesyuarat Utama BAHE',
 '[{"name":"Puan Penolong Pengarah BAHE","designation":"PPD","role":"Auditee"},{"name":"Encik Auditor Kanan","designation":"Auditor","role":"Ketua Pasukan"}]',
 'Mesyuarat pembukaan telah bersetuju dengan skop audit. Auditee akan menyediakan dokumen sokongan dalam masa 3 hari.',
 '[{"milestone":"Penyerahan Dokumen Fasa 1","deadline":"' || DATE_ADD(CURDATE(), INTERVAL -10 DAY) || '"},{"milestone":"Temubaha Pengurusan","deadline":"' || DATE_ADD(CURDATE(), INTERVAL -5 DAY) || '"}]');

-- =====================================================================
-- 7. WORKING PAPERS (KKA - 4C Format)
-- =====================================================================
INSERT INTO `working_papers` (`id`, `engagement_id`, `reference_no`, `title`, `kriteria`, `keadaan`, `sebab`, `kesan`, `risk_rating`, `status`, `created_by`) VALUES
(1, 1, 'WP-BAHE-01', 'Ketidakpatuhan Had Kuotasi Perolehan',
 'Pekeliling Perbendaharaan (PP) 3.2: Perolehan bernilai RM10,000 hingga RM50,000 wajib mendapatkan minimum 3 sebut harga bertulis.',
 'Semakan terhadap 5 fail perolehan mendapati 3 fail hanya mempunyai 1 sebut harga, dan 2 fail tiada rekod sebut harga langsung.',
 'Kekurangan kesedaran pegawai mengenai had kewangan terkini dan ketiadaan semakan oleh jawatankuasa perolehan cawangan.',
 'Risiko ketirisan kewangan berjumlah RM45,000 dan potensi pelanggaran peraturan perolehan negara.',
 'HIGH', 'APPROVED', 4),
(2, 1, 'WP-BAHE-02', 'Lewat Pendaftaran Aset Baharu',
 'Pekeliling Perbendaharaan (PP) 6: Semua aset baharu mesti didaftarkan dalam sistem e-DAFTAR dalam tempoh 7 hari dari tarikh diterima.',
 'Sebanyak 15 unit komputer riba yang diterima pada bulan Januari belum dikemaskini dalam sistem e-DAFTAR setakat bulan Mac.',
 'Pegawai aset sedang cuti sakit dan tiada pegawai ganti yang dilantik untuk menguruskan sistem.',
 'Aset bernilai RM60,000 berisiko hilang atau disalahgunakan kerana tiada rekod kawalan semasa.',
 'MEDIUM', 'REVIEWED', 4);

-- =====================================================================
-- 8. AUDIT QUERIES (KUERI) & RESPONSES (MAKLUM BALAS)
-- =====================================================================
INSERT INTO `audit_queries` (`id`, `engagement_id`, `working_paper_id`, `query_ref_no`, `query_text`, `risk_rating`, `deadline_date`, `status`) VALUES
(1, 1, 1, 'KUERI-BAHE-01', 'Sila jelaskan mengapa 3 fail perolehan tidak mematuhi PP 3.2 mengenai minimum 3 sebut harga, dan apakah tindakan pembetulan yang akan diambil?', 'HIGH', DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'ISSUED'), -- OVERDUE!
(2, 1, 2, 'KUERI-BAHE-02', 'Sila kemukakan bukti pendaftaran 15 unit komputer riba tersebut ke dalam sistem e-DAFTAR.', 'MEDIUM', DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'REPLIED');

INSERT INTO `query_responses` (`query_id`, `response_text`, `action_plan`, `responded_by`) VALUES
(2, 'Kami mengakui kelewatan tersebut. Pegawai yang berkenaan telah kembali bertugas.', '15 unit komputer riba telah didaftarkan sepenuhnya pada 15 Mac 2024. Surat akuan dilampirkan.', 5);

-- =====================================================================
-- 9. CLOSING MEETINGS & FINDINGS
-- =====================================================================
INSERT INTO `closing_meetings` (`engagement_id`, `meeting_date`, `venue`, `minutes`) VALUES
(1, DATE_ADD(CURDATE(), INTERVAL 10 DAY), 'Bilik Mesyuarat Utama', 'Auditee bersetuju dengan penemuan WP-BAHE-02 tetapi membantah penemuan WP-BAHE-01 kerana mendapat pekeliling baru.');

INSERT INTO `closing_meeting_findings` (`closing_meeting_id`, `query_id`, `status`, `auditee_comments`) VALUES
((SELECT id FROM closing_meetings WHERE engagement_id = 1), 1, 'DISPUTED', 'Kami telah mendapat pekeliling pengecualian dari Kementerian Kewangan pada bulan Feb.'),
((SELECT id FROM closing_meetings WHERE engagement_id = 1), 2, 'AGREED', 'Kami bersetuju dan telah mengambil tindakan pembetulan.');

-- =====================================================================
-- 10. CORRECTIVE ACTION PLANS (CAP) & ESCALATION TRIGGERS
-- =====================================================================
INSERT INTO `corrective_action_plans` (`id`, `query_id`, `action_description`, `responsible_officer`, `target_date`, `status`) VALUES
-- CAP 1: Overdue by 40 days (Triggers RED Escalation in UI)
(1, 1, 'Menganjurkan taklimat khas Pekeliling Perbendaharaan untuk semua pegawai perolehan.', 'Pn. Penolong Pengarah BAHE', DATE_SUB(CURDATE(), INTERVAL 40 DAY), 'OPEN'),
-- CAP 2: Completed but not verified (Triggers Amber/Warning if target passed)
(2, 2, 'Mengemaskini sistem e-DAFTAR dan melantik pegawai ganti.', 'Encik Penolong Unit Aset', DATE_SUB(CURDATE(), INTERVAL 10 DAY), 'COMPLETED');

-- =====================================================================
-- 11. ACCOUNTABILITY INDEX (AI) SCORES
-- =====================================================================
INSERT INTO `accountability_scores` (`entity_id`, `assessment_year`, `financial_compliance`, `asset_management`, `procurement_compliance`, `overall_percentage`, `star_rating`, `assessed_by`) VALUES
(1, YEAR(CURDATE()), 65.00, 80.00, 45.00, 63.33, 3, 3),
(2, YEAR(CURDATE()), 95.00, 90.00, 92.00, 92.33, 5, 3);

SET FOREIGN_KEY_CHECKS = 1;