CREATE DATABASE IF NOT EXISTS roxzas_dashboard
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE roxzas_dashboard;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(60) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,

    role ENUM('admin','user') NOT NULL DEFAULT 'user',
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',

    totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
    totp_secret VARCHAR(255) NULL,

    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    ticket_number VARCHAR(40) NOT NULL UNIQUE,

    category VARCHAR(100) NOT NULL,
    subcategory VARCHAR(100) NULL,

    customer_name VARCHAR(150) NOT NULL,
    customer_email VARCHAR(190) NOT NULL,
    company VARCHAR(190) NULL,
    discord VARCHAR(100) NULL,

    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,

    budget VARCHAR(100) NULL,
    timeframe VARCHAR(100) NULL,

    priority ENUM('low','normal','high','urgent')
        NOT NULL DEFAULT 'normal',

    status ENUM(
        'new',
        'in_progress',
        'waiting_customer',
        'customer_reply',
        'answered',
        'closed'
    ) NOT NULL DEFAULT 'new',

    first_response_at DATETIME NULL,
    last_activity_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_created (created_at),
    INDEX idx_customer_email (customer_email)
);

CREATE TABLE ticket_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    ticket_id BIGINT UNSIGNED NOT NULL,

    sender_type ENUM('customer','admin','system') NOT NULL,
    sender_email VARCHAR(190) NULL,

    source ENUM('website','dashboard','email','system')
        NOT NULL DEFAULT 'system',

    subject VARCHAR(255) NULL,
    body MEDIUMTEXT NOT NULL,

    message_id VARCHAR(255) NULL,
    in_reply_to VARCHAR(255) NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_message_ticket
        FOREIGN KEY (ticket_id)
        REFERENCES tickets(id)
        ON DELETE CASCADE,

    INDEX idx_message_ticket (ticket_id),
    INDEX idx_message_id (message_id)
);

CREATE TABLE ticket_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id BIGINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    note TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (ticket_id)
        REFERENCES tickets(id)
        ON DELETE CASCADE,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE activity_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    ticket_id BIGINT UNSIGNED NULL,

    action VARCHAR(100) NOT NULL,
    description TEXT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_activity_created (created_at)
);