CREATE DATABASE IF NOT EXISTS erpticket;
USE erpticket;

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)
);

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());
