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

CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE institutes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE departments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  institute_id INT UNSIGNED NULL,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE SET NULL
);

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  department_id INT UNSIGNED NULL,
  employee_no VARCHAR(50) UNIQUE NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  phone VARCHAR(40) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE services (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  sla_hours INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE tickets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_no VARCHAR(40) NOT NULL UNIQUE,
  requester_id BIGINT UNSIGNED NOT NULL,
  department_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NULL,
  service_id INT UNSIGNED NULL,
  subject VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  priority ENUM('Low','Medium','High','Critical') NOT NULL DEFAULT 'Medium',
  status ENUM('Pending','Approved','Rejected','Assigned','In Progress','Resolved','Closed') NOT NULL DEFAULT 'Pending',
  assigned_to BIGINT UNSIGNED NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  resolved_at DATETIME NULL,
  closed_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (requester_id) REFERENCES users(id),
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_ticket_status (status),
  INDEX idx_ticket_department (department_id),
  INDEX idx_ticket_assignee (assigned_to)
);

CREATE TABLE ticket_comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  comment TEXT NOT NULL,
  status ENUM('Pending','Approved','Rejected','Assigned','In Progress','Resolved','Closed') NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE attachments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NULL,
  requisition_id BIGINT UNSIGNED NULL,
  damage_item_id BIGINT UNSIGNED NULL,
  uploaded_by BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

CREATE TABLE requisitions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_no VARCHAR(40) NOT NULL UNIQUE,
  department_id INT UNSIGNED NOT NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(180) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  justification TEXT NOT NULL,
  status ENUM('Pending','Forwarded to Store','Forwarded to Admin','Approved','Rejected','Issued','Closed') NOT NULL DEFAULT 'Pending',
  forwarded_to ENUM('Store','Admin','Concerned Department') NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  review_remark TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (requested_by) REFERENCES users(id),
  FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_req_status (status)
);

CREATE TABLE inventory_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  asset_tag VARCHAR(80) NOT NULL UNIQUE,
  item_name VARCHAR(180) NOT NULL,
  category VARCHAR(100) NULL,
  serial_no VARCHAR(120) NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  condition_status ENUM('New','Good','Repair','Damaged','Disposed') NOT NULL DEFAULT 'New',
  store_department_id INT UNSIGNED NULL,
  assigned_department_id INT UNSIGNED NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  purchase_date DATE NULL,
  warranty_end DATE NULL,
  location VARCHAR(180) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (store_department_id) REFERENCES departments(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_department_id) REFERENCES departments(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_inventory_department (assigned_department_id),
  INDEX idx_inventory_condition (condition_status)
);

CREATE TABLE damage_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inventory_item_id BIGINT UNSIGNED NULL,
  department_id INT UNSIGNED NOT NULL,
  reported_by BIGINT UNSIGNED NOT NULL,
  damage_type VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  status ENUM('Pending','Forwarded to Store','Under Review','Repair','Replacement','Closed','Rejected') NOT NULL DEFAULT 'Pending',
  store_remark TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id) ON DELETE SET NULL,
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (reported_by) REFERENCES users(id),
  INDEX idx_damage_status (status)
);

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id BIGINT UNSIGNED NULL,
  description TEXT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_user (user_id),
  INDEX idx_audit_created (created_at)
);

INSERT INTO roles (name, description) VALUES
('Admin','System administrator'),
('Manager','Department manager'),
('Technician','IT technician'),
('Staff','General staff');

INSERT INTO institutes (code,name) VALUES
('IMC','IMC'),('IMCH','IMCH'),('UMS','UMS'),('HOSTEL','Hostel');

INSERT INTO categories (name,description) VALUES
('Hardware','Desktop, laptop, printer and device support'),
('Software','Operating system and application support'),
('Network','LAN, Wi-Fi and connectivity'),
('Account & Access','User account and access issues');

INSERT INTO services (category_id,name,description,sla_hours)
SELECT id,'Desktop / Laptop Support','Hardware troubleshooting',24 FROM categories WHERE name='Hardware';
INSERT INTO services (category_id,name,description,sla_hours)
SELECT id,'Printer Support','Printer setup and troubleshooting',24 FROM categories WHERE name='Hardware';
INSERT INTO services (category_id,name,description,sla_hours)
SELECT id,'Network Connectivity','LAN/Wi-Fi issue',8 FROM categories WHERE name='Network';
INSERT INTO services (category_id,name,description,sla_hours)
SELECT id,'Account Access','Password and access assistance',8 FROM categories WHERE name='Account & Access';