CREATE TABLE IF NOT EXISTS `personnel` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_no` VARCHAR(100) DEFAULT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `department` VARCHAR(255) DEFAULT NULL,
  `position` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(100) DEFAULT NULL,
  `status` VARCHAR(100) DEFAULT NULL,
  `pin` VARCHAR(100) DEFAULT NULL,
  `password` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_no_unique` (`employee_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ppe_inventory` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(255) DEFAULT NULL,
  `brand` VARCHAR(255) DEFAULT NULL,
  `quantity` INT DEFAULT 0,
  `min_stock` INT DEFAULT 0,
  `expiry_date` DATE DEFAULT NULL,
  `received_qty` INT DEFAULT 0,
  `receiving_date` DATE DEFAULT NULL,
  `ppe_code` VARCHAR(100) DEFAULT NULL,
  `storage_location` VARCHAR(255) DEFAULT NULL,
  `supplier_name` VARCHAR(255) DEFAULT NULL,
  `supplier_phone` VARCHAR(100) DEFAULT NULL,
  `supplier_location` VARCHAR(255) DEFAULT NULL,
  `quality` VARCHAR(100) DEFAULT NULL,
  `size` VARCHAR(50) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `stock_entry` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entry_ref` VARCHAR(100) DEFAULT NULL,
  `ppe_id` INT DEFAULT NULL,
  `item_name` VARCHAR(255) DEFAULT NULL,
  `entry_type` VARCHAR(100) DEFAULT NULL,
  `quantity` INT DEFAULT 0,
  `warehouse` VARCHAR(255) DEFAULT NULL,
  `user` VARCHAR(255) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `pin_code` VARCHAR(100) DEFAULT NULL,
  `role` VARCHAR(100) DEFAULT 'user',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_name` VARCHAR(255) NOT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `contact_no` VARCHAR(100) DEFAULT NULL,
  `contact_person` VARCHAR(255) DEFAULT NULL,
  `item_category` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ppe_issuance` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `issue_ref` VARCHAR(100) DEFAULT NULL,
  `personnel_id` INT DEFAULT NULL,
  `personnel_name` VARCHAR(255) DEFAULT NULL,
  `ppe_id` INT DEFAULT NULL,
  `ppe_name` VARCHAR(255) DEFAULT NULL,
  `size` VARCHAR(50) DEFAULT NULL,
  `quantity` INT DEFAULT 0,
  `department` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(100) DEFAULT NULL,
  `issued_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ppe_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_ref` VARCHAR(100) DEFAULT NULL,
  `requester_name` VARCHAR(255) DEFAULT NULL,
  `ppe_name` VARCHAR(255) DEFAULT NULL,
  `quantity` INT DEFAULT 0,
  `status` VARCHAR(100) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `competence_training` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_name` VARCHAR(255) DEFAULT NULL,
  `cert_type` VARCHAR(255) DEFAULT NULL,
  `provider` VARCHAR(255) DEFAULT NULL,
  `expiry_date` DATE DEFAULT NULL,
  `status` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `competence_medical` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_name` VARCHAR(255) DEFAULT NULL,
  `medical_type` VARCHAR(255) DEFAULT NULL,
  `next_due` DATE DEFAULT NULL,
  `status` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `stock_adjustments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `adjustment_ref` VARCHAR(100) DEFAULT NULL,
  `item_name` VARCHAR(255) DEFAULT NULL,
  `type` VARCHAR(100) DEFAULT NULL,
  `old_quantity` INT DEFAULT 0,
  `new_quantity` INT DEFAULT 0,
  `reason` TEXT DEFAULT NULL,
  `user` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `warehouses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `location` VARCHAR(255) DEFAULT NULL,
  `capacity` INT DEFAULT 0,
  `status` VARCHAR(100) DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `goods_received` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier` VARCHAR(255) DEFAULT NULL,
  `item_name` VARCHAR(255) DEFAULT NULL,
  `quantity` INT DEFAULT 0,
  `warehouse` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(100) DEFAULT 'received',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `departments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dept_code` VARCHAR(50) NOT NULL UNIQUE,
  `dept_name` VARCHAR(255) NOT NULL,
  `dept_icon` VARCHAR(100) DEFAULT 'building-2',
  `dept_prefix` VARCHAR(10) DEFAULT 'D',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dept_code_unique` (`dept_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Database Indexes for Performance
CREATE INDEX idx_personnel_employee_no ON personnel(employee_no);
CREATE INDEX idx_personnel_department ON personnel(department);
CREATE INDEX idx_ppe_inventory_category ON ppe_inventory(category);
CREATE INDEX idx_ppe_inventory_created_at ON ppe_inventory(created_at);
CREATE INDEX idx_ppe_issuance_personnel_id ON ppe_issuance(personnel_id);
CREATE INDEX idx_ppe_issuance_ppe_id ON ppe_issuance(ppe_id);
CREATE INDEX idx_ppe_issuance_created_at ON ppe_issuance(issued_at);
CREATE INDEX idx_suppliers_supplier_name ON suppliers(supplier_name);
CREATE INDEX idx_stock_adjustments_item_name ON stock_adjustments(item_name);
CREATE INDEX idx_stock_adjustments_created_at ON stock_adjustments(created_at);
CREATE INDEX idx_goods_received_supplier ON goods_received(supplier);
CREATE INDEX idx_goods_received_created_at ON goods_received(created_at);
CREATE INDEX idx_stock_entry_ppe_id ON stock_entry(ppe_id);
CREATE INDEX idx_stock_entry_created_at ON stock_entry(created_at);
CREATE INDEX idx_users_username ON users(username);

-- Additional indexes for ultra-fast performance
CREATE INDEX idx_personnel_status ON personnel(status);
CREATE INDEX idx_ppe_inventory_quantity ON ppe_inventory(quantity);
CREATE INDEX idx_ppe_issuance_status ON ppe_issuance(status);
CREATE INDEX idx_ppe_requests_status ON ppe_requests(status);
CREATE INDEX idx_stock_entry_entry_type ON stock_entry(entry_type);
CREATE INDEX idx_competence_training_status ON competence_training(status);
CREATE INDEX idx_competence_medical_status ON competence_medical(status);
