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

CREATE TABLE IF NOT EXISTS `staff` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `staff_id` VARCHAR(20) NOT NULL UNIQUE,
  `full_name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `personal_email` VARCHAR(150) NOT NULL UNIQUE,
  `official_email` VARCHAR(150) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('staff','admin') NOT NULL DEFAULT 'staff',
  `status` ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `records` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `record_code` VARCHAR(20) NOT NULL UNIQUE,
  `customer_name` VARCHAR(200) NOT NULL,
  `customer_phone` VARCHAR(20) DEFAULT NULL,
  `customer_address` TEXT DEFAULT NULL,
  `service_type` ENUM('NIN','BVN','OTHERS') NOT NULL,
  `service_category` ENUM('New Enrollment','Modification') NOT NULL,
  `service_start_date` DATE NOT NULL,
  `service_status` ENUM('Pending','In Progress','Completed','Rejected') NOT NULL DEFAULT 'Pending',
  `identifier` VARCHAR(50) DEFAULT NULL,
  `tracking_id` VARCHAR(100) DEFAULT NULL,
  `description` TEXT,
  `staff_note` TEXT,
  `slip_name` VARCHAR(255) DEFAULT NULL,
  `created_by_id` VARCHAR(20),
  `created_by_name` VARCHAR(150),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_edited_by_id` VARCHAR(20) DEFAULT NULL,
  `last_edited_by_name` VARCHAR(150) DEFAULT NULL,
  `last_edited_at` DATETIME DEFAULT NULL,
  INDEX `idx_type` (`service_type`),
  INDEX `idx_status` (`service_status`),
  INDEX `idx_tracking` (`tracking_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `record_history` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `record_id` INT NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `staff_id` VARCHAR(20),
  `staff_name` VARCHAR(150),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_record` (`record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_code` VARCHAR(20) NOT NULL UNIQUE,
  `subject` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `record_code` VARCHAR(20) DEFAULT NULL,
  `staff_id` VARCHAR(20) NOT NULL,
  `staff_name` VARCHAR(150) NOT NULL,
  `status` ENUM('open','resolved') NOT NULL DEFAULT 'open',
  `note` TEXT,
  `resolved_by` VARCHAR(150) DEFAULT NULL,
  `resolved_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `staff_id` INT NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_token` (`token_hash`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;