CREATE DATABASE IF NOT EXISTS erpticket;
USE erpticket;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    user_id VARCHAR(150) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    client_id INT UNSIGNED NULL,
    plant_id INT UNSIGNED NULL,
    role ENUM('customer', 'support') NOT NULL DEFAULT 'customer',
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

CREATE TABLE email_verifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token VARCHAR(100) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    verified_at DATETIME NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_email_verifications_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

CREATE TABLE plants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_plants_client FOREIGN KEY (client_id) REFERENCES clients(id)
);

ALTER TABLE users
    ADD CONSTRAINT fk_users_client FOREIGN KEY (client_id) REFERENCES clients(id),
    ADD CONSTRAINT fk_users_plant FOREIGN KEY (plant_id) REFERENCES plants(id);

CREATE TABLE tickets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_no VARCHAR(30) NOT NULL UNIQUE,
    ticket_date DATE NOT NULL,
    client_id INT UNSIGNED NOT NULL,
    plant_id INT UNSIGNED NOT NULL,
    requester_name VARCHAR(120) NOT NULL,
    requester_user_id VARCHAR(150) NOT NULL,
    requester_email VARCHAR(150) NOT NULL,
    created_by INT UNSIGNED NOT NULL,
    requirement_type ENUM('new', 'issue') NOT NULL,
    issue_type ENUM('form', 'report') NOT NULL,
    screenshot VARCHAR(255) NULL,
    issue_detail TEXT NOT NULL,
    issue_status ENUM('Open', 'Pending', 'Resolved') NOT NULL DEFAULT 'Open',
    issue_viewed_at DATETIME NULL,
    support_screenshot VARCHAR(255) NULL,
    resolved_detail TEXT NULL,
    resolved_by INT UNSIGNED NULL,
    resolved_at DATETIME NULL,
    chargeable ENUM('Yes', 'No') NOT NULL DEFAULT 'No',
    no_of_days DECIMAL(6,2) NULL,
    deployed ENUM('Yes', 'No') NOT NULL DEFAULT 'No',
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_tickets_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_tickets_plant FOREIGN KEY (plant_id) REFERENCES plants(id),
    CONSTRAINT fk_tickets_user FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_tickets_resolved_by FOREIGN KEY (resolved_by) REFERENCES users(id)
);

CREATE TABLE ticket_updates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT UNSIGNED NOT NULL,
    updated_by INT UNSIGNED NOT NULL,
    issue_status ENUM('Open', 'Pending', 'Resolved') NOT NULL,
    resolved_detail TEXT NULL,
    chargeable ENUM('Yes', 'No') NOT NULL DEFAULT 'No',
    no_of_days DECIMAL(6,2) NULL,
    deployed ENUM('Yes', 'No') NOT NULL DEFAULT 'No',
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_ticket_updates_ticket FOREIGN KEY (ticket_id) REFERENCES tickets(id),
    CONSTRAINT fk_ticket_updates_user FOREIGN KEY (updated_by) REFERENCES users(id)
);

INSERT INTO clients (name, created_at, updated_at) VALUES
('ABC Industries', NOW(), NOW()),
('Metro Steels', NOW(), NOW()),
('Sunrise Foods', NOW(), NOW());

INSERT INTO plants (client_id, name, created_at, updated_at) VALUES
(1, 'Ahmedabad Plant', NOW(), NOW()),
(1, 'Pune Plant', NOW(), NOW()),
(2, 'Chennai Unit', NOW(), NOW()),
(3, 'Indore Factory', NOW(), NOW());

INSERT INTO users (name, user_id, email, password_hash, client_id, plant_id, role, is_verified, created_at, updated_at) VALUES
('ERP Support Admin', 'support@erp.local', 'support@erp.local', '$2y$10$D1DRtutoJmF6QSLn.ORx1.KYVQFCfW64igpyg5qW0yzr/kRdqFIWy', 1, 1, 'support', 1, NOW(), NOW()),
('ERP Admin', 'admin@erp.local', 'admin@erp.local', '$2y$10$D1DRtutoJmF6QSLn.ORx1.KYVQFCfW64igpyg5qW0yzr/kRdqFIWy', 1, 1, 'admin', 1, NOW(), NOW());