-- CREATE DATABASE IF NOT EXISTS itsm_helpdesk
-- CHARACTER SET utf8mb4
-- COLLATE utf8mb4_unicode_ci;
-- USE itsm_helpdesk;
CREATE TABLE divisions (
    id_divisi INT AUTO_INCREMENT PRIMARY KEY,
    nama_divisi VARCHAR(100) NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    no_telepon VARCHAR(20),
    role ENUM(
        'USER',
        'ADMIN',
        'TEKNISI'
    ) NOT NULL DEFAULT 'USER',
    id_divisi INT NULL,
    status_akun ENUM('AKTIF', 'NONAKTIF') DEFAULT 'AKTIF',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_divisi FOREIGN KEY(id_divisi) REFERENCES divisions(id_divisi)
);

CREATE TABLE teknisi (
    id_teknisi INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL UNIQUE,
    spesialisasi VARCHAR(100),
    status_aktif ENUM(
        'AKTIF',
        'CUTI',
        'NONAKTIF'
    ) DEFAULT 'AKTIF',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_teknisi_user FOREIGN KEY(id_user) REFERENCES users(id_user) ON DELETE CASCADE
);

CREATE TABLE categories (
    id_kategori INT AUTO_INCREMENT PRIMARY KEY,
    nama_kategori VARCHAR(100) NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE priorities (
    id_prioritas INT AUTO_INCREMENT PRIMARY KEY,
    nama_prioritas VARCHAR(50) NOT NULL,
    level INT NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE sla (
    id_sla INT AUTO_INCREMENT PRIMARY KEY,
    nama_sla VARCHAR(100) NOT NULL,
    waktu_respon_jam INT NOT NULL,
    waktu_penyelesaian_jam INT NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE status_ticket (
    id_status INT AUTO_INCREMENT PRIMARY KEY,
    nama_status VARCHAR(50) NOT NULL,
    urutan INT,
    deskripsi TEXT
);

CREATE TABLE tickets (
    id_ticket BIGINT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL,
    id_kategori INT NOT NULL,
    id_prioritas INT NULL,
    id_sla INT NULL,
    id_status INT NOT NULL,
    judul VARCHAR(255) NOT NULL,
    deskripsi TEXT,
    tanggal_dibuat DATETIME DEFAULT CURRENT_TIMESTAMP,
    tanggal_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deadline DATETIME,
    nomor_tiket varchar(100) not null,
    is_closed BOOLEAN DEFAULT FALSE,
    CONSTRAINT fk_ticket_user FOREIGN KEY(id_user) REFERENCES users(id_user),
    CONSTRAINT fk_ticket_kategori FOREIGN KEY(id_kategori) REFERENCES categories(id_kategori),
    CONSTRAINT fk_ticket_status FOREIGN KEY(id_status) REFERENCES status_ticket(id_status)
);

CREATE TABLE ticket_assignments (
    id_assignment BIGINT AUTO_INCREMENT PRIMARY KEY,
    id_ticket BIGINT NOT NULL,
    id_teknisi INT NOT NULL,
    id_status INT NOT NULL,
    tanggal_ditugaskan DATETIME DEFAULT CURRENT_TIMESTAMP,
    tanggal_selesai DATETIME,
    status_penugasan ENUM(
        'DITUGASKAN',
        'DIPROSES',
        'SELESAI',
        'DITOLAK'
    ) DEFAULT 'DITUGASKAN',
    catatan TEXT,
    CONSTRAINT fk_assignment_ticket FOREIGN KEY(id_ticket) REFERENCES tickets(id_ticket) ON DELETE CASCADE,
    CONSTRAINT fk_assignment_teknisi FOREIGN KEY(id_teknisi) REFERENCES teknisi(id_teknisi),
    CONSTRAINT fk_assignment_status FOREIGN KEY(id_status) REFERENCES status_ticket(id_status)
);

CREATE TABLE ticket_comments (
    id_komentar BIGINT AUTO_INCREMENT PRIMARY KEY,
    id_ticket BIGINT NOT NULL,
    id_user INT NOT NULL,
    komentar TEXT NOT NULL,
    tipe ENUM('KOMENTAR', 'UPDATE') DEFAULT 'KOMENTAR',
    tanggal_komentar DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_comment_ticket FOREIGN KEY(id_ticket) REFERENCES tickets(id_ticket) ON DELETE CASCADE,
    CONSTRAINT fk_comment_user FOREIGN KEY(id_user) REFERENCES users(id_user)
);

CREATE TABLE attachments (
    id_lampiran BIGINT AUTO_INCREMENT PRIMARY KEY,
    id_ticket BIGINT NOT NULL,
    id_user INT NOT NULL,
    nama_file VARCHAR(255) NOT NULL,
    tipe_file VARCHAR(50),
    ukuran_file BIGINT,
    tanggal_upload DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_attachment_ticket FOREIGN KEY(id_ticket) REFERENCES tickets(id_ticket) ON DELETE CASCADE,
    CONSTRAINT fk_attachment_user FOREIGN KEY(id_user) REFERENCES users(id_user)
);

CREATE TABLE activity_logs (
    id_log BIGINT AUTO_INCREMENT PRIMARY KEY,
    id_ticket BIGINT NULL,
    id_user INT NOT NULL,
    aktivitas VARCHAR(255) NOT NULL,
    log_type varchar(50) NULL,
    ip_address VARCHAR(45),
    tanggal_aktivitas DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_log_ticket FOREIGN KEY(id_ticket) REFERENCES tickets(id_ticket) ON DELETE
    SET
        NULL,
        CONSTRAINT fk_log_user FOREIGN KEY(id_user) REFERENCES users(id_user)
);

CREATE INDEX idx_ticket_status ON tickets(id_status);

CREATE INDEX idx_ticket_user ON tickets(id_user);

CREATE INDEX idx_ticket_prioritas ON tickets(id_prioritas);

CREATE INDEX idx_ticket_deadline ON tickets(deadline);

CREATE INDEX idx_assignment_ticket ON ticket_assignments(id_ticket);

CREATE INDEX idx_assignment_teknisi ON ticket_assignments(id_teknisi);

CREATE INDEX idx_comment_ticket ON ticket_comments(id_ticket);

CREATE INDEX idx_attachment_ticket ON attachments(id_ticket);

CREATE INDEX idx_log_ticket ON activity_logs(id_ticket);