SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `survey_responses`;
DROP TABLE IF EXISTS `survey_questions`;
DROP TABLE IF EXISTS `kpi_manual_inputs`;
DROP TABLE IF EXISTS `helpdesk_tickets`;
DROP TABLE IF EXISTS `maintenance_assessments`;
DROP TABLE IF EXISTS `maintenance_history`;
DROP TABLE IF EXISTS `ticket_updates`;
DROP TABLE IF EXISTS `tickets`;
DROP TABLE IF EXISTS `sla_incidents`;
DROP TABLE IF EXISTS `vendors`;
DROP TABLE IF EXISTS `assets`;
DROP TABLE IF EXISTS `users`;

SET FOREIGN_KEY_CHECKS=1;

-- =================================================================
-- User & Authentication Table
-- =================================================================

CREATE TABLE IF NOT EXISTS users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    role ENUM('admin', 'petugas') NOT NULL DEFAULT 'petugas',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a default admin user. Password is 'admin'
INSERT INTO `users` (`username`, `password`, `nama_lengkap`, `role`) VALUES
('admin', '$2y$10$T.x/y5b.bVzC23GkO4a.J.p/C6aF4jJ3k.8rE.Ex.Q4wY8N9/L.6G', 'Administrator', 'admin'),
('petugas1', '$2y$10$mBq6z/P8c/ggnIAV5pG3A.P6n99A5p.YxZ.zY.H/C3fB/jX.5r.2K', 'Petugas Lapangan 1', 'petugas');

CREATE TABLE IF NOT EXISTS assets (
    id_aset INT AUTO_INCREMENT PRIMARY KEY,
    nama_perangkat VARCHAR(255) NOT NULL,
    kategori ENUM('PC', 'Laptop', 'Printer', 'Switch', 'Server', 'Monitor', 'Lain-lain') NOT NULL,
    serial_number VARCHAR(255) UNIQUE NOT NULL,
    lokasi VARCHAR(255) NOT NULL,
    pic VARCHAR(255) NOT NULL,
    tanggal_pembelian DATE NOT NULL,
    nilai_pembelian DECIMAL(15, 2) NOT NULL,
    life_cycle_years INT NOT NULL,
    status_aset ENUM('Aktif', 'Rusak', 'Dismantle', 'Maintenance') NOT NULL DEFAULT 'Aktif',
    qr_code TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Optional: Create an index for faster lookups on frequently queried columns
CREATE INDEX idx_kategori ON assets (kategori);
CREATE INDEX idx_lokasi ON assets (lokasi);
CREATE INDEX idx_status_aset ON assets (status_aset);

-- Table for maintenance history
CREATE TABLE IF NOT EXISTS maintenance_history (
    id_maintenance INT AUTO_INCREMENT PRIMARY KEY,
    id_aset INT NOT NULL,
    tanggal_maintenance DATE NOT NULL,
    tindakan VARCHAR(255) NOT NULL,
    catatan_teknisi TEXT,
    nama_teknisi VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_aset) REFERENCES assets(id_aset) ON DELETE CASCADE
);

CREATE INDEX idx_id_aset_history ON maintenance_history (id_aset);

-- Table for detailed maintenance assessments
CREATE TABLE IF NOT EXISTS maintenance_assessments (
    id_assessment INT AUTO_INCREMENT PRIMARY KEY,
    id_maintenance INT NOT NULL,
    asset_category VARCHAR(50) NOT NULL, -- e.g., 'PC', 'Laptop', 'AIO', 'Printer'
    assessment_data JSON NOT NULL,      -- Stores the detailed assessment indicators in JSON format
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_maintenance) REFERENCES maintenance_history(id_maintenance) ON DELETE CASCADE
);

-- =================================================================
-- Ticketing Module Tables
-- =================================================================

-- Table for tickets to external vendors
CREATE TABLE IF NOT EXISTS tickets (
    id_ticket INT AUTO_INCREMENT PRIMARY KEY,
    vendor_system VARCHAR(255) NOT NULL,
    request_type VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    priority ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL DEFAULT 'Medium',
    status ENUM('Submitted', 'In Progress by Vendor', 'Ready for Testing', 'Done', 'On Hold') NOT NULL DEFAULT 'Submitted',
    requester VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_ticket_status ON tickets (status);
CREATE INDEX idx_ticket_vendor ON tickets (vendor_system);

-- Table for ticket updates and comments
CREATE TABLE IF NOT EXISTS ticket_updates (
    id_update INT AUTO_INCREMENT PRIMARY KEY,
    id_ticket INT NOT NULL,
    update_description TEXT NOT NULL,
    updated_by VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_ticket) REFERENCES tickets(id_ticket) ON DELETE CASCADE
);

CREATE INDEX idx_id_ticket_updates ON ticket_updates (id_ticket);

-- =================================================================
-- SLA & Vendor Module Tables
-- =================================================================

CREATE TABLE IF NOT EXISTS vendors (
    id_vendor INT AUTO_INCREMENT PRIMARY KEY,
    nama_vendor VARCHAR(255) UNIQUE NOT NULL,
    jenis_layanan VARCHAR(255) NOT NULL,
    pic_vendor VARCHAR(255),
    kontak_vendor VARCHAR(255),
    sla_uptime_percentage DECIMAL(5, 2) COMMENT 'e.g., 99.90',
    sla_response_time_minutes INT COMMENT 'e.g., 15'
);

CREATE TABLE IF NOT EXISTS sla_incidents (
    id_incident INT AUTO_INCREMENT PRIMARY KEY,
    id_vendor INT NOT NULL,
    incident_description TEXT NOT NULL,
    waktu_mulai DATETIME NOT NULL,
    waktu_selesai DATETIME,
    durasi_downtime_minutes INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_vendor) REFERENCES vendors(id_vendor) ON DELETE CASCADE
);

CREATE INDEX idx_id_vendor_incidents ON sla_incidents (id_vendor);

-- =================================================================
-- Helpdesk Module Tables
-- =================================================================

CREATE TABLE IF NOT EXISTS helpdesk_tickets (
    id_ticket INT AUTO_INCREMENT PRIMARY KEY,
    asset_id INT NOT NULL,
    reporter_name VARCHAR(255) NOT NULL,
    reporter_contact VARCHAR(255), -- Email or Phone
    problem_description TEXT NOT NULL,
    status ENUM('New', 'In Progress', 'Resolved', 'Closed') NOT NULL DEFAULT 'New',
    priority ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL DEFAULT 'Medium',
    assigned_to_user_id INT NULL, -- Who is working on it, nullable
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    response_at DATETIME NULL,    -- When the first response was made
    resolved_at DATETIME NULL,    -- When the ticket was resolved
    resolution_details TEXT NULL,
    FOREIGN KEY (asset_id) REFERENCES assets(id_aset) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to_user_id) REFERENCES users(id_user) ON DELETE SET NULL
);

CREATE INDEX idx_helpdesk_status ON helpdesk_tickets (status);
CREATE INDEX idx_helpdesk_priority ON helpdesk_tickets (priority);
CREATE INDEX idx_helpdesk_asset_id ON helpdesk_tickets (asset_id);

-- =================================================================
-- KPI Manual Input Table
-- =================================================================

CREATE TABLE IF NOT EXISTS kpi_manual_inputs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kpi_name VARCHAR(255) NOT NULL,
    value TEXT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    input_by_user_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (input_by_user_id) REFERENCES users(id_user) ON DELETE SET NULL,
    UNIQUE (kpi_name, month, year) -- Ensure only one entry per KPI per month/year
);

CREATE INDEX idx_kpi_manual_name_month_year ON kpi_manual_inputs (kpi_name, month, year);

-- =================================================================
-- Customer Satisfaction Survey Module Tables
-- =================================================================

CREATE TABLE IF NOT EXISTS survey_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_text TEXT NOT NULL,
    question_type ENUM('rating_5', 'text') NOT NULL, -- rating_5 for 1-5 scale, text for open-ended
    question_order INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Initial survey questions
INSERT IGNORE INTO `survey_questions` (`id`, `question_text`, `question_type`, `question_order`) VALUES
(1, 'Seberapa puas Anda dengan respons awal kami terhadap masalah Anda?', 'rating_5', 1),
(2, 'Seberapa efektif solusi yang diberikan untuk masalah Anda?', 'rating_5', 2),
(3, 'Bagaimana kualitas layanan teknisi kami secara keseluruhan?', 'rating_5', 3),
(4, 'Apakah ada komentar atau saran lain untuk meningkatkan layanan kami?', 'text', 4);


CREATE TABLE IF NOT EXISTS survey_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    survey_question_id INT NOT NULL,
    response_value TEXT NOT NULL, -- '1'-'5' for rating, actual text for text questions
    -- Optionally link to helpdesk ticket, but for public QR, it might be anonymous.
    -- If we need to link to helpdesk ticket, we might add helpdesk_ticket_id INT NULL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_question_id) REFERENCES survey_questions(id) ON DELETE CASCADE
);

CREATE INDEX idx_survey_question_id ON survey_responses (survey_question_id);


