CREATE DATABASE IF NOT EXISTS trunks_dashboard;
USE trunks_dashboard;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    phone VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('user','manager','admin') NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS providers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact_email VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS trunks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    provider_id INT,
    FOREIGN KEY (provider_id) REFERENCES providers(id)
);

CREATE TABLE IF NOT EXISTS phone_numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    number VARCHAR(20) UNIQUE NOT NULL,
    status VARCHAR(50) NOT NULL,
    assigned_to INT NULL,
    tracking_number VARCHAR(255),
    maintenance_window VARCHAR(255),
    trunk_id INT,
    type ENUM('user','ivr','conference','fax') NOT NULL,
    call_recording_enabled TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_to) REFERENCES users(id),
    FOREIGN KEY (trunk_id) REFERENCES trunks(id)
);

CREATE TABLE IF NOT EXISTS phone_number_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone_number_id INT NOT NULL,
    user_id INT NOT NULL,
    change_description TEXT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (phone_number_id) REFERENCES phone_numbers(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS extensions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    extension VARCHAR(10) UNIQUE NOT NULL,
    user_id INT,
    phone_number_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (phone_number_id) REFERENCES phone_numbers(id)
);

CREATE TABLE IF NOT EXISTS tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('extension_new','extension_modify','extension_remove','phone_number_request','recording_request','voicemail_request','spam_report','uid_association') NOT NULL,
    details TEXT,
    status ENUM('open','closed') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS voicemail_boxes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    box_number VARCHAR(20) UNIQUE NOT NULL,
    owner_user_id INT NULL,
    FOREIGN KEY (owner_user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS voicemail_box_members (
    box_id INT NOT NULL,
    user_id INT NOT NULL,
    PRIMARY KEY (box_id, user_id),
    FOREIGN KEY (box_id) REFERENCES voicemail_boxes(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS login_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    used TINYINT(1) DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
