CREATE DATABASE IF NOT EXISTS website_tracker CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE website_tracker;

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS content_reminders;
DROP TABLE IF EXISTS request_status_history;
DROP TABLE IF EXISTS request_attachments;
DROP TABLE IF EXISTS request_comments;
DROP TABLE IF EXISTS website_requests;
DROP TABLE IF EXISTS request_types;
DROP TABLE IF EXISTS websites;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS settings;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(30) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    company_id INT UNSIGNED NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
    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,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE websites (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    website_name VARCHAR(150) NOT NULL,
    domain VARCHAR(190) NOT NULL,
    primary_hod_user_id INT UNSIGNED NULL,
    reminder_enabled TINYINT(1) NOT NULL DEFAULT 1,
    reminder_interval_days INT NOT NULL DEFAULT 30,
    last_content_review_date DATE NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_websites_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_websites_hod FOREIGN KEY (primary_hod_user_id) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY uq_websites_domain (domain)
) ENGINE=InnoDB;

CREATE TABLE request_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE website_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_no VARCHAR(30) NOT NULL UNIQUE,
    website_id INT UNSIGNED NOT NULL,
    request_type_id INT UNSIGNED NOT NULL,
    requested_by INT UNSIGNED NOT NULL,
    assigned_to INT UNSIGNED NULL,
    title VARCHAR(200) NOT NULL,
    details TEXT NOT NULL,
    page_url VARCHAR(500) NULL,
    priority ENUM('Low','Normal','High','Urgent') NOT NULL DEFAULT 'Normal',
    status ENUM('Draft','Submitted','IT Review','In Progress','Waiting for HOD','Completed','Closed','Rejected','Correction Required') NOT NULL DEFAULT 'Submitted',
    due_date DATE NULL,
    hod_approval_note TEXT NULL,
    completed_at DATETIME NULL,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_requests_website FOREIGN KEY (website_id) REFERENCES websites(id),
    CONSTRAINT fk_requests_type FOREIGN KEY (request_type_id) REFERENCES request_types(id),
    CONSTRAINT fk_requests_requester FOREIGN KEY (requested_by) REFERENCES users(id),
    CONSTRAINT fk_requests_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    KEY idx_requests_status (status),
    KEY idx_requests_website (website_id),
    KEY idx_requests_requested_by (requested_by),
    KEY idx_requests_assigned_to (assigned_to)
) ENGINE=InnoDB;

CREATE TABLE request_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    comment TEXT NOT NULL,
    is_internal TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_comments_request FOREIGN KEY (request_id) REFERENCES website_requests(id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE request_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    uploaded_by INT UNSIGNED NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_attachments_request FOREIGN KEY (request_id) REFERENCES website_requests(id) ON DELETE CASCADE,
    CONSTRAINT fk_attachments_user FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE request_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    changed_by INT UNSIGNED NOT NULL,
    old_status VARCHAR(50) NULL,
    new_status VARCHAR(50) NOT NULL,
    note VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_status_request FOREIGN KEY (request_id) REFERENCES website_requests(id) ON DELETE CASCADE,
    CONSTRAINT fk_status_user FOREIGN KEY (changed_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE content_reminders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    website_id INT UNSIGNED NOT NULL,
    hod_user_id INT UNSIGNED NOT NULL,
    due_date DATE NOT NULL,
    sent_at DATETIME NULL,
    status ENUM('Pending','Responded','No Response') NOT NULL DEFAULT 'Pending',
    response_type ENUM('Has Updates','No Updates') NULL,
    response_notes TEXT NULL,
    responded_at DATETIME NULL,
    generated_request_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_reminder_website FOREIGN KEY (website_id) REFERENCES websites(id),
    CONSTRAINT fk_reminder_hod FOREIGN KEY (hod_user_id) REFERENCES users(id),
    CONSTRAINT fk_reminder_request FOREIGN KEY (generated_request_id) REFERENCES website_requests(id) ON DELETE SET NULL,
    KEY idx_reminder_status (status),
    KEY idx_reminder_due (due_date)
) ENGINE=InnoDB;

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    message VARCHAR(500) NOT NULL,
    link_url VARCHAR(500) NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    KEY idx_notification_user_read (user_id, is_read)
) ENGINE=InnoDB;

CREATE TABLE settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NULL
) ENGINE=InnoDB;

INSERT INTO roles (name) VALUES ('Admin'),('IT Team'),('HOD');
INSERT INTO request_types (name, description) VALUES
('New Page / Section','Create a new website page or section'),
('Existing Page Change','Change text, images, layout or other existing page content'),
('New Product Page','Add a new product page'),
('Product Technical Details','Add or revise product technical data/specifications'),
('Blog / News','Publish a blog, news item or article'),
('Banner / Campaign','Add or revise a website banner or campaign'),
('Image / Document','Upload or replace website images, brochures, PDFs or documents'),
('SEO / Meta Update','Update title, description, keywords or SEO-related content'),
('Remove / Deactivate Content','Remove or deactivate existing content'),
('Other','Other website request');

INSERT INTO settings (setting_key, setting_value) VALUES
('group_name','Dolphin Group'),
('default_due_days','7'),
('reminder_grace_days','7');