-- ============================================================
-- NexusIT ITSM Platform — Complete Database Schema
-- MySQL 8.0+ | Run this first, then seed.sql
-- ============================================================

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

USE nexusit_db;

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  username     VARCHAR(50)  NOT NULL UNIQUE,
  email        VARCHAR(150) NOT NULL UNIQUE,
  password     VARCHAR(255) NOT NULL,
  role         ENUM('admin','finance','manager','staff') NOT NULL DEFAULT 'staff',
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  last_login   DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- SETTINGS
-- ============================================================
CREATE TABLE IF NOT EXISTS settings (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key   VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT NOT NULL,
  updated_by    INT UNSIGNED NULL,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- IT INFRASTRUCTURE AMC
-- ============================================================
CREATE TABLE IF NOT EXISTS it_amc (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_no     VARCHAR(20) NOT NULL UNIQUE,
  company_name    VARCHAR(200) NOT NULL,
  contact_person  VARCHAR(100) NOT NULL,
  contact_email   VARCHAR(150),
  contact_phone   VARCHAR(30),
  description     TEXT,
  visit_type      VARCHAR(100),
  start_date      DATE NOT NULL,
  expiry_date     DATE NOT NULL,
  contract_amount DECIMAL(12,2) DEFAULT 0.00,
  status          ENUM('active','expiring','critical','expired') DEFAULT 'active',
  created_by      INT UNSIGNED NOT NULL,
  updated_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- CCTV AMC
-- ============================================================
CREATE TABLE IF NOT EXISTS cctv_amc (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_no     VARCHAR(20) NOT NULL UNIQUE,
  company_name    VARCHAR(200) NOT NULL,
  contact_person  VARCHAR(100) NOT NULL,
  contact_email   VARCHAR(150),
  contact_phone   VARCHAR(30),
  description     TEXT,
  visit_type      VARCHAR(100),
  start_date      DATE NOT NULL,
  expiry_date     DATE NOT NULL,
  contract_amount DECIMAL(12,2) DEFAULT 0.00,
  status          ENUM('active','expiring','critical','expired') DEFAULT 'active',
  created_by      INT UNSIGNED NOT NULL,
  updated_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- WEBSITE AMC
-- ============================================================
CREATE TABLE IF NOT EXISTS website_amc (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_no     VARCHAR(20) NOT NULL UNIQUE,
  company_name    VARCHAR(200) NOT NULL,
  contact_person  VARCHAR(100) NOT NULL,
  contact_email   VARCHAR(150),
  contact_phone   VARCHAR(30),
  description     TEXT,
  hours_package   VARCHAR(100),
  start_date      DATE NOT NULL,
  expiry_date     DATE NOT NULL,
  contract_amount DECIMAL(12,2) DEFAULT 0.00,
  status          ENUM('active','expiring','critical','expired') DEFAULT 'active',
  created_by      INT UNSIGNED NOT NULL,
  updated_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- SOCIAL MEDIA & SEO
-- ============================================================
CREATE TABLE IF NOT EXISTS social_media (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_no     VARCHAR(20) NOT NULL UNIQUE,
  company_name    VARCHAR(200) NOT NULL,
  contact_person  VARCHAR(100) NOT NULL,
  contact_email   VARCHAR(150),
  contact_phone   VARCHAR(30),
  description     TEXT,
  package_name    VARCHAR(100),
  start_date      DATE NOT NULL,
  expiry_date     DATE NOT NULL,
  contract_amount DECIMAL(12,2) DEFAULT 0.00,
  status          ENUM('active','expiring','critical','expired') DEFAULT 'active',
  created_by      INT UNSIGNED NOT NULL,
  updated_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- MICROSOFT EMAIL RENEWAL
-- ============================================================
CREATE TABLE IF NOT EXISTS microsoft_email (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_no       VARCHAR(20) NOT NULL UNIQUE,
  company_name      VARCHAR(200) NOT NULL,
  contact_person    VARCHAR(100) NOT NULL,
  contact_email     VARCHAR(150),
  contact_phone     VARCHAR(30),
  description       TEXT,
  total_licenses    INT UNSIGNED DEFAULT 0,
  cost_per_license  DECIMAL(10,2) DEFAULT 0.00,
  start_date        DATE NOT NULL,
  expiry_date       DATE NOT NULL,
  contract_amount   DECIMAL(12,2) GENERATED ALWAYS AS (total_licenses * cost_per_license) STORED,
  status            ENUM('active','expiring','critical','expired') DEFAULT 'active',
  created_by        INT UNSIGNED NOT NULL,
  updated_by        INT UNSIGNED NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- HOSTING & DOMAIN
-- ============================================================
CREATE TABLE IF NOT EXISTS hosting_domain (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_no     VARCHAR(20) NOT NULL UNIQUE,
  company_name    VARCHAR(200) NOT NULL,
  contact_person  VARCHAR(100) NOT NULL,
  contact_email   VARCHAR(150),
  contact_phone   VARCHAR(30),
  description     TEXT,
  package_name    VARCHAR(100),
  start_date      DATE NOT NULL,
  expiry_date     DATE NOT NULL,
  contract_amount DECIMAL(12,2) DEFAULT 0.00,
  status          ENUM('active','expiring','critical','expired') DEFAULT 'active',
  created_by      INT UNSIGNED NOT NULL,
  updated_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- HR — EMPLOYEES
-- ============================================================
CREATE TABLE IF NOT EXISTS employees (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company         VARCHAR(100) NOT NULL,
  name            VARCHAR(150) NOT NULL,
  company_email   VARCHAR(150),
  personal_email  VARCHAR(150),
  mobile          VARCHAR(30),
  date_of_joining DATE,
  work_mode       ENUM('Online','Onsite') DEFAULT 'Onsite',
  location        ENUM('India','UAE') DEFAULT 'UAE',
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_by      INT UNSIGNED NOT NULL,
  updated_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- HR — EMPLOYEE DOCUMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS employee_documents (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id  INT UNSIGNED NOT NULL,
  doc_type     VARCHAR(100) NOT NULL,
  expiry_date  DATE NULL,
  description  TEXT,
  status       ENUM('active','expiring','expired') DEFAULT 'active',
  created_by   INT UNSIGNED NOT NULL,
  updated_by   INT UNSIGNED NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- HR — COMPANY DOCUMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS company_documents (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company      VARCHAR(100) NOT NULL,
  title        VARCHAR(200) NOT NULL,
  doc_type     VARCHAR(100) NOT NULL,
  expiry_date  DATE NULL,
  description  TEXT,
  status       ENUM('active','expiring','expired') DEFAULT 'active',
  created_by   INT UNSIGNED NOT NULL,
  updated_by   INT UNSIGNED NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- AUDIT LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  username    VARCHAR(50),
  action      ENUM('CREATE','UPDATE','DELETE','LOGIN','LOGOUT') NOT NULL,
  module      VARCHAR(50),
  record_id   INT UNSIGNED,
  old_values  JSON NULL,
  new_values  JSON NULL,
  ip_address  VARCHAR(45),
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- NOTIFICATION LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS notification_logs (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module       VARCHAR(50) NOT NULL,
  contract_id  INT UNSIGNED NOT NULL,
  contract_no  VARCHAR(20),
  company_name VARCHAR(200),
  days_before  INT NOT NULL,
  email_to     VARCHAR(150),
  status       ENUM('sent','failed') DEFAULT 'sent',
  sent_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX idx_it_expiry      ON it_amc(expiry_date);
CREATE INDEX idx_cctv_expiry    ON cctv_amc(expiry_date);
CREATE INDEX idx_web_expiry     ON website_amc(expiry_date);
CREATE INDEX idx_soc_expiry     ON social_media(expiry_date);
CREATE INDEX idx_ms_expiry      ON microsoft_email(expiry_date);
CREATE INDEX idx_host_expiry    ON hosting_domain(expiry_date);
CREATE INDEX idx_emp_company    ON employees(company);
CREATE INDEX idx_edoc_emp       ON employee_documents(employee_id, expiry_date);
CREATE INDEX idx_cdoc_company   ON company_documents(company, expiry_date);
CREATE INDEX idx_audit_module   ON audit_logs(module, created_at);