-- ==============================================================
-- AMI Database Schema for msdmikim_ami
-- Institut Kesehatan Immanuel - Sistem Audit Mutu Internal
-- ==============================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- Drop existing tables (reverse dependency order)
DROP TABLE IF EXISTS rtm_attendees;
DROP TABLE IF EXISTS rtm_evidence_photos;
DROP TABLE IF EXISTS rtm_action_items;
DROP TABLE IF EXISTS checklist_documents;
DROP TABLE IF EXISTS audit_assignment_members;
DROP TABLE IF EXISTS audit_assignment_standards;
DROP TABLE IF EXISTS findings;
DROP TABLE IF EXISTS documents;
DROP TABLE IF EXISTS checklist_items;
DROP TABLE IF EXISTS rtm_meetings;
DROP TABLE IF EXISTS audit_assignments;
DROP TABLE IF EXISTS standards;
DROP TABLE IF EXISTS audit_cycles;
DROP TABLE IF EXISTS unit_kerja;
DROP TABLE IF EXISTS prodis;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS users;

-- ==============================================================
-- CORE TABLES
-- ==============================================================

-- Users
CREATE TABLE users (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) DEFAULT 'admin123',
    role ENUM('SPM', 'AUDITOR', 'AUDITEE', 'PIMPINAN') NOT NULL,
    unit VARCHAR(255),
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Cycles
CREATE TABLE audit_cycles (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    year INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT FALSE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_year (year),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Standards
CREATE TABLE standards (
    id VARCHAR(50) PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Checklist Items
CREATE TABLE checklist_items (
    id VARCHAR(50) PRIMARY KEY,
    standard_id VARCHAR(50) NOT NULL,
    category VARCHAR(255) NOT NULL,
    question TEXT NOT NULL,
    scope ENUM('SEMUA', 'PRODI', 'UNIT_KERJA') DEFAULT 'SEMUA',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (standard_id) REFERENCES standards(id) ON DELETE CASCADE,
    INDEX idx_standard (standard_id),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Assignments
CREATE TABLE audit_assignments (
    id VARCHAR(50) PRIMARY KEY,
    cycle_id VARCHAR(50) NOT NULL,
    unit VARCHAR(255) NOT NULL,
    auditor_id VARCHAR(50) NOT NULL,
    auditor_name VARCHAR(255) NOT NULL,
    auditee_id VARCHAR(50) NOT NULL,
    date DATE NOT NULL,
    start_time VARCHAR(20),
    duration VARCHAR(50),
    location VARCHAR(255),
    status ENUM('Scheduled', 'In Progress', 'Pending LPM Review', 'Pending Auditee Review', 'Completed') DEFAULT 'Scheduled',
    scope TEXT,
    report_status ENUM('Draft', 'Pending LPM Approval', 'Pending Auditee Approval', 'Final', 'Rejected') DEFAULT 'Draft',
    auditor_signature JSON,
    auditee_signature JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (cycle_id) REFERENCES audit_cycles(id) ON DELETE CASCADE,
    FOREIGN KEY (auditor_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (auditee_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_cycle (cycle_id),
    INDEX idx_auditor (auditor_id),
    INDEX idx_auditee (auditee_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Assignment Standards (Many-to-Many)
CREATE TABLE audit_assignment_standards (
    assignment_id VARCHAR(50) NOT NULL,
    standard_id VARCHAR(255) NOT NULL,
    PRIMARY KEY (assignment_id, standard_id),
    FOREIGN KEY (assignment_id) REFERENCES audit_assignments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Assignment Members
CREATE TABLE audit_assignment_members (
    assignment_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    PRIMARY KEY (assignment_id, user_id),
    FOREIGN KEY (assignment_id) REFERENCES audit_assignments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Findings
CREATE TABLE findings (
    id VARCHAR(50) PRIMARY KEY,
    audit_id VARCHAR(50) NOT NULL,
    checklist_item_id VARCHAR(50),
    type ENUM('Mayor', 'Minor', 'Observasi', 'Peluang Perbaikan') NOT NULL,
    category VARCHAR(255) NOT NULL,
    severity ENUM('Critical', 'High', 'Medium', 'Low') NOT NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT NOT NULL,
    evidence TEXT,
    root_cause TEXT,
    recommendation TEXT,
    status ENUM('Open', 'Responded', 'Pending Verification', 'Verified', 'Closed') DEFAULT 'Open',
    auditee_response TEXT,
    action_plan JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (audit_id) REFERENCES audit_assignments(id) ON DELETE CASCADE,
    FOREIGN KEY (checklist_item_id) REFERENCES checklist_items(id) ON DELETE SET NULL,
    INDEX idx_audit (audit_id),
    INDEX idx_type (type),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documents
CREATE TABLE documents (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    url TEXT NOT NULL,
    category ENUM('General', 'Audit', 'Evidence') DEFAULT 'General',
    uploaded_by VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    description TEXT,
    related_standard_id VARCHAR(50),
    visibility ENUM('public', 'private') DEFAULT 'private',
    owner_id VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (related_standard_id) REFERENCES standards(id) ON DELETE SET NULL,
    INDEX idx_category (category),
    INDEX idx_owner (owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Checklist Documents
CREATE TABLE checklist_documents (
    checklist_item_id VARCHAR(50) NOT NULL,
    document_id VARCHAR(50) NOT NULL,
    PRIMARY KEY (checklist_item_id, document_id),
    FOREIGN KEY (checklist_item_id) REFERENCES checklist_items(id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings (Single Row)
CREATE TABLE settings (
    id INT PRIMARY KEY DEFAULT 1,
    site_name VARCHAR(255) DEFAULT 'Sistem AMI',
    site_description VARCHAR(500) DEFAULT 'Sistem Penjaminan Mutu Internal',
    contact_email VARCHAR(255) DEFAULT 'spm@iki.ac.id',
    institution_name VARCHAR(255) DEFAULT 'Institut Kesehatan Immanuel',
    institution_address TEXT,
    institution_website VARCHAR(255),
    logo_url TEXT,
    login_background_url TEXT,
    max_file_size_mb INT DEFAULT 10,
    allowed_file_types VARCHAR(255) DEFAULT 'pdf,doc,docx,jpg,png,xls,xlsx',
    gemini_api_key TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prodis
CREATE TABLE prodis (
    id VARCHAR(50) PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Unit Kerja
CREATE TABLE unit_kerja (
    id VARCHAR(50) PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    type ENUM('Lembaga', 'Fakultas', 'Jurusan', 'Bagian', 'Unit', 'Unit Kerja') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RTM Meetings
CREATE TABLE rtm_meetings (
    id VARCHAR(50) PRIMARY KEY,
    cycle_id VARCHAR(50) NOT NULL,
    date DATE NOT NULL,
    location VARCHAR(255) NOT NULL,
    agenda TEXT NOT NULL,
    status ENUM('Scheduled', 'Completed') DEFAULT 'Scheduled',
    standard_ids TEXT NULL,
    unit_assignment_ids TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (cycle_id) REFERENCES audit_cycles(id) ON DELETE CASCADE,
    INDEX idx_cycle (cycle_id),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RTM Action Items
CREATE TABLE rtm_action_items (
    id VARCHAR(50) PRIMARY KEY,
    meeting_id VARCHAR(50) NOT NULL,
    task TEXT NOT NULL,
    pic VARCHAR(255) NOT NULL,
    deadline DATE NOT NULL,
    status ENUM('Open', 'In Progress', 'Done') DEFAULT 'Open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES rtm_meetings(id) ON DELETE CASCADE,
    INDEX idx_meeting (meeting_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RTM Evidence Photos
CREATE TABLE rtm_evidence_photos (
    meeting_id VARCHAR(50) NOT NULL,
    photo_url TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES rtm_meetings(id) ON DELETE CASCADE,
    INDEX idx_meeting (meeting_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RTM Attendees
CREATE TABLE rtm_attendees (
    meeting_id VARCHAR(50) NOT NULL,
    attendee_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (meeting_id, attendee_name),
    FOREIGN KEY (meeting_id) REFERENCES rtm_meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================
-- INITIAL DATA FROM constants.ts
-- ==============================================================

-- Users
INSERT INTO users (id, name, username, password, role, unit, email) VALUES
('1', 'Dr. Admin SPM', 'admin', 'admin123', 'SPM', 'Lembaga Penjaminan Mutu', 'spm@iki.ac.id'),
('2', 'Budi Auditor', 'auditor', 'auditor123', 'AUDITOR', 'Fakultas Keperawatan', 'budi@iki.ac.id'),
('3', 'Siti Auditee', 'auditee', 'auditee123', 'AUDITEE', 'LP2M', 'lp2m@iki.ac.id'),
('4', 'Prof. Pimpinan', 'pimpinan', 'pimpinan123', 'PIMPINAN', 'Rektorat', 'rektor@iki.ac.id'),
('5', 'Ani Auditor', 'auditor2', 'auditor123', 'AUDITOR', 'Fakultas Farmasi', 'ani@iki.ac.id'),
('6', 'Dewi Auditee', 'auditee2', 'auditee123', 'AUDITEE', 'Fakultas Keperawatan', 'fkep@iki.ac.id'),
('99', 'Bambang Farmasi', 'auditee3', 'auditee123', 'AUDITEE', 'Fakultas Farmasi', 'farmasi@iki.ac.id');

-- Audit Cycles  
INSERT INTO audit_cycles (id, name, year, start_date, end_date, is_active, description) VALUES
('c1', 'Siklus AMI 2024', 2024, '2024-01-01', '2024-12-31', FALSE, 'Siklus Terdahulu'),
('c2', 'Siklus AMI 2025', 2025, '2025-01-01', '2025-12-31', TRUE, 'Audit Mutu Internal Tahunan');

-- Standards
INSERT INTO standards (id, code, name, description) VALUES
('s4', 'SM', 'Standar Melampaui', 'Standar Mutu Internal IKI (98 Item)'),
('s1', 'SPN', 'Standar Penelitian', 'Standar Nasional Penelitian (83 Item)'),
('s2', 'SPM', 'Standar Pengabdian Masyarakat', 'Standar Nasional PkM (41 Item)'),
('s3', 'SP', 'Standar Pendidikan', 'Standar Nasional Pendidikan (78 Item)');

-- Checklist Items (Sample - add more as needed)
INSERT INTO checklist_items (id, standard_id, category, question) VALUES
('cl-sm-1', 's4', 'SM-I: Standar Bimbingan Akademik', 'Apakah ada SK dosen pembimbingan akademik/SK dosen wali?'),
('cl-sm-2', 's4', 'SM-I: Standar Bimbingan Akademik', 'Apakah SK dosen pembimbingan akademik/SK dosen wali dikeluarkan maksimal 2 minggu sebelum jadwal perkuliahan semester dimulai?'),
('cl-sm-3', 's4', 'SM-I: Standar Bimbingan Akademik', 'Apakah semua dosen di prodi sudah menjadi dosen pembimbing akademik/dosen wali?'),
('cl-sm-12', 's4', 'SM-II: Standar Identitas', 'Apakah ada pedoman perumusan dan penyusunan visi misi institusi dan prodi?'),
('cl-spn-1', 's1', 'SPN-I: Standar Hasil Penelitian', 'Apakah LP2M memiliki SK Ketua tentang Pedoman Penelitian?'),
('cl-spn-2', 's1', 'SPN-I: Standar Hasil Penelitian', 'Apakah LP2M memiliki pedoman hasil penelitian?');

-- Audit Assignments
INSERT INTO audit_assignments (id, cycle_id, unit, auditor_id, auditor_name, auditee_id, date, start_time, duration, location, status, scope, report_status) VALUES
('a1', 'c2', 'S1 Keperawatan', '2', 'Budi Auditor', '6', '2025-08-15', '09:00', '2 Jam', 'Ruang Rapat Fkep', 'Scheduled', 'Audit Standar Melampaui (SM-I)', 'Draft'),
('a2', 'c2', 'Lembaga Penelitian & Pengabdian Masyarakat', '5', 'Ani Auditor', '3', '2025-08-20', '10:00', '3 Jam', 'Ruang LP2M', 'In Progress', 'Audit Standar Penelitian', 'Draft');

-- Assignment Members
INSERT INTO audit_assignment_members (assignment_id, user_id) VALUES
('a1', '5'),
('a2', '2');

-- Assignment Standards
INSERT INTO audit_assignment_standards (assignment_id, standard_id) VALUES
('a1', 'SM-I: Standar Bimbingan Akademik'),
('a2', 'SPN-I: Standar Hasil Penelitian');

-- Findings
INSERT INTO findings (id, audit_id, type, category, severity, title, description, status, recommendation) VALUES
('f1', 'a2', 'Minor', 'SPN-I: Standar Hasil Penelitian', 'Low', 'Kurangnya dokumentasi hasil penelitian', 'Beberapa laporan penelitian dosen belum terarsip dengan rapi di repository.', 'Open', 'Lakukan digitalisasi arsip laporan penelitian dan unggah ke sistem.');

-- Documents
INSERT INTO documents (id, name, type, url, category, uploaded_by, date, description, related_standard_id, visibility, owner_id) VALUES
('d1', 'Laporan Kinerja LP2M 2024.pdf', 'PDF', '#', 'General', 'Siti Auditee', '2025-06-01', 'Laporan tahunan kinerja lembaga', NULL, 'private', '3'),
('d2', 'Bukti Absensi Dosen.jpg', 'JPG', '#', 'Evidence', 'Siti Auditee', '2025-06-14', 'Sampel kehadiran dosen semester genap', 's3', 'private', '3'),
('d3', 'Pedoman Penelitian 2025.pdf', 'PDF', '#', 'General', 'Dr. Admin SPM', '2025-01-10', 'Pedoman terbaru sesuai Permendikbud', 's1', 'public', '1'),
('d4', 'SK Rektor No 123.pdf', 'PDF', '#', 'Audit', 'Prof. Pimpinan', '2025-02-01', NULL, 's4', 'public', '4');

-- Settings
INSERT INTO settings (id, site_name, site_description, contact_email, institution_name, institution_address, institution_website, logo_url, login_background_url, max_file_size_mb, allowed_file_types) VALUES
(1, 'Sistem AMI', 'Sistem Penjaminan Mutu Internal', 'spm@iki.ac.id', 'Institut Kesehatan Immanuel', 'Jl. Kopo No. 123, Bandung, Jawa Barat', 'www.iki.ac.id', 'https://cdn-icons-png.flaticon.com/512/2702/2702069.png', 'https://images.unsplash.com/photo-1497366216548-37526070297c?q=80&w=1920&auto=format&fit=crop', 10, 'pdf,doc,docx,jpg,png,xls,xlsx');

-- Prodis
INSERT INTO prodis (id, code, name, is_active) VALUES
('p1', 'S1-KEP', 'S1 Keperawatan', TRUE),
('p2', 'D3-KEP', 'D3 Keperawatan', TRUE),
('p3', 'S1-FAR', 'S1 Farmasi', TRUE),
('p4', 'PROF-NERS', 'Profesi Ners', TRUE);

-- Unit Kerja
INSERT INTO unit_kerja (id, code, name, type, is_active) VALUES
('u1', 'LP2M', 'Lembaga Penelitian & Pengabdian Masyarakat', 'Lembaga', TRUE),
('u2', 'BAAK', 'Biro Administrasi Akademik', 'Bagian', TRUE),
('u3', 'BAUK', 'Biro Administrasi Umum & Keuangan', 'Bagian', TRUE),
('u4', 'UPT-TI', 'UPT Teknologi Informasi', 'Unit', TRUE),
('u5', 'FF', 'Fakultas Farmasi', 'Fakultas', TRUE);

-- RTM Meeting
INSERT INTO rtm_meetings (id, cycle_id, date, location, agenda, status, notes) VALUES
('rtm1', 'c2', '2025-12-01', 'Ruang Rapat Utama', 'RTM Evaluasi Siklus 2025', 'Scheduled', '<p>Pembahasan awal mengenai capaian siklus tahun ini.</p>');

-- RTM Attendees
INSERT INTO rtm_attendees (meeting_id, attendee_name) VALUES
('rtm1', 'Dr. Admin SPM'),
('rtm1', 'Prof. Pimpinan');

-- ==============================================================
-- Schema Created Successfully!
-- ==============================================================