-- =====================================================
-- Education For Life - Single-Organization Database
-- =====================================================

-- IMPORTANT: Recommended MySQL / MariaDB versions
-- - MySQL 5.7+ or MariaDB 10.2+ (JSON columns, generated columns, and utf8mb4 support)
-- If you run on older servers, consider changing `JSON` columns to TEXT and removing generated columns.

-- Safe import wrapper: disable foreign-key and unique checks to avoid ordering errors.
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;


-- Core: Departments, Users, Employees, Security

CREATE TABLE IF NOT EXISTS `departments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) UNIQUE NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `parent_id` BIGINT UNSIGNED NULL,
  `head_id` BIGINT UNSIGNED NULL COMMENT 'employee id who is head',
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  FOREIGN KEY (`parent_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  INDEX `idx_departments_parent` (`parent_id`),
  INDEX `idx_departments_head` (`head_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Compatibility: minimal `companies` and `branches` tables to satisfy legacy references
CREATE TABLE IF NOT EXISTS `companies` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(50) NULL,
  `address` TEXT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `branches` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` BIGINT UNSIGNED NOT NULL,
  `code` VARCHAR(50) NULL,
  `name` VARCHAR(255) NOT NULL,
  `type` VARCHAR(50) NULL,
  `country` VARCHAR(100) NULL,
  `city` VARCHAR(100) NULL,
  `address` TEXT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_branches_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed a single organization and main branch if none exist
INSERT INTO `companies` (`name`,`code`) 
SELECT 'Education For Life','EFL' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `companies`);

INSERT INTO `branches` (`company_id`,`code`,`name`,`type`,`country`,`city`) 
SELECT c.id,'MAIN','Main Campus','headquarters','Unknown','Unknown' FROM `companies` c
WHERE NOT EXISTS (SELECT 1 FROM `branches` WHERE company_id = c.id);


-- moved `idx_store_transactions_reference` index placement to later; avoid ALTER here to keep imports idempotent
CREATE TABLE IF NOT EXISTS `roles` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `level` INT DEFAULT 0,
  `is_system` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_role_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(150) NOT NULL UNIQUE,
  `group` VARCHAR(100) NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `department_id` BIGINT UNSIGNED NULL,
  `employee_id` BIGINT UNSIGNED NULL COMMENT 'nullable link to employees',
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(50),
  `profile_photo` VARCHAR(500),
  `two_factor_enabled` BOOLEAN DEFAULT FALSE,
  `remember_token` VARCHAR(100),
  `status` ENUM('active','inactive','suspended','terminated') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  INDEX `idx_users_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_roles` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `assigned_by` BIGINT UNSIGNED NULL,
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`role_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_permissions` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  `granted_by` BIGINT UNSIGNED NULL,
  `granted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`permission_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`granted_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System settings table used by SettingService
CREATE TABLE IF NOT EXISTS `settings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(191) NOT NULL UNIQUE,
  `value` TEXT NULL,
  `type` VARCHAR(20) NOT NULL DEFAULT 'string',
  `group` VARCHAR(100) NOT NULL DEFAULT 'general',
  `description` TEXT NULL,
  `default_value` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_settings_group` (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Employees / Staff (may or may not be linked to `users`)
CREATE TABLE IF NOT EXISTS `employees` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL UNIQUE,
  `department_id` BIGINT UNSIGNED NULL,
  `employee_number` VARCHAR(50) UNIQUE,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255),
  `phone` VARCHAR(50),
  `national_id` VARCHAR(50) NULL,
  `kra_pin` VARCHAR(50) NULL COMMENT 'Kenya Revenue Authority PIN',
  `nssf_number` VARCHAR(50) NULL COMMENT 'Social security number',
  `nhif_number` VARCHAR(50) NULL COMMENT 'Health insurance number',
  `mobile_money_number` VARCHAR(50) NULL,
  `preferred_bank_account_id` BIGINT UNSIGNED NULL COMMENT 'links to employee_bank_accounts.id (no FK to avoid ordering issues)',
  `hire_date` DATE,
  `job_title` VARCHAR(255),
  `status` ENUM('active','inactive','terminated') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  INDEX `idx_employees_user` (`user_id`),
  INDEX `idx_employees_department` (`department_id`),
  INDEX `idx_employees_kra` (`kra_pin`),
  INDEX `idx_employees_nssf` (`nssf_number`),
  INDEX `idx_employees_nhif` (`nhif_number`),
  INDEX `idx_employees_preferred_bank` (`preferred_bank_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Track HR role assignments to employees (separate from `user_roles` which are account grants)
CREATE TABLE IF NOT EXISTS `employee_roles` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `assigned_by` BIGINT UNSIGNED NULL COMMENT 'HR user who assigned this role to employee',
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `note` TEXT NULL,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_employee_role` (`employee_id`,`role_id`),
  INDEX `idx_employee_roles_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Onboarding workflow: admin creates employee record, HR assigns role/department, admin creates user account
CREATE TABLE IF NOT EXISTS `employee_onboardings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `created_by_admin_id` BIGINT UNSIGNED NULL,
  `hr_assigned_by` BIGINT UNSIGNED NULL,
  `user_account_created_by` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `status` ENUM('pending','hr_assigned','user_created','completed','cancelled') DEFAULT 'pending',
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  `notes` TEXT NULL,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by_admin_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`hr_assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_account_created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_onboardings_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login / Security helpers
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `username_or_email` VARCHAR(255),
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `success` BOOLEAN DEFAULT FALSE,
  `attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_login_attempts_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(255) PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `payload` LONGTEXT,
  `last_activity` INT,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_sessions_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `password_resets` (
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_password_resets_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_devices` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `device_name` VARCHAR(255),
  `device_type` ENUM('mobile','tablet','desktop','biometric') DEFAULT 'desktop',
  `device_id` VARCHAR(255),
  `fcm_token` TEXT,
  `last_used_at` TIMESTAMP NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_devices_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `event_type` VARCHAR(100),
  `auditable_type` VARCHAR(255),
  `auditable_id` BIGINT UNSIGNED NULL,
  `old_values` JSON,
  `new_values` JSON,
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_audit_logs_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notifications
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `type` VARCHAR(100),
  `title` VARCHAR(255),
  `body` TEXT,
  `data` JSON,
  `read_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_notifications_user` (`user_id`,`read_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Attendance & HR / Payroll
-- =====================================================

CREATE TABLE IF NOT EXISTS `shifts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `grace_minutes_late` INT DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_shift_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `schedules` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `shift_id` BIGINT UNSIGNED NULL,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `shifts`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_employee_date` (`employee_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendance` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `clock_in` DATETIME NULL,
  `clock_out` DATETIME NULL,
  `clock_in_method` ENUM('biometric','manual','card','mobile','web','gatekeeper') DEFAULT 'biometric',
  `status` ENUM('present','absent','late','on_leave') DEFAULT 'present',
  `recorded_by` BIGINT UNSIGNED NULL COMMENT 'user who recorded this entry (gatekeeper or system)',
  `excused` BOOLEAN DEFAULT FALSE,
  `excused_reason` TEXT NULL,
  `excused_by` BIGINT UNSIGNED NULL,
  `excused_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`excused_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_attendance_employee_date` (`employee_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance support: devices, corrections, overtime, audit logs
CREATE TABLE IF NOT EXISTS `attendance_devices` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `device_type` ENUM('biometric','card','mobile','web') DEFAULT 'biometric',
  `identifier` VARCHAR(255) NULL,
  `location` VARCHAR(255) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendance_corrections` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attendance_id` BIGINT UNSIGNED NOT NULL,
  `requested_by` BIGINT UNSIGNED NULL,
  `requested_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `from_value` TEXT NULL,
  `to_value` TEXT NULL,
  `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` TIMESTAMP NULL,
  `reason` TEXT NULL,
  FOREIGN KEY (`attendance_id`) REFERENCES `attendance`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_att_corr_attendance` (`attendance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `overtime_records` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `hours` DECIMAL(5,2) NOT NULL,
  `approved` BOOLEAN DEFAULT FALSE,
  `approved_by` BIGINT UNSIGNED NULL,
  `approved_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_overtime_employee_date` (`employee_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendance_audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attendance_id` BIGINT UNSIGNED NOT NULL,
  `actor_id` BIGINT UNSIGNED NULL,
  `action` VARCHAR(100) NOT NULL,
  `details` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`attendance_id`) REFERENCES `attendance`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`actor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_att_audit_attendance` (`attendance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `breaks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attendance_id` BIGINT UNSIGNED NOT NULL,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NULL,
  `recorded_by` BIGINT UNSIGNED NULL COMMENT 'user who logged the break',
  FOREIGN KEY (`attendance_id`) REFERENCES `attendance`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leave_types` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `paid` BOOLEAN DEFAULT TRUE,
  `accrues` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_leave_type_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leave_requests` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `leave_type_id` BIGINT UNSIGNED NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `status` ENUM('draft','submitted','approved','rejected','cancelled') DEFAULT 'draft',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Holidays and calendar exceptions
CREATE TABLE IF NOT EXISTS `holidays` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `date` DATE NOT NULL,
  `recurrent` BOOLEAN DEFAULT FALSE COMMENT 'true = recurs annually',
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_holidays_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Use to model specific calendar exceptions (e.g., working weekend days, special shifts)
CREATE TABLE IF NOT EXISTS `calendar_exceptions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `date` DATE NOT NULL,
  `type` ENUM('holiday','working_day','special_shift') NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_calendar_exception_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_components` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `type` ENUM('earning','deduction') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payruns` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `run_date` DATE NOT NULL,
  `status` ENUM('draft','finalized') DEFAULT 'draft',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payslips` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payrun_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `gross` DECIMAL(15,2) DEFAULT 0,
  `net` DECIMAL(15,2) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payrun_id`) REFERENCES `payruns`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Payslip documents (electronic payslips accessible by staff)
-- =====================================================
CREATE TABLE IF NOT EXISTS `payslip_documents` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payslip_id` BIGINT UNSIGNED NOT NULL,
  `file_path` VARCHAR(1000) NOT NULL,
  `file_name` VARCHAR(255) NULL,
  `mime_type` VARCHAR(255) NULL,
  `size` BIGINT NULL,
  `uploaded_by` BIGINT UNSIGNED NULL,
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `visible_to_employee` BOOLEAN DEFAULT TRUE,
  FOREIGN KEY (`payslip_id`) REFERENCES `payslips`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_payslip_documents_payslip` (`payslip_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Technical Support / Helpdesk
-- =====================================================
CREATE TABLE IF NOT EXISTS `support_ticket_categories` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_support_ticket_category_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ticket_number` VARCHAR(100) UNIQUE,
  `category_id` BIGINT UNSIGNED NULL,
  `department_id` BIGINT UNSIGNED NULL,
  `reported_by` BIGINT UNSIGNED NULL,
  `assigned_to` BIGINT UNSIGNED NULL,
  `priority` ENUM('low','normal','high','urgent') DEFAULT 'normal',
  `status` ENUM('open','in_progress','on_hold','resolved','closed') DEFAULT 'open',
  `subject` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `support_ticket_categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`reported_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_support_tickets_department` (`department_id`),
  INDEX `idx_support_tickets_reported_by` (`reported_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_ticket_messages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` BIGINT UNSIGNED NOT NULL,
  `author_id` BIGINT UNSIGNED NULL,
  `message` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_support_messages_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_ticket_attachments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` BIGINT UNSIGNED NOT NULL,
  `file_path` VARCHAR(1000) NOT NULL,
  `file_name` VARCHAR(255) NULL,
  `mime_type` VARCHAR(255) NULL,
  `size` BIGINT NULL,
  `uploaded_by` BIGINT UNSIGNED NULL,
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_support_attachments_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_ticket_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` BIGINT UNSIGNED NOT NULL,
  `actor_id` BIGINT UNSIGNED NULL,
  `from_status` VARCHAR(50) NULL,
  `to_status` VARCHAR(50) NULL,
  `note` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`actor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_support_logs_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Payroll: periods, lines, deductions, taxes, grades, adjustments, transactions
CREATE TABLE IF NOT EXISTS `payroll_periods` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `status` ENUM('open','closed') DEFAULT 'open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_payroll_period` (`start_date`,`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payslip_lines` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payslip_id` BIGINT UNSIGNED NOT NULL,
  `component_id` BIGINT UNSIGNED NULL,
  `description` VARCHAR(255),
  `amount` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `line_type` ENUM('earning','deduction') NOT NULL DEFAULT 'earning',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payslip_id`) REFERENCES `payslips`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`component_id`) REFERENCES `payroll_components`(`id`) ON DELETE SET NULL,
  INDEX `idx_payslip_lines_payslip` (`payslip_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_deductions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `deduction_type` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `recurring` BOOLEAN DEFAULT TRUE,
  `start_date` DATE NULL,
  `end_date` DATE NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_payroll_deductions_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tax_rates` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `rate_percent` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `threshold_min` DECIMAL(15,2) DEFAULT 0,
  `threshold_max` DECIMAL(15,2) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `salary_grades` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `min_salary` DECIMAL(15,2) DEFAULT 0,
  `max_salary` DECIMAL(15,2) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_adjustments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payslip_id` BIGINT UNSIGNED NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `reason` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payslip_id`) REFERENCES `payslips`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_payroll_adjustments_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_transactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payslip_id` BIGINT UNSIGNED NULL,
  `transaction_ref` VARCHAR(255),
  `amount` DECIMAL(15,2) NOT NULL,
  `transferred_at` TIMESTAMP NULL,
  `status` ENUM('pending','completed','failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payslip_id`) REFERENCES `payslips`(`id`) ON DELETE SET NULL,
  INDEX `idx_payroll_transactions_payslip` (`payslip_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_settings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(191) NOT NULL UNIQUE,
  `value` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- HR: Positions, Contracts, Contacts, Bank, Dependents, Benefits, Allowances, Performance, Training, Documents
CREATE TABLE IF NOT EXISTS `job_positions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) UNIQUE,
  `title` VARCHAR(255) NOT NULL,
  `grade` VARCHAR(50) NULL,
  `department_id` BIGINT UNSIGNED NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  INDEX `idx_job_positions_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_contracts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `contract_type` ENUM('permanent','contract','temporary','intern') DEFAULT 'permanent',
  `start_date` DATE NOT NULL,
  `end_date` DATE NULL,
  `salary` DECIMAL(15,2) DEFAULT 0,
  `probation_months` INT DEFAULT 0,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_employee_contracts_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `emergency_contacts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `relation` VARCHAR(100),
  `phone` VARCHAR(50),
  `email` VARCHAR(255),
  `address` VARCHAR(500),
  `is_primary` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_emergency_contacts_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_bank_accounts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `bank_name` VARCHAR(255),
  `account_number` VARCHAR(100),
  `branch` VARCHAR(255),
  `currency` VARCHAR(10) DEFAULT 'USD',
  `is_primary` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_employee_bank_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dependents` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `relation` VARCHAR(100),
  `date_of_birth` DATE NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_dependents_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_benefits` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `benefit_type` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(15,2) DEFAULT 0,
  `start_date` DATE NULL,
  `end_date` DATE NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_employee_benefits_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_allowances` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `allowance_type` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(15,2) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_employee_allowances_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `performance_reviews` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `reviewer_id` BIGINT UNSIGNED NULL,
  `review_date` DATE NOT NULL,
  `score` INT NULL,
  `comments` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`reviewer_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
  INDEX `idx_performance_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `training_records` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `provider` VARCHAR(255),
  `start_date` DATE NULL,
  `end_date` DATE NULL,
  `status` ENUM('planned','completed','cancelled','in_progress') DEFAULT 'planned',
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_training_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hr_documents` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `doc_type` VARCHAR(100),
  `file_path` VARCHAR(1000),
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  INDEX `idx_hr_documents_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gatekeeper / Access control
CREATE TABLE IF NOT EXISTS `gates` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `location` VARCHAR(255),
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `visitors` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `company` VARCHAR(255),
  `id_document` VARCHAR(255),
  `phone` VARCHAR(50),
  `email` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ,INDEX `idx_visitors_id_document` (`id_document`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `visitor_passes` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `visitor_id` BIGINT UNSIGNED NOT NULL,
  `pass_code` VARCHAR(100) UNIQUE,
  `issued_by` BIGINT UNSIGNED NULL,
  `issued_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NULL,
  `status` ENUM('active','expired','revoked') DEFAULT 'active',
  FOREIGN KEY (`visitor_id`) REFERENCES `visitors`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_visitor_pass_visitor` (`visitor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `visitor_checkins` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `visitor_id` BIGINT UNSIGNED NULL,
  `id_document` VARCHAR(255) NULL COMMENT 'ID number entered at gate',
  `name_entered` VARCHAR(255) NULL,
  `phone_entered` VARCHAR(50) NULL,
  `checked_in_by` BIGINT UNSIGNED NULL COMMENT 'user/guard who checked in',
  `check_in_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `check_out_time` TIMESTAMP NULL,
  `pass_code` VARCHAR(100) NULL,
  `matched` BOOLEAN DEFAULT FALSE COMMENT 'true if matched to existing visitor record',
  `match_visitor_id` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  FOREIGN KEY (`visitor_id`) REFERENCES `visitors`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`checked_in_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`match_visitor_id`) REFERENCES `visitors`(`id`) ON DELETE SET NULL,
  INDEX `idx_visitor_checkins_iddoc` (`id_document`),
  INDEX `idx_visitor_checkins_checkin` (`check_in_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `access_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `employee_id` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `visitor_id` BIGINT UNSIGNED NULL,
  `gate_id` BIGINT UNSIGNED NULL,
  `action` ENUM('entry','exit','unauthorized') DEFAULT 'entry',
  `recorded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `remarks` TEXT,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`visitor_id`) REFERENCES `visitors`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`gate_id`) REFERENCES `gates`(`id`) ON DELETE SET NULL,
  INDEX `idx_access_logs_gate` (`gate_id`),
  INDEX `idx_access_logs_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `guard_shifts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `guard_employee_id` BIGINT UNSIGNED NOT NULL,
  `gate_id` BIGINT UNSIGNED NOT NULL,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NULL,
  `notes` TEXT,
  FOREIGN KEY (`guard_employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`gate_id`) REFERENCES `gates`(`id`) ON DELETE CASCADE,
  INDEX `idx_guard_shifts_guard` (`guard_employee_id`),
  INDEX `idx_guard_shifts_gate` (`gate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Finance (basic ledger + invoices + payments)
-- =====================================================

CREATE TABLE IF NOT EXISTS `chart_of_accounts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `type` ENUM('asset','liability','equity','income','expense') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `journal_entries` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `entry_date` DATE NOT NULL,
  `narration` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `journal_lines` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `journal_id` BIGINT UNSIGNED NOT NULL,
  `account_id` BIGINT UNSIGNED NOT NULL,
  `debit` DECIMAL(15,2) DEFAULT 0,
  `credit` DECIMAL(15,2) DEFAULT 0,
  FOREIGN KEY (`journal_id`) REFERENCES `journal_entries`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoices` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_number` VARCHAR(50) UNIQUE NOT NULL,
  `supplier` VARCHAR(255),
  `total` DECIMAL(15,2) DEFAULT 0,
  `status` ENUM('draft','posted','paid') DEFAULT 'draft',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` BIGINT UNSIGNED NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `paid_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Inventory / Store / Procurement (single store)
-- =====================================================

CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `contact_person` VARCHAR(255),
  `email` VARCHAR(255),
  `phone` VARCHAR(50),
  `address` VARCHAR(500),
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(100) UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `unit` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `warehouses` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(50) UNIQUE,
  `is_main` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_levels` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT DEFAULT 0,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_product_warehouse` (`product_id`,`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_movements` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `from_warehouse_id` BIGINT UNSIGNED NULL,
  `to_warehouse_id` BIGINT UNSIGNED NULL,
  `quantity` INT NOT NULL,
  `movement_type` ENUM('receipt','issue','transfer','adjustment') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_requests` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `request_number` VARCHAR(50) UNIQUE,
  `department_id` BIGINT UNSIGNED NULL,
  `requested_by` BIGINT UNSIGNED NULL,
  `status` ENUM('draft','submitted','approved','rejected') DEFAULT 'draft',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_orders` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `po_number` VARCHAR(50) UNIQUE,
  `supplier_id` BIGINT UNSIGNED NULL,
  `total` DECIMAL(15,2) DEFAULT 0,
  `status` ENUM('draft','ordered','received') DEFAULT 'draft',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `goods_receipts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `grn_number` VARCHAR(50) UNIQUE,
  `purchase_order_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Procurement / Main Store: item lines, categories, supplier contacts, stock adjustments/transfers
CREATE TABLE IF NOT EXISTS `purchase_request_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_request_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `unit_price` DECIMAL(15,2) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`purchase_request_id`) REFERENCES `purchase_requests`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  INDEX `idx_pr_items_request` (`purchase_request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_order_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_order_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `unit_price` DECIMAL(15,2) DEFAULT 0,
  `received_quantity` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  INDEX `idx_po_items_order` (`purchase_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `goods_receipt_lines` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `goods_receipt_id` BIGINT UNSIGNED NOT NULL,
  `purchase_order_item_id` BIGINT UNSIGNED NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`goods_receipt_id`) REFERENCES `goods_receipts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`purchase_order_item_id`) REFERENCES `purchase_order_items`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  INDEX `idx_gr_lines_receipt` (`goods_receipt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `parent_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`parent_id`) REFERENCES `product_categories`(`id`) ON DELETE SET NULL,
  INDEX `idx_product_categories_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_suppliers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `supplier_id` BIGINT UNSIGNED NOT NULL,
  `supplier_sku` VARCHAR(255),
  `lead_time_days` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_product_supplier` (`product_id`,`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_contacts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `supplier_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50),
  `email` VARCHAR(255),
  `position` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE CASCADE,
  INDEX `idx_supplier_contacts_supplier` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_adjustments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `adjustment_type` ENUM('increase','decrease') NOT NULL,
  `quantity` INT NOT NULL,
  `reason` VARCHAR(255),
  `adjusted_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`adjusted_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_stock_adjustments_product` (`product_id`),
  INDEX `idx_stock_adjustments_warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_transfers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `from_warehouse_id` BIGINT UNSIGNED NOT NULL,
  `to_warehouse_id` BIGINT UNSIGNED NOT NULL,
  `initiated_by` BIGINT UNSIGNED NULL,
  `approved_by` BIGINT UNSIGNED NULL,
  `status` ENUM('initiated','approved','completed','cancelled') DEFAULT 'initiated',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`from_warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`to_warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`initiated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_stock_transfers_from` (`from_warehouse_id`),
  INDEX `idx_stock_transfers_to` (`to_warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inventory_locations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `code` VARCHAR(100) UNIQUE,
  `description` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  INDEX `idx_inventory_locations_warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `store_transactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `reference_type` VARCHAR(100),
  `reference_id` BIGINT UNSIGNED,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `transaction_type` ENUM('in','out','transfer','adjustment') NOT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_store_transactions_product` (`product_id`),
  INDEX `idx_store_transactions_warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- index to speed lookups by reference (requisition/release etc.)
-- index to speed lookups by reference (requisition/release etc.) -- moved into table definition when needed

-- Stock ledger for per-movement valuation and audit
CREATE TABLE IF NOT EXISTS `stock_ledger` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` BIGINT UNSIGNED NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NULL,
  `movement_type` ENUM('in','out','adjustment','transfer') NOT NULL,
  `quantity` INT NOT NULL,
  `unit_cost` DECIMAL(12,4) DEFAULT 0.0000,
  `total_cost` DECIMAL(18,4) GENERATED ALWAYS AS (quantity * unit_cost) STORED,
  `balance_quantity` INT DEFAULT 0,
  `balance_value` DECIMAL(18,4) DEFAULT 0.0000,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `store_transactions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
  INDEX `idx_stock_ledger_product` (`product_id`),
  INDEX `idx_stock_ledger_warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Department store: requisitions, release orders, department stock levels
CREATE TABLE IF NOT EXISTS `department_requisitions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `requisition_number` VARCHAR(100) UNIQUE,
  `department_id` BIGINT UNSIGNED NOT NULL,
  `requested_by` BIGINT UNSIGNED NULL,
  `status` ENUM('draft','submitted','approved','rejected','processed') DEFAULT 'draft',
  `current_approver_id` BIGINT UNSIGNED NULL,
  `approved_by` BIGINT UNSIGNED NULL,
  `approved_at` TIMESTAMP NULL,
  `manager_comments` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`current_approver_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_dept_requisitions_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `department_requisition_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `requisition_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`requisition_id`) REFERENCES `department_requisitions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  INDEX `idx_dept_req_items_requisition` (`requisition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `release_orders` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `release_number` VARCHAR(100) UNIQUE,
  `department_id` BIGINT UNSIGNED NOT NULL,
  `issued_by` BIGINT UNSIGNED NULL,
  `issued_from_warehouse_id` BIGINT UNSIGNED NULL,
  `status` ENUM('pending','issued','completed','cancelled') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`issued_from_warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
  INDEX `idx_release_orders_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `release_order_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `release_order_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`release_order_id`) REFERENCES `release_orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  INDEX `idx_release_items_order` (`release_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `department_stock_levels` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `department_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_department_product` (`department_id`,`product_id`),
  INDEX `idx_department_stock_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `department_returns` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `return_number` VARCHAR(100) UNIQUE,
  `department_id` BIGINT UNSIGNED NOT NULL,
  `processed_by` BIGINT UNSIGNED NULL,
  `status` ENUM('pending','processed','rejected') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `department_return_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `department_return_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_return_id`) REFERENCES `department_returns`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  INDEX `idx_dept_return_items_return` (`department_return_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Approval tracking for department requisitions
CREATE TABLE IF NOT EXISTS `department_requisition_approvals` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `requisition_id` BIGINT UNSIGNED NOT NULL,
  `approver_id` BIGINT UNSIGNED NULL,
  `approver_role` VARCHAR(100) NULL,
  `decision` ENUM('approved','rejected','needs_info') NOT NULL,
  `comments` TEXT NULL,
  `decided_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`requisition_id`) REFERENCES `department_requisitions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`approver_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_req_approvals_requisition` (`requisition_id`),
  INDEX `idx_req_approvals_approver` (`approver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `department_requisition_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `requisition_id` BIGINT UNSIGNED NOT NULL,
  `actor_id` BIGINT UNSIGNED NULL,
  `from_status` VARCHAR(50) NULL,
  `to_status` VARCHAR(50) NULL,
  `note` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`requisition_id`) REFERENCES `department_requisitions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`actor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_req_logs_requisition` (`requisition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Department manager assignments (for routing requisitions and approvals)
CREATE TABLE IF NOT EXISTS `department_managers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `department_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `assigned_by` BIGINT UNSIGNED NULL,
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_department_manager` (`department_id`,`user_id`),
  INDEX `idx_dept_managers_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Library
-- =====================================================

-- =====================================================
-- Asset Inventory (fixed assets, furniture, equipment)
-- =====================================================

CREATE TABLE IF NOT EXISTS `asset_types` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_asset_type_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `asset_locations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `warehouse_id` BIGINT UNSIGNED NULL,
  `department_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `uq_asset_location_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `assets` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `asset_tag` VARCHAR(100) UNIQUE NOT NULL,
  `asset_type_id` BIGINT UNSIGNED NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `serial_number` VARCHAR(255) NULL,
  `purchase_date` DATE NULL,
  `purchase_cost` DECIMAL(12,2) NULL,
  `condition` ENUM('new','good','fair','poor') DEFAULT 'good',
  `status` ENUM('available','assigned','in_repair','disposed','lost') DEFAULT 'available',
  `current_location_id` BIGINT UNSIGNED NULL,
  `current_department_id` BIGINT UNSIGNED NULL,
  `current_user_id` BIGINT UNSIGNED NULL,
  `acquisition_details` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  FOREIGN KEY (`asset_type_id`) REFERENCES `asset_types`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`current_location_id`) REFERENCES `asset_locations`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`current_department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`current_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_assets_type` (`asset_type_id`),
  INDEX `idx_assets_department` (`current_department_id`),
  INDEX `idx_assets_user` (`current_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `asset_assignments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `asset_id` BIGINT UNSIGNED NOT NULL,
  `assigned_to_user_id` BIGINT UNSIGNED NULL,
  `assigned_to_department_id` BIGINT UNSIGNED NULL,
  `assigned_by` BIGINT UNSIGNED NULL,
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `due_back_at` TIMESTAMP NULL,
  `returned_at` TIMESTAMP NULL,
  `note` TEXT NULL,
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`assigned_to_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to_department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_asset_assignments_asset` (`asset_id`),
  INDEX `idx_asset_assignments_user` (`assigned_to_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `asset_maintenance` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `asset_id` BIGINT UNSIGNED NOT NULL,
  `performed_by_user_id` BIGINT UNSIGNED NULL,
  `vendor_id` BIGINT UNSIGNED NULL,
  `maintenance_date` DATE NOT NULL,
  `description` TEXT NULL,
  `cost` DECIMAL(12,2) DEFAULT 0.00,
  `next_due_date` DATE NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`performed_by_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`vendor_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL,
  INDEX `idx_asset_maint_asset` (`asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `asset_disposals` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `asset_id` BIGINT UNSIGNED NOT NULL,
  `disposed_by` BIGINT UNSIGNED NULL,
  `disposed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `method` VARCHAR(150) NULL,
  `reason` TEXT NULL,
  `proceeds` DECIMAL(12,2) DEFAULT 0.00,
  `notes` TEXT NULL,
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`disposed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_asset_disposals_asset` (`asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `asset_audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `asset_id` BIGINT UNSIGNED NOT NULL,
  `actor_id` BIGINT UNSIGNED NULL,
  `action` VARCHAR(100) NOT NULL,
  `details` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`actor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_asset_audit_asset` (`asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Store / Asset Issue Reporting (broken items, consumables used)
-- =====================================================

CREATE TABLE IF NOT EXISTS `store_issues` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `issue_number` VARCHAR(100) UNIQUE,
  `reference_type` ENUM('product','asset','release','requisition') DEFAULT 'product',
  `reference_id` BIGINT UNSIGNED NULL,
  `product_id` BIGINT UNSIGNED NULL,
  `asset_id` BIGINT UNSIGNED NULL,
  `release_order_id` BIGINT UNSIGNED NULL,
  `department_id` BIGINT UNSIGNED NULL,
  `reported_by` BIGINT UNSIGNED NULL,
  `issue_type` ENUM('damaged','broken','missing','consumable_used','defective','other') NOT NULL,
  `quantity` INT DEFAULT 1,
  `reason` TEXT NULL,
  `status` ENUM('reported','acknowledged','in_progress','resolved','rejected') DEFAULT 'reported',
  `reported_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `processed_by` BIGINT UNSIGNED NULL,
  `processed_at` TIMESTAMP NULL,
  `resolution` TEXT NULL,
  `action_taken` ENUM('repair','replace','credit','dispose','none') DEFAULT 'none',
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`release_order_id`) REFERENCES `release_orders`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`reported_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_store_issues_product` (`product_id`),
  INDEX `idx_store_issues_asset` (`asset_id`),
  INDEX `idx_store_issues_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Centralized files/media table for attachments
CREATE TABLE IF NOT EXISTS `files` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `storage_path` VARCHAR(1000) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(255) NULL,
  `size` BIGINT NULL,
  `uploaded_by` BIGINT UNSIGNED NULL,
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `related_type` VARCHAR(255) NULL,
  `related_id` BIGINT UNSIGNED NULL,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_files_related` (`related_type`,`related_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Outbox for reliable notifications (email/SMS)
CREATE TABLE IF NOT EXISTS `outbox_notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `recipient` VARCHAR(500) NOT NULL,
  `channel` ENUM('email','sms','push') DEFAULT 'email',
  `payload` JSON NULL,
  `attempts` INT DEFAULT 0,
  `last_attempted_at` TIMESTAMP NULL,
  `status` ENUM('pending','sent','failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_outbox_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Personal access tokens for API/mobile
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `token` VARCHAR(512) NOT NULL,
  `abilities` TEXT NULL,
  `last_used_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_pat_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `store_issue_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `store_issue_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NULL,
  `asset_id` BIGINT UNSIGNED NULL,
  `quantity` INT DEFAULT 1,
  `note` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`store_issue_id`) REFERENCES `store_issues`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE SET NULL,
  INDEX `idx_store_issue_items_issue` (`store_issue_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `store_issue_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `store_issue_id` BIGINT UNSIGNED NOT NULL,
  `actor_id` BIGINT UNSIGNED NULL,
  `from_status` VARCHAR(50) NULL,
  `to_status` VARCHAR(50) NULL,
  `note` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`store_issue_id`) REFERENCES `store_issues`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`actor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_store_issue_logs_issue` (`store_issue_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `library_members` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_number` VARCHAR(50) UNIQUE NOT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255),
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `books` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `isbn` VARCHAR(50),
  `title` VARCHAR(500) NOT NULL,
  `author` VARCHAR(255),
  `publisher` VARCHAR(255),
  `status` ENUM('available','lost','damaged') DEFAULT 'available',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `book_copies` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `book_id` BIGINT UNSIGNED NOT NULL,
  `barcode` VARCHAR(100) UNIQUE,
  `status` ENUM('available','borrowed','reserved') DEFAULT 'available',
  FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `borrow_transactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` BIGINT UNSIGNED NOT NULL,
  `book_copy_id` BIGINT UNSIGNED NOT NULL,
  `borrowed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `due_date` DATE,
  `returned_at` TIMESTAMP NULL,
  FOREIGN KEY (`member_id`) REFERENCES `library_members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`book_copy_id`) REFERENCES `book_copies`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fines` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `borrow_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `paid` DECIMAL(10,2) DEFAULT 0,
  FOREIGN KEY (`borrow_id`) REFERENCES `borrow_transactions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Computer Lab
-- =====================================================

CREATE TABLE IF NOT EXISTS `computer_labs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(50) UNIQUE,
  `capacity` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `computers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `lab_id` BIGINT UNSIGNED NOT NULL,
  `asset_tag` VARCHAR(100) UNIQUE,
  `hostname` VARCHAR(255),
  `ip_address` VARCHAR(45),
  `status` ENUM('available','in_use','maintenance') DEFAULT 'available',
  FOREIGN KEY (`lab_id`) REFERENCES `computer_labs`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_bookings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `lab_id` BIGINT UNSIGNED NOT NULL,
  `booked_by` BIGINT UNSIGNED NULL,
  `date` DATE NOT NULL,
  `start_time` TIME,
  `end_time` TIME,
  FOREIGN KEY (`lab_id`) REFERENCES `computer_labs`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`booked_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Science Lab
-- =====================================================

CREATE TABLE IF NOT EXISTS `chemicals` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `cas_number` VARCHAR(100),
  `name` VARCHAR(255) NOT NULL,
  `formula` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chemical_batches` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `chemical_id` BIGINT UNSIGNED NOT NULL,
  `batch_number` VARCHAR(100),
  `quantity` DECIMAL(15,4) DEFAULT 0,
  `expiry_date` DATE NULL,
  FOREIGN KEY (`chemical_id`) REFERENCES `chemicals`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `experiments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `department_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Utilities: jobs, cache, reports
-- =====================================================

CREATE TABLE IF NOT EXISTS `jobs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `queue` VARCHAR(255) NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `attempts` TINYINT UNSIGNED NOT NULL,
  `available_at` INT UNSIGNED NOT NULL,
  `created_at` INT UNSIGNED NOT NULL,
  INDEX `idx_jobs_queue` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache` (
  `key` VARCHAR(255) PRIMARY KEY,
  `value` MEDIUMTEXT NOT NULL,
  `expiration` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed minimal roles (idempotent)
INSERT IGNORE INTO `roles` (`name`,`slug`,`level`,`is_system`) VALUES
('Admin','admin',100,TRUE),
('Manager','manager',80,FALSE),
('HR','hr',70,FALSE),
('Librarian','librarian',50,FALSE),
('Store Manager','store-manager',70,FALSE),
('Storekeeper','storekeeper',50,FALSE),
('Lab Manager','lab-manager',70,FALSE),
('Gatekeeper','gatekeeper',30,FALSE),
('Employee','employee',10,FALSE)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Example: convert an employee to a user and assign a role (run manually)
-- START TRANSACTION;
-- INSERT INTO `users` (`department_id`,`employee_id`,`username`,`email`,`password`,`first_name`,`last_name`) 
-- SELECT department_id, id, CONCAT(LOWER(first_name),'.',LOWER(last_name)), email, :hashed_password, first_name, last_name FROM employees WHERE id = :employee_id;
-- INSERT INTO `user_roles` (`user_id`,`role_id`,`assigned_by`) VALUES (LAST_INSERT_ID(), (SELECT id FROM roles WHERE slug = 'librarian' LIMIT 1), :admin_user_id);
-- COMMIT;

-- End of single-organization schema
 

CREATE TABLE IF NOT EXISTS `book_authors` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `book_id` BIGINT UNSIGNED NOT NULL,
    `author_name` VARCHAR(255) NOT NULL,
    `author_type` ENUM('author', 'editor', 'translator', 'contributor') DEFAULT 'author',
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON DELETE CASCADE,
    INDEX `idx_book_authors_book` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `book_copies` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `book_id` BIGINT UNSIGNED NOT NULL,
    `copy_number` VARCHAR(50) NOT NULL,
    `barcode` VARCHAR(100) UNIQUE,
    `rfid_tag` VARCHAR(100) UNIQUE,
    `location` VARCHAR(255),
    `shelf_location` VARCHAR(100),
    `status` ENUM('available', 'borrowed', 'reserved', 'maintenance', 'lost', 'damaged', 'withdrawn') DEFAULT 'available',
    `condition` ENUM('new', 'good', 'fair', 'poor', 'damaged') DEFAULT 'good',
    `acquisition_date` DATE,
    `acquisition_price` DECIMAL(15,2),
    `last_inventory_date` DATE,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_copy_book` (`book_id`, `copy_number`),
    INDEX `idx_book_copies_book` (`book_id`),
    INDEX `idx_book_copies_barcode` (`barcode`),
    INDEX `idx_book_copies_rfid` (`rfid_tag`),
    INDEX `idx_book_copies_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `borrow_transactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `transaction_number` VARCHAR(50) UNIQUE NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `book_copy_id` BIGINT UNSIGNED NOT NULL,
    `borrow_date` DATETIME NOT NULL,
    `due_date` DATE NOT NULL,
    `return_date` DATETIME NULL,
    `renewal_count` INT DEFAULT 0,
    `status` ENUM('borrowed', 'returned', 'overdue', 'renewed', 'lost') DEFAULT 'borrowed',
    `issued_by` BIGINT UNSIGNED NOT NULL,
    `received_by` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`member_id`) REFERENCES `library_members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`book_copy_id`) REFERENCES `book_copies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`received_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_borrow_transactions_member` (`member_id`),
    INDEX `idx_borrow_transactions_copy` (`book_copy_id`),
    INDEX `idx_borrow_transactions_status` (`status`),
    INDEX `idx_borrow_transactions_due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fines` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `borrow_transaction_id` BIGINT UNSIGNED NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `fine_type` ENUM('overdue', 'damage', 'loss', 'processing') NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `paid_amount` DECIMAL(10,2) DEFAULT 0,
    `status` ENUM('pending', 'paid', 'waived', 'cancelled') DEFAULT 'pending',
    `due_date` DATE,
    `paid_date` DATE NULL,
    `waived_by` BIGINT UNSIGNED NULL,
    `waived_at` TIMESTAMP NULL,
    `waiver_reason` TEXT,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`borrow_transaction_id`) REFERENCES `borrow_transactions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `library_members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`waived_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_fines_transaction` (`borrow_transaction_id`),
    INDEX `idx_fines_member` (`member_id`),
    INDEX `idx_fines_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `reservations` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `book_id` BIGINT UNSIGNED NOT NULL,
    `reservation_date` DATE NOT NULL,
    `expiry_date` DATE NOT NULL,
    `status` ENUM('pending', 'available', 'expired', 'cancelled', 'fulfilled') DEFAULT 'pending',
    `notified_at` TIMESTAMP NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`member_id`) REFERENCES `library_members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON DELETE CASCADE,
    INDEX `idx_reservations_member` (`member_id`),
    INDEX `idx_reservations_book` (`book_id`),
    INDEX `idx_reservations_status` (`status`),
    INDEX `idx_reservations_date` (`reservation_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Computer Lab Module Tables (Continuation)
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `computers` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lab_id` BIGINT UNSIGNED NULL,
    `asset_tag` VARCHAR(100) UNIQUE NOT NULL,
    `computer_name` VARCHAR(255) NOT NULL,
    `hostname` VARCHAR(255),
    `ip_address` VARCHAR(45),
    `mac_address` VARCHAR(17),
    `manufacturer` VARCHAR(255),
    `model` VARCHAR(255),
    `serial_number` VARCHAR(100) UNIQUE,
    `processor` VARCHAR(255),
    `ram_gb` INT,
    `ram_type` VARCHAR(50),
    `storage_gb` INT,
    `storage_type` ENUM('hdd', 'ssd', 'nvme') DEFAULT 'ssd',
    `graphics_card` VARCHAR(255),
    `operating_system` VARCHAR(255),
    `os_version` VARCHAR(100),
    `os_license_key` VARCHAR(255),
    `office_version` VARCHAR(255),
    `office_license_key` VARCHAR(255),
    `antivirus_installed` VARCHAR(255),
    `purchase_date` DATE,
    `purchase_price` DECIMAL(15,2),
    `warranty_start` DATE,
    `warranty_end` DATE,
    `warranty_provider` VARCHAR(255),
    `status` ENUM('available', 'in_use', 'maintenance', 'repair', 'retired', 'lost') DEFAULT 'available',
    `location` VARCHAR(255),
    `last_maintenance` DATE,
    `next_maintenance` DATE,
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_computers_status` (`status`),
    INDEX `idx_computers_asset_tag` (`asset_tag`),
    INDEX `idx_computers_serial` (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `computer_labs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `capacity` INT NOT NULL,
    `computers_count` INT DEFAULT 0,
    `has_projector` BOOLEAN DEFAULT FALSE,
    `has_smartboard` BOOLEAN DEFAULT FALSE,
    `has_ac` BOOLEAN DEFAULT FALSE,
    `floor` VARCHAR(50),
    `building` VARCHAR(255),
    `opening_time` TIME,
    `closing_time` TIME,
    `status` ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
    `lab_assistant_id` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`lab_assistant_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_bookings` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `booking_number` VARCHAR(50) UNIQUE NOT NULL,
    `lab_id` BIGINT UNSIGNED NOT NULL,
    `booked_by` BIGINT UNSIGNED NOT NULL,
    `booked_for_user_id` BIGINT UNSIGNED NULL,
    `purpose` VARCHAR(500) NOT NULL,
    `booking_date` DATE NOT NULL,
    `start_time` TIME NOT NULL,
    `end_time` TIME NOT NULL,
    `attendees_count` INT DEFAULT 1,
    `computers_needed` INT,
    `software_required` TEXT,
    `status` ENUM('pending', 'approved', 'rejected', 'cancelled', 'completed') DEFAULT 'pending',
    `approved_by` BIGINT UNSIGNED NULL,
    `approved_at` TIMESTAMP NULL,
    `rejection_reason` TEXT,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`lab_id`) REFERENCES `computer_labs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`booked_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`booked_for_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_lab_bookings_lab` (`lab_id`),
    INDEX `idx_lab_bookings_date` (`booking_date`),
    INDEX `idx_lab_bookings_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_sessions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lab_booking_id` BIGINT UNSIGNED NULL,
    `computer_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `session_start` DATETIME NOT NULL,
    `session_end` DATETIME NULL,
    `status` ENUM('active', 'completed', 'terminated') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`lab_booking_id`) REFERENCES `lab_bookings`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`computer_id`) REFERENCES `computers`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_lab_sessions_computer` (`computer_id`),
    INDEX `idx_lab_sessions_user` (`user_id`),
    INDEX `idx_lab_sessions_start` (`session_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `software` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `version` VARCHAR(100),
    `publisher` VARCHAR(255),
    `category` VARCHAR(100),
    `license_type` ENUM('perpetual', 'subscription', 'trial', 'free', 'academic') DEFAULT 'perpetual',
    `license_key` TEXT,
    `license_count` INT DEFAULT 1,
    `used_licenses` INT DEFAULT 0,
    `purchase_date` DATE,
    `expiry_date` DATE,
    `support_expiry` DATE,
    `cost` DECIMAL(15,2),
    `is_open_source` BOOLEAN DEFAULT FALSE,
    `website` VARCHAR(255),
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    INDEX `idx_software_expiry` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `computer_software` (
    `computer_id` BIGINT UNSIGNED NOT NULL,
    `software_id` BIGINT UNSIGNED NOT NULL,
    `installed_date` DATE NOT NULL,
    `installed_by` BIGINT UNSIGNED NULL,
    `license_used` VARCHAR(255),
    `status` ENUM('installed', 'uninstalled') DEFAULT 'installed',
    `uninstalled_date` DATE NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`computer_id`, `software_id`),
    FOREIGN KEY (`computer_id`) REFERENCES `computers`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`software_id`) REFERENCES `software`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`installed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maintenance_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `maintenance_number` VARCHAR(50) UNIQUE NOT NULL,
    `computer_id` BIGINT UNSIGNED NULL,
    `equipment_type` ENUM('computer', 'printer', 'scanner', 'projector', 'network') DEFAULT 'computer',
    `equipment_id` BIGINT UNSIGNED NULL,
    `maintenance_type` ENUM('preventive', 'corrective', 'emergency', 'upgrade') DEFAULT 'corrective',
    `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `issue_description` TEXT NOT NULL,
    `resolution` TEXT,
    `reported_by` BIGINT UNSIGNED NOT NULL,
    `assigned_to` BIGINT UNSIGNED NULL,
    `reported_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `started_at` TIMESTAMP NULL,
    `completed_at` TIMESTAMP NULL,
    `cost` DECIMAL(15,2),
    `parts_used` TEXT,
    `status` ENUM('reported', 'assigned', 'in_progress', 'on_hold', 'resolved', 'closed') DEFAULT 'reported',
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`computer_id`) REFERENCES `computers`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`reported_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_maintenance_logs_computer` (`computer_id`),
    INDEX `idx_maintenance_logs_status` (`status`),
    INDEX `idx_maintenance_logs_assigned` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Science Lab Module Tables (Continuation)
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `chemicals` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `lab_id` BIGINT UNSIGNED NULL,
    `cas_number` VARCHAR(50), -- Chemical Abstracts Service number
    `name` VARCHAR(255) NOT NULL,
    `synonyms` TEXT,
    `formula` VARCHAR(255),
    `molecular_weight` DECIMAL(10,4),
    `appearance` VARCHAR(255),
    `odor` VARCHAR(255),
    `density` DECIMAL(10,4),
    `melting_point` VARCHAR(100),
    `boiling_point` VARCHAR(100),
    `flash_point` VARCHAR(100),
    `autoignition_temperature` VARCHAR(100),
    `solubility` TEXT,
    `vapor_pressure` VARCHAR(100),
    `vapor_density` DECIMAL(10,4),
    `ph` VARCHAR(50),
    `storage_conditions` TEXT,
    `incompatible_materials` TEXT,
    `hazard_statements` TEXT,
    `precautionary_statements` TEXT,
    `signal_word` ENUM('warning', 'danger', 'none') DEFAULT 'none',
    `nfpa_health` INT CHECK (nfpa_health BETWEEN 0 AND 4),
    `nfpa_flammability` INT CHECK (nfpa_flammability BETWEEN 0 AND 4),
    `nfpa_reactivity` INT CHECK (nfpa_reactivity BETWEEN 0 AND 4),
    `nfpa_special` VARCHAR(50),
    `ghs_pictograms` JSON,
    `is_restricted` BOOLEAN DEFAULT FALSE,
    `requires_approval` BOOLEAN DEFAULT FALSE,
    `min_storage_level` DECIMAL(10,2),
    `max_storage_level` DECIMAL(10,2),
    `unit` VARCHAR(50) DEFAULT 'g',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    INDEX `idx_chemicals_company` (`company_id`),
    INDEX `idx_chemicals_cas` (`cas_number`),
    FULLTEXT INDEX `idx_chemicals_search` (`name`, `synonyms`, `formula`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chemical_batches` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `chemical_id` BIGINT UNSIGNED NOT NULL,
    `batch_number` VARCHAR(100) NOT NULL,
    `supplier_id` BIGINT UNSIGNED NULL,
    `supplier_batch` VARCHAR(100),
    `quantity` DECIMAL(15,4) NOT NULL,
    `remaining_quantity` DECIMAL(15,4) NOT NULL,
    `unit` VARCHAR(50) NOT NULL,
    `concentration` DECIMAL(10,4),
    `concentration_unit` VARCHAR(50),
    `purity` DECIMAL(5,2),
    `manufacture_date` DATE,
    `expiry_date` DATE NOT NULL,
    `received_date` DATE NOT NULL,
    `opened_date` DATE,
    `opened_by` BIGINT UNSIGNED NULL,
    `storage_location` VARCHAR(255),
    `storage_temperature` VARCHAR(50),
    `msds_available` BOOLEAN DEFAULT FALSE,
    `msds_file_path` VARCHAR(500),
    `safety_data_sheet` VARCHAR(500),
    `status` ENUM('available', 'in_use', 'expired', 'depleted', 'disposed', 'quarantine') DEFAULT 'available',
    `quality_checked` BOOLEAN DEFAULT FALSE,
    `quality_checked_by` BIGINT UNSIGNED NULL,
    `quality_checked_at` TIMESTAMP NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`chemical_id`) REFERENCES `chemicals`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`opened_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`quality_checked_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `unique_chemical_batch` (`chemical_id`, `batch_number`),
    INDEX `idx_chemical_batches_chemical` (`chemical_id`),
    INDEX `idx_chemical_batches_expiry` (`expiry_date`),
    INDEX `idx_chemical_batches_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `experiments` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `experiment_number` VARCHAR(50) UNIQUE NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `objective` TEXT,
    `procedure` TEXT,
    `safety_precautions` TEXT,
    `waste_disposal` TEXT,
    `duration_minutes` INT,
    `difficulty_level` ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
    `department_id` BIGINT UNSIGNED NULL,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `approved_by` BIGINT UNSIGNED NULL,
    `approved_at` TIMESTAMP NULL,
    `status` ENUM('draft', 'active', 'inactive', 'archived') DEFAULT 'draft',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_experiments_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `experiment_chemicals` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `experiment_id` BIGINT UNSIGNED NOT NULL,
    `chemical_id` BIGINT UNSIGNED NOT NULL,
    `quantity` DECIMAL(15,4) NOT NULL,
    `unit` VARCHAR(50) NOT NULL,
    `concentration` VARCHAR(100),
    `is_alternative` BOOLEAN DEFAULT FALSE,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`experiment_id`) REFERENCES `experiments`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`chemical_id`) REFERENCES `chemicals`(`id`) ON DELETE CASCADE,
    INDEX `idx_experiment_chemicals_experiment` (`experiment_id`),
    INDEX `idx_experiment_chemicals_chemical` (`chemical_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `experiment_equipment` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `experiment_id` BIGINT UNSIGNED NOT NULL,
    `equipment_name` VARCHAR(255) NOT NULL,
    `quantity` INT DEFAULT 1,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`experiment_id`) REFERENCES `experiments`(`id`) ON DELETE CASCADE,
    INDEX `idx_experiment_equipment_experiment` (`experiment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `experiment_results` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `experiment_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `performed_date` DATE NOT NULL,
    `start_time` DATETIME,
    `end_time` DATETIME,
    `observations` TEXT,
    `results` TEXT,
    `conclusion` TEXT,
    `data_file_path` VARCHAR(500),
    `status` ENUM('completed', 'failed', 'inconclusive') DEFAULT 'completed',
    `verified_by` BIGINT UNSIGNED NULL,
    `verified_at` TIMESTAMP NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`experiment_id`) REFERENCES `experiments`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`verified_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_experiment_results_experiment` (`experiment_id`),
    INDEX `idx_experiment_results_user` (`user_id`),
    INDEX `idx_experiment_results_performed` (`performed_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_requests` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `request_number` VARCHAR(50) UNIQUE NOT NULL,
    `requester_id` BIGINT UNSIGNED NOT NULL,
    `lab_id` BIGINT UNSIGNED NULL,
    `request_type` ENUM('chemical', 'equipment', 'glassware', 'consumable', 'assistance') NOT NULL,
    `priority` ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    `request_date` DATE NOT NULL,
    `required_date` DATE NOT NULL,
    `purpose` TEXT NOT NULL,
    `status` ENUM('pending', 'approved', 'partially_fulfilled', 'fulfilled', 'rejected', 'cancelled') DEFAULT 'pending',
    `approved_by` BIGINT UNSIGNED NULL,
    `approved_at` TIMESTAMP NULL,
    `fulfilled_by` BIGINT UNSIGNED NULL,
    `fulfilled_at` TIMESTAMP NULL,
    `rejection_reason` TEXT,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`requester_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`lab_id`) REFERENCES `computer_labs`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`fulfilled_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_lab_requests_requester` (`requester_id`),
    INDEX `idx_lab_requests_status` (`status`),
    INDEX `idx_lab_requests_required_date` (`required_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_request_items` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lab_request_id` BIGINT UNSIGNED NOT NULL,
    `chemical_id` BIGINT UNSIGNED NULL,
    `item_name` VARCHAR(255),
    `quantity_requested` DECIMAL(15,4) NOT NULL,
    `quantity_fulfilled` DECIMAL(15,4) DEFAULT 0,
    `unit` VARCHAR(50) NOT NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`lab_request_id`) REFERENCES `lab_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`chemical_id`) REFERENCES `chemicals`(`id`) ON DELETE SET NULL,
    INDEX `idx_lab_request_items_request` (`lab_request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `waste_disposal` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `disposal_number` VARCHAR(50) UNIQUE NOT NULL,
    `chemical_batch_id` BIGINT UNSIGNED NULL,
    `chemical_id` BIGINT UNSIGNED NOT NULL,
    `quantity` DECIMAL(15,4) NOT NULL,
    `unit` VARCHAR(50) NOT NULL,
    `disposal_method` ENUM('chemical_treatment', 'incineration', 'landfill', 'recycling', 'neutralization', 'specialized_vendor') NOT NULL,
    `disposal_date` DATE NOT NULL,
    `disposed_by` BIGINT UNSIGNED NOT NULL,
    `supervised_by` BIGINT UNSIGNED NULL,
    `waste_classification` VARCHAR(255),
    `manifest_number` VARCHAR(255),
    `vendor_name` VARCHAR(255),
    `cost` DECIMAL(15,2),
    `certificate_path` VARCHAR(500),
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`chemical_batch_id`) REFERENCES `chemical_batches`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`chemical_id`) REFERENCES `chemicals`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`disposed_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`supervised_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_waste_disposal_chemical` (`chemical_id`),
    INDEX `idx_waste_disposal_date` (`disposal_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Library Module Tables (Continuation)
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `book_reservations` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `reservation_number` VARCHAR(50) UNIQUE NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `book_id` BIGINT UNSIGNED NOT NULL,
    `book_copy_id` BIGINT UNSIGNED NULL,
    `reservation_date` DATE NOT NULL,
    `expiry_date` DATE NOT NULL,
    `pickup_deadline` DATETIME,
    `notified_at` TIMESTAMP NULL,
    `status` ENUM('pending', 'available', 'notified', 'picked_up', 'cancelled', 'expired') DEFAULT 'pending',
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`member_id`) REFERENCES `library_members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`book_copy_id`) REFERENCES `book_copies`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_book_reservations_member` (`member_id`),
    INDEX `idx_book_reservations_book` (`book_id`),
    INDEX `idx_book_reservations_status` (`status`),
    INDEX `idx_book_reservations_expiry` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `library_fines` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `fine_number` VARCHAR(50) UNIQUE NOT NULL,
    `member_id` BIGINT UNSIGNED NOT NULL,
    `borrow_transaction_id` BIGINT UNSIGNED NULL,
    `fine_type` ENUM('overdue', 'damage', 'loss', 'processing', 'other') NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `paid_amount` DECIMAL(10,2) DEFAULT 0,
    `waived_amount` DECIMAL(10,2) DEFAULT 0,
    `status` ENUM('pending', 'partial', 'paid', 'waived', 'cancelled') DEFAULT 'pending',
    `due_date` DATE,
    `paid_date` DATE NULL,
    `payment_method` VARCHAR(50),
    `payment_reference` VARCHAR(255),
    `waived_by` BIGINT UNSIGNED NULL,
    `waived_at` TIMESTAMP NULL,
    `waiver_reason` TEXT,
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`member_id`) REFERENCES `library_members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`borrow_transaction_id`) REFERENCES `borrow_transactions`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`waived_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_library_fines_member` (`member_id`),
    INDEX `idx_library_fines_status` (`status`),
    INDEX `idx_library_fines_due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acquisitions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `acquisition_number` VARCHAR(50) UNIQUE NOT NULL,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `branch_id` BIGINT UNSIGNED NULL,
    `supplier_id` BIGINT UNSIGNED NULL,
    `acquisition_type` ENUM('purchase', 'donation', 'exchange', 'transfer') DEFAULT 'purchase',
    `order_date` DATE NOT NULL,
    `received_date` DATE NULL,
    `invoice_number` VARCHAR(100),
    `total_amount` DECIMAL(15,2),
    `currency` VARCHAR(3) DEFAULT 'USD',
    `fund_source` VARCHAR(255),
    `donor_name` VARCHAR(255),
    `donor_contact` TEXT,
    `status` ENUM('ordered', 'partial', 'received', 'cancelled') DEFAULT 'ordered',
    `received_by` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`received_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_acquisitions_company` (`company_id`),
    INDEX `idx_acquisitions_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acquisition_items` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `acquisition_id` BIGINT UNSIGNED NOT NULL,
    `book_id` BIGINT UNSIGNED NULL,
    `title` VARCHAR(500) NOT NULL,
    `author` VARCHAR(255),
    `isbn` VARCHAR(20),
    `publisher` VARCHAR(255),
    `publication_year` YEAR,
    `edition` VARCHAR(50),
    `quantity_ordered` INT NOT NULL,
    `quantity_received` INT DEFAULT 0,
    `unit_price` DECIMAL(15,2),
    `total_price` DECIMAL(15,2),
    `currency` VARCHAR(3) DEFAULT 'USD',
    `fund_source` VARCHAR(255),
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`acquisition_id`) REFERENCES `acquisitions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON DELETE SET NULL,
    INDEX `idx_acquisition_items_acquisition` (`acquisition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Events and Notifications Tables
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `events` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `event_id` VARCHAR(100) NOT NULL,
    `event_type` VARCHAR(100) NOT NULL,
    `aggregate_type` VARCHAR(100),
    `aggregate_id` BIGINT UNSIGNED,
    `data` JSON NOT NULL,
    `metadata` JSON,
    `version` INT DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_events_event_id` (`event_id`),
    INDEX `idx_events_event_type` (`event_type`),
    INDEX `idx_events_aggregate` (`aggregate_type`, `aggregate_id`),
    INDEX `idx_events_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `event_subscriptions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `subscriber_type` VARCHAR(100) NOT NULL,
    `subscriber_id` BIGINT UNSIGNED NOT NULL,
    `event_type` VARCHAR(100) NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_subscription` (`subscriber_type`, `subscriber_id`, `event_type`),
    INDEX `idx_event_subscriptions_event` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `event_failures` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `event_id` VARCHAR(100) NOT NULL,
    `listener_class` VARCHAR(255) NOT NULL,
    `attempts` INT DEFAULT 1,
    `last_attempt_at` TIMESTAMP NULL,
    `error_message` TEXT,
    `error_trace` TEXT,
    `status` ENUM('pending', 'processing', 'failed', 'resolved') DEFAULT 'pending',
    `resolved_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_event_failures_event` (`event_id`),
    INDEX `idx_event_failures_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_templates` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `template_key` VARCHAR(100) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `type` ENUM('email', 'sms', 'push', 'in_app') NOT NULL,
    `subject` VARCHAR(255),
    `body` TEXT NOT NULL,
    `variables` JSON,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_template_key_company` (`company_id`, `template_key`),
    INDEX `idx_notification_templates_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `notification_id` BIGINT UNSIGNED NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `channel` ENUM('email', 'sms', 'push', 'in_app') NOT NULL,
    `subject` VARCHAR(255),
    `content` TEXT,
    `status` ENUM('sent', 'delivered', 'failed', 'opened', 'clicked') DEFAULT 'sent',
    `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `delivered_at` TIMESTAMP NULL,
    `opened_at` TIMESTAMP NULL,
    `error_message` TEXT,
    `metadata` JSON,
    INDEX `idx_notification_logs_user` (`user_id`),
    INDEX `idx_notification_logs_notification` (`notification_id`),
    INDEX `idx_notification_logs_status` (`status`),
    INDEX `idx_notification_logs_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Reporting and Analytics Tables
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `reports` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `report_number` VARCHAR(50) UNIQUE NOT NULL,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `category` VARCHAR(100),
    `module` VARCHAR(100),
    `parameters` JSON,
    `query` TEXT,
    `template_path` VARCHAR(500),
    `format` ENUM('pdf', 'excel', 'csv', 'html', 'json') DEFAULT 'pdf',
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_reports_company` (`company_id`),
    INDEX `idx_reports_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `report_schedules` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `report_id` BIGINT UNSIGNED NOT NULL,
    `frequency` ENUM('daily', 'weekly', 'monthly', 'quarterly', 'yearly') NOT NULL,
    `day_of_week` TINYINT,
    `day_of_month` TINYINT,
    `time` TIME NOT NULL,
    `recipients` JSON NOT NULL,
    `format` ENUM('pdf', 'excel', 'csv', 'all') DEFAULT 'pdf',
    `last_run_at` TIMESTAMP NULL,
    `next_run_at` TIMESTAMP NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`) ON DELETE CASCADE,
    INDEX `idx_report_schedules_next_run` (`next_run_at`),
    INDEX `idx_report_schedules_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `report_history` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `report_id` BIGINT UNSIGNED NOT NULL,
    `generated_by` BIGINT UNSIGNED NOT NULL,
    `generated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `file_path` VARCHAR(500) NOT NULL,
    `file_size` INT,
    `parameters` JSON,
    `row_count` INT,
    `status` ENUM('success', 'failed', 'processing') DEFAULT 'success',
    `error_message` TEXT,
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`generated_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_report_history_report` (`report_id`),
    INDEX `idx_report_history_generated` (`generated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dashboards` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `layout` JSON,
    `is_default` BOOLEAN DEFAULT FALSE,
    `role_id` BIGINT UNSIGNED NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_dashboards_company` (`company_id`),
    INDEX `idx_dashboards_role` (`role_id`),
    INDEX `idx_dashboards_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dashboard_widgets` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `dashboard_id` BIGINT UNSIGNED NOT NULL,
    `widget_type` VARCHAR(100) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `size` ENUM('small', 'medium', 'large', 'full') DEFAULT 'medium',
    `position_x` INT DEFAULT 0,
    `position_y` INT DEFAULT 0,
    `width` INT DEFAULT 1,
    `height` INT DEFAULT 1,
    `settings` JSON,
    `data_source` VARCHAR(255),
    `refresh_interval` INT DEFAULT 300,
    `is_visible` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`dashboard_id`) REFERENCES `dashboards`(`id`) ON DELETE CASCADE,
    INDEX `idx_dashboard_widgets_dashboard` (`dashboard_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Queue and Job Tables
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `queue` VARCHAR(255) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL,
    `reserved_at` INT UNSIGNED NULL,
    `available_at` INT UNSIGNED NOT NULL,
    `created_at` INT UNSIGNED NOT NULL,
    INDEX `idx_jobs_queue` (`queue`),
    INDEX `idx_jobs_available` (`available_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `failed_jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(255) UNIQUE NOT NULL,
    `connection` TEXT NOT NULL,
    `queue` TEXT NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `exception` LONGTEXT NOT NULL,
    `failed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `job_batches` (
    `id` VARCHAR(255) PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `total_jobs` INT NOT NULL,
    `pending_jobs` INT NOT NULL,
    `failed_jobs` INT NOT NULL,
    `failed_job_ids` JSON NOT NULL,
    `options` JSON,
    `cancelled_at` INT NULL,
    `created_at` INT NOT NULL,
    `finished_at` INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Cache and Lock Tables
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cache` (
    `key` VARCHAR(255) PRIMARY KEY,
    `value` MEDIUMTEXT NOT NULL,
    `expiration` INT NOT NULL,
    INDEX `idx_cache_expiration` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache_locks` (
    `key` VARCHAR(255) PRIMARY KEY,
    `owner` VARCHAR(255) NOT NULL,
    `expiration` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- API and Integration Tables
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `api_clients` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `client_id` VARCHAR(100) UNIQUE NOT NULL,
    `client_secret` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `allowed_ips` JSON,
    `allowed_origins` JSON,
    `rate_limit` INT DEFAULT 60,
    `expires_at` TIMESTAMP NULL,
    `last_used_at` TIMESTAMP NULL,
    `status` ENUM('active', 'inactive', 'revoked') DEFAULT 'active',
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_api_clients_company` (`company_id`),
    INDEX `idx_api_clients_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `api_client_id` BIGINT UNSIGNED NULL,
    `method` VARCHAR(10) NOT NULL,
    `endpoint` VARCHAR(500) NOT NULL,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `request_headers` JSON,
    `request_body` JSON,
    `response_code` INT,
    `response_body` LONGTEXT,
    `duration_ms` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`api_client_id`) REFERENCES `api_clients`(`id`) ON DELETE SET NULL,
    INDEX `idx_api_logs_company` (`company_id`),
    INDEX `idx_api_logs_user` (`user_id`),
    INDEX `idx_api_logs_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhooks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `url` VARCHAR(500) NOT NULL,
    `secret` VARCHAR(255),
    `events` JSON NOT NULL,
    `headers` JSON,
    `retry_count` INT DEFAULT 3,
    `timeout` INT DEFAULT 30,
    `is_active` BOOLEAN DEFAULT TRUE,
    `last_triggered_at` TIMESTAMP NULL,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_webhooks_company` (`company_id`),
    INDEX `idx_webhooks_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhook_deliveries` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `webhook_id` BIGINT UNSIGNED NOT NULL,
    `event_type` VARCHAR(100) NOT NULL,
    `payload` JSON NOT NULL,
    `response_code` INT,
    `response_body` TEXT,
    `attempts` INT DEFAULT 1,
    `status` ENUM('pending', 'success', 'failed', 'retrying') DEFAULT 'pending',
    `error_message` TEXT,
    `next_retry_at` TIMESTAMP NULL,
    `completed_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`webhook_id`) REFERENCES `webhooks`(`id`) ON DELETE CASCADE,
    INDEX `idx_webhook_deliveries_webhook` (`webhook_id`),
    INDEX `idx_webhook_deliveries_status` (`status`),
    INDEX `idx_webhook_deliveries_next_retry` (`next_retry_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- System and Maintenance Tables
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `system_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `level` ENUM('debug', 'info', 'warning', 'error', 'critical') NOT NULL,
    `message` TEXT NOT NULL,
    `context` JSON,
    `channel` VARCHAR(100),
    `ip_address` VARCHAR(45),
    `user_id` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_system_logs_level` (`level`),
    INDEX `idx_system_logs_created` (`created_at`),
    INDEX `idx_system_logs_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `system_health` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `component` VARCHAR(100) NOT NULL,
    `status` ENUM('healthy', 'warning', 'critical', 'offline') NOT NULL,
    `metric_name` VARCHAR(100),
    `metric_value` VARCHAR(255),
    `metric_unit` VARCHAR(50),
    `message` TEXT,
    `last_check` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_system_health_component` (`component`),
    INDEX `idx_system_health_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `scheduled_tasks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `task_name` VARCHAR(255) NOT NULL,
    `command` VARCHAR(500) NOT NULL,
    `schedule` VARCHAR(255) NOT NULL, -- Cron expression
    `timezone` VARCHAR(100) DEFAULT 'UTC',
    `parameters` JSON,
    `is_active` BOOLEAN DEFAULT TRUE,
    `last_run_at` TIMESTAMP NULL,
    `last_run_status` ENUM('success', 'failed', 'running') NULL,
    `last_run_output` TEXT,
    `next_run_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_scheduled_tasks_next_run` (`next_run_at`),
    INDEX `idx_scheduled_tasks_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `task_history` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `task_id` BIGINT UNSIGNED NOT NULL,
    `started_at` TIMESTAMP NOT NULL,
    `finished_at` TIMESTAMP NULL,
    `status` ENUM('running', 'success', 'failed') NOT NULL,
    `output` TEXT,
    `error_message` TEXT,
    `duration_ms` INT,
    FOREIGN KEY (`task_id`) REFERENCES `scheduled_tasks`(`id`) ON DELETE CASCADE,
    INDEX `idx_task_history_task` (`task_id`),
    INDEX `idx_task_history_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `backups` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `backup_number` VARCHAR(50) UNIQUE NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `type` ENUM('full', 'incremental', 'differential') DEFAULT 'full',
    `size_bytes` BIGINT,
    `file_path` VARCHAR(500) NOT NULL,
    `checksum` VARCHAR(255),
    `database_size` BIGINT,
    `included_tables` JSON,
    `excluded_tables` JSON,
    `status` ENUM('pending', 'in_progress', 'completed', 'failed', 'restored') DEFAULT 'pending',
    `started_at` TIMESTAMP NULL,
    `completed_at` TIMESTAMP NULL,
    `restored_at` TIMESTAMP NULL,
    `restored_by` BIGINT UNSIGNED NULL,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`restored_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_backups_status` (`status`),
    INDEX `idx_backups_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Insert Default Data
-- -----------------------------------------------------

-- Insert default permissions
INSERT INTO `permissions` (`name`, `slug`, `group`) VALUES
-- Core permissions
('View Dashboard', 'view.dashboard', 'core'),
('Manage Users', 'manage.users', 'core'),
('Manage Roles', 'manage.roles', 'core'),
('Manage Permissions', 'manage.permissions', 'core'),
('View Audit Logs', 'view.audit-logs', 'core'),

-- Attendance permissions
('View Attendance', 'view.attendance', 'attendance'),
('Clock In/Out', 'clock.attendance', 'attendance'),
('Manage Leaves', 'manage.leaves', 'attendance'),
('Approve Leaves', 'approve.leaves', 'attendance'),
('View Attendance Reports', 'view.attendance-reports', 'attendance'),

-- Inventory permissions
('View Products', 'view.products', 'inventory'),
('Manage Products', 'manage.products', 'inventory'),
('Receive Stock', 'receive.stock', 'inventory'),
('Issue Stock', 'issue.stock', 'inventory'),
('Transfer Stock', 'transfer.stock', 'inventory'),
('Count Stock', 'count.stock', 'inventory'),
('View Inventory Reports', 'view.inventory-reports', 'inventory'),

-- ETIM permissions
('View ETIM Data', 'view.etim', 'etim'),
('Manage ETIM Data', 'manage.etim', 'etim'),
('Verify ETIM', 'verify.etim', 'etim'),
('Import ETIM', 'import.etim', 'etim'),

-- Procurement permissions
('View Purchase Requests', 'view.purchase-requests', 'procurement'),
('Create Purchase Requests', 'create.purchase-requests', 'procurement'),
('Approve Purchase Requests', 'approve.purchase-requests', 'procurement'),
('Manage Suppliers', 'manage.suppliers', 'procurement'),
('Create Purchase Orders', 'create.purchase-orders', 'procurement'),
('Approve Purchase Orders', 'approve.purchase-orders', 'procurement'),
('Receive Goods', 'receive.goods', 'procurement'),

-- Library permissions
('View Books', 'view.books', 'library'),
('Manage Books', 'manage.books', 'library'),
('Manage Members', 'manage.members', 'library'),
('Borrow Books', 'borrow.books', 'library'),
('Return Books', 'return.books', 'library'),
('Manage Fines', 'manage.fines', 'library'),

-- Computer Lab permissions
('View Computers', 'view.computers', 'computerlab'),
('Manage Computers', 'manage.computers', 'computerlab'),
('Book Lab', 'book.lab', 'computerlab'),
('Manage Software', 'manage.software', 'computerlab'),
('Perform Maintenance', 'perform.maintenance', 'computerlab'),

-- Science Lab permissions
('View Chemicals', 'view.chemicals', 'sciencelab'),
('Manage Chemicals', 'manage.chemicals', 'sciencelab'),
('Request Chemicals', 'request.chemicals', 'sciencelab'),
('Dispose Waste', 'dispose.waste', 'sciencelab'),
('Manage Experiments', 'manage.experiments', 'sciencelab'),

-- Finance permissions
('View Financial Data', 'view.financial', 'finance'),
('Manage Budgets', 'manage.budgets', 'finance'),
('Process Payments', 'process.payments', 'finance'),
('View Financial Reports', 'view.financial-reports', 'finance'),
('Reconcile Accounts', 'reconcile.accounts', 'finance'),

-- Report permissions
('View Reports', 'view.reports', 'reports'),
('Create Reports', 'create.reports', 'reports'),
('Schedule Reports', 'schedule.reports', 'reports'),
('Export Reports', 'export.reports', 'reports');

-- Insert sample company
INSERT INTO `companies` (`name`, `registration_number`, `email`, `phone`, `country`, `timezone`, `currency`) VALUES
('ERP Systems Inc', 'REG-001', 'info@erpsystems.com', '+1234567890', 'USA', 'America/New_York', 'USD');

-- Insert sample branches
INSERT INTO `branches` (`company_id`, `code`, `name`, `type`, `country`, `city`) VALUES
(1, 'HQ', 'Headquarters', 'headquarters', 'USA', 'New York'),
(1, 'BR001', 'Downtown Branch', 'regional', 'USA', 'Los Angeles');

INSERT INTO `departments` (`code`, `name`, `type`) VALUES
('IT', 'Information Technology', 'support'),
('HR', 'Human Resources', 'administrative'),
('FIN', 'Finance', 'administrative'),
('OPS', 'Operations', 'operational'),
('SALES', 'Sales', 'operational');

INSERT INTO `roles` (`name`, `slug`, `level`, `is_system`) VALUES
('Super Admin', 'super-admin', 100, TRUE),
('System Administrator', 'admin', 90, TRUE),
('General Manager', 'general-manager', 80, FALSE),
('HR Manager', 'hr-manager', 70, FALSE),
('HR Staff', 'hr-staff', 60, FALSE),
('Store Manager', 'store-manager', 70, FALSE),
('Storekeeper', 'storekeeper', 50, FALSE),
('Procurement Manager', 'procurement-manager', 70, FALSE),
('Procurement Officer', 'procurement-officer', 50, FALSE),
('Lab Manager', 'lab-manager', 70, FALSE),
('Lab Technician', 'lab-technician', 50, FALSE),
('Lab Assistant', 'lab-assistant', 50, FALSE),
('Librarian', 'librarian', 50, FALSE),
('Gatekeeper', 'gatekeeper', 30, FALSE),
('Employee', 'employee', 20, FALSE);

-- Assign permissions to roles (simplified - would need many more in production)
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.slug = 'super-admin';

INSERT INTO `users` (`department_id`, `username`, `email`, `password`, `first_name`, `last_name`, `status`) VALUES
(1, 'admin', 'admin@erpsystems.com', '$2y$10$aLhfrzKFEudBKvHQEx7A2ego5P35lvyinYt2x7mxdpatUP.ijtPNC', 'System', 'Administrator', 'active');

-- Assign super admin role
INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES (1, 1);

-- =====================================================
-- Example: convert an existing staff (`employees`) record into a loginable `users` account
-- This is a plain SQL transaction you can run on staging/backup. It creates a `users` row,
-- links it to the `employees` record and assigns a role by slug (e.g. 'librarian').
-- Generate the bcrypt hash in PHP (see HashHelper::make or password_hash) and paste into the INSERT.
-- Replace <department_id>, <employee_id>, <admin_user_id> and the bcrypt hash value.

/*
START TRANSACTION;

-- 1) Create the user (use a bcrypt hash for password)
INSERT INTO `users` (`department_id`,`username`,`email`,`password`,`first_name`,`last_name`,`status`)
VALUES (<department_id>, 'jdoe', 'jdoe@example.com', '$2y$10$...bcrypt-hash...', 'John', 'Doe', 'active');
SET @new_user_id = LAST_INSERT_ID();

-- 2) Link employee -> user
UPDATE `employees` SET `user_id` = @new_user_id WHERE `id` = <employee_id>;

-- 3) Assign role by slug (example: 'librarian')
INSERT INTO `user_roles` (`user_id`,`role_id`,`assigned_by`)
SELECT @new_user_id, r.id, <admin_user_id> FROM `roles` r WHERE r.slug = 'librarian'
ON DUPLICATE KEY UPDATE assigned_at = CURRENT_TIMESTAMP;

COMMIT;
*/


-- PROJECT MANAGEMENT TABLES (COMPLETELY MISSING)

CREATE TABLE IF NOT EXISTS `projects` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `branch_id` BIGINT UNSIGNED NULL,
    `department_id` BIGINT UNSIGNED NULL,
    `project_code` VARCHAR(50) UNIQUE NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `start_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `estimated_duration_days` INT,
    `budget` DECIMAL(15,2),
    `actual_cost` DECIMAL(15,2) DEFAULT 0,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `status` ENUM('planning', 'active', 'on_hold', 'completed', 'cancelled') DEFAULT 'planning',
    `progress_percentage` DECIMAL(5,2) DEFAULT 0,
    `project_manager_id` BIGINT UNSIGNED NULL,
    `sponsor` VARCHAR(255),
    `customer_id` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`project_manager_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_projects_company` (`company_id`),
    INDEX `idx_projects_status` (`status`),
    INDEX `idx_projects_manager` (`project_manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_tasks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `parent_task_id` BIGINT UNSIGNED NULL,
    `task_code` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `task_type` ENUM('task', 'milestone', 'phase', 'subtask') DEFAULT 'task',
    `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `status` ENUM('not_started', 'in_progress', 'completed', 'blocked', 'cancelled') DEFAULT 'not_started',
    `start_date` DATE,
    `due_date` DATE,
    `completed_date` DATE NULL,
    `estimated_hours` DECIMAL(10,2),
    `actual_hours` DECIMAL(10,2) DEFAULT 0,
    `progress_percentage` DECIMAL(5,2) DEFAULT 0,
    `assigned_to` BIGINT UNSIGNED NULL,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `completed_by` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent_task_id`) REFERENCES `project_tasks`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`completed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `unique_task_code_project` (`project_id`, `task_code`),
    INDEX `idx_project_tasks_project` (`project_id`),
    INDEX `idx_project_tasks_assigned` (`assigned_to`),
    INDEX `idx_project_tasks_status` (`status`),
    INDEX `idx_project_tasks_dates` (`start_date`, `due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_team` (
    `project_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `role` VARCHAR(100),
    `assignment_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `allocation_percentage` DECIMAL(5,2) DEFAULT 100,
    `is_lead` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`project_id`, `user_id`),
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_project_team_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `time_entries` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `project_id` BIGINT UNSIGNED NULL,
    `task_id` BIGINT UNSIGNED NULL,
    `date` DATE NOT NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME,
    `duration_minutes` INT,
    `description` TEXT,
    `billable` BOOLEAN DEFAULT TRUE,
    `overtime` BOOLEAN DEFAULT FALSE,
    `status` ENUM('draft', 'submitted', 'approved', 'rejected') DEFAULT 'draft',
    `approved_by` BIGINT UNSIGNED NULL,
    `approved_at` TIMESTAMP NULL,
    `rejection_reason` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`task_id`) REFERENCES `project_tasks`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_time_entries_user` (`user_id`),
    INDEX `idx_time_entries_project` (`project_id`),
    INDEX `idx_time_entries_task` (`task_id`),
    INDEX `idx_time_entries_date` (`date`),
    INDEX `idx_time_entries_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_budgets` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `category` VARCHAR(100) NOT NULL,
    `budgeted_amount` DECIMAL(15,2) NOT NULL,
    `committed_amount` DECIMAL(15,2) DEFAULT 0,
    `actual_amount` DECIMAL(15,2) DEFAULT 0,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_project_category` (`project_id`, `category`),
    INDEX `idx_project_budgets_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_risks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `risk_code` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `category` VARCHAR(100),
    `probability` ENUM('very_low', 'low', 'medium', 'high', 'very_high') DEFAULT 'medium',
    `impact` ENUM('very_low', 'low', 'medium', 'high', 'very_high') DEFAULT 'medium',
    `risk_score` DECIMAL(5,2) GENERATED ALWAYS AS (
        CASE 
            WHEN probability = 'very_low' THEN 1
            WHEN probability = 'low' THEN 2
            WHEN probability = 'medium' THEN 3
            WHEN probability = 'high' THEN 4
            WHEN probability = 'very_high' THEN 5
        END * 
        CASE 
            WHEN impact = 'very_low' THEN 1
            WHEN impact = 'low' THEN 2
            WHEN impact = 'medium' THEN 3
            WHEN impact = 'high' THEN 4
            WHEN impact = 'very_high' THEN 5
        END
    ) STORED,
    `mitigation_plan` TEXT,
    `contingency_plan` TEXT,
    `owner` BIGINT UNSIGNED NULL,
    `status` ENUM('identified', 'analyzing', 'mitigating', 'monitoring', 'closed') DEFAULT 'identified',
    `closure_date` DATE NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`owner`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `unique_risk_code_project` (`project_id`, `risk_code`),
    INDEX `idx_project_risks_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_documents` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `document_code` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `file_path` VARCHAR(500) NOT NULL,
    `file_size` INT,
    `mime_type` VARCHAR(100),
    `version` VARCHAR(20),
    `category` VARCHAR(100),
    `uploaded_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_document_code_project` (`project_id`, `document_code`),
    INDEX `idx_project_documents_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- QUALITY MANAGEMENT TABLES (COMPLETELY MISSING)

CREATE TABLE IF NOT EXISTS `quality_checks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `check_number` VARCHAR(50) UNIQUE NOT NULL,
    `reference_type` ENUM('goods_receipt', 'production', 'inventory', 'return') NOT NULL,
    `reference_id` BIGINT UNSIGNED NOT NULL,
    `product_id` BIGINT UNSIGNED NOT NULL,
    `batch_id` BIGINT UNSIGNED NULL,
    `check_date` DATE NOT NULL,
    `checked_by` BIGINT UNSIGNED NOT NULL,
    `template_id` BIGINT UNSIGNED NULL,
    `result` ENUM('pass', 'fail', 'conditional') NOT NULL,
    `defect_quantity` INT DEFAULT 0,
    `defect_rate` DECIMAL(5,2),
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`checked_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_quality_checks_company` (`company_id`),
    INDEX `idx_quality_checks_reference` (`reference_type`, `reference_id`),
    INDEX `idx_quality_checks_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `quality_check_templates` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `product_category_id` BIGINT UNSIGNED NULL,
    `criteria` JSON NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_category_id`) REFERENCES `product_categories`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_quality_check_templates_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `non_conformities` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `nc_number` VARCHAR(50) UNIQUE NOT NULL,
    `reference_type` ENUM('quality_check', 'customer_complaint', 'audit', 'incident') NOT NULL,
    `reference_id` BIGINT UNSIGNED NOT NULL,
    `description` TEXT NOT NULL,
    `severity` ENUM('minor', 'major', 'critical') DEFAULT 'minor',
    `category` VARCHAR(100),
    `reported_by` BIGINT UNSIGNED NOT NULL,
    `reported_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `assigned_to` BIGINT UNSIGNED NULL,
    `due_date` DATE,
    `root_cause` TEXT,
    `corrective_action` TEXT,
    `preventive_action` TEXT,
    `effectiveness_check` TEXT,
    `status` ENUM('open', 'investigating', 'corrective_action', 'verifying', 'closed') DEFAULT 'open',
    `closed_at` TIMESTAMP NULL,
    `closed_by` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reported_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`closed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_non_conformities_company` (`company_id`),
    INDEX `idx_non_conformities_status` (`status`),
    INDEX `idx_non_conformities_assigned` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audits` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `audit_number` VARCHAR(50) UNIQUE NOT NULL,
    `audit_type` ENUM('internal', 'external', 'supplier', 'regulatory') NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `scope` TEXT,
    `criteria` TEXT,
    `start_date` DATE NOT NULL,
    `end_date` DATE,
    `auditor_id` BIGINT UNSIGNED NULL,
    `audit_team` JSON,
    `auditee_id` BIGINT UNSIGNED NULL,
    `status` ENUM('planned', 'in_progress', 'completed', 'cancelled') DEFAULT 'planned',
    `findings` JSON,
    `conclusion` TEXT,
    `report_path` VARCHAR(500),
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`auditor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`auditee_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_audits_company` (`company_id`),
    INDEX `idx_audits_status` (`status`),
    INDEX `idx_audits_dates` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Additional support tables: policies, valuations, reconciliations, archives, retention, SLA
-- =====================================================

CREATE TABLE IF NOT EXISTS `attendance_policies` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(50) UNIQUE NOT NULL,
  `grace_minutes` INT DEFAULT 0,
  `rounding_minutes` INT DEFAULT 1,
  `auto_absent_after_minutes` INT DEFAULT 480,
  `description` TEXT NULL,
  `is_default` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `onboarding_tasks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `onboarding_id` BIGINT UNSIGNED NOT NULL,
  `task_key` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `required` BOOLEAN DEFAULT TRUE,
  `completed` BOOLEAN DEFAULT FALSE,
  `completed_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`onboarding_id`) REFERENCES `employee_onboardings`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_onboard_task` (`onboarding_id`,`task_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inventory_valuations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `valuation_date` DATE NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NULL,
  `product_id` BIGINT UNSIGNED NULL,
  `quantity` DECIMAL(18,4) DEFAULT 0,
  `unit_cost` DECIMAL(18,4) DEFAULT 0.0000,
  `total_value` DECIMAL(24,4) GENERATED ALWAYS AS (quantity * unit_cost) STORED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
  INDEX `idx_inventory_valuations_date` (`valuation_date`),
  INDEX `idx_inventory_valuations_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_reconciliations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `reconciliation_number` VARCHAR(100) UNIQUE,
  `warehouse_id` BIGINT UNSIGNED NULL,
  `conducted_by` BIGINT UNSIGNED NULL,
  `conducted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('open','completed','approved','adjusted') DEFAULT 'open',
  `notes` TEXT NULL,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`conducted_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_stock_reconciliations_warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_reconciliation_lines` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `reconciliation_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `expected_qty` DECIMAL(18,4) DEFAULT 0,
  `counted_qty` DECIMAL(18,4) DEFAULT 0,
  `difference` DECIMAL(18,4) GENERATED ALWAYS AS (counted_qty - expected_qty) STORED,
  `adjusted` BOOLEAN DEFAULT FALSE,
  `notes` TEXT NULL,
  FOREIGN KEY (`reconciliation_id`) REFERENCES `stock_reconciliations`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  INDEX `idx_stock_recon_lines_reconciliation` (`reconciliation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Archive tables for heavy logs (use for periodic archival)
CREATE TABLE IF NOT EXISTS `archived_audit_logs` LIKE `audit_logs`;
CREATE TABLE IF NOT EXISTS `archived_attendance` LIKE `attendance`;
CREATE TABLE IF NOT EXISTS `archived_store_transactions` LIKE `store_transactions`;

CREATE TABLE IF NOT EXISTS `retention_policies` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `target_table` VARCHAR(255) NOT NULL,
  `retain_for_days` INT NOT NULL,
  `action` ENUM('archive','delete') DEFAULT 'archive',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pii_consent` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `consent_text` TEXT NULL,
  `consented_at` TIMESTAMP NULL,
  `withdrawn_at` TIMESTAMP NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_pii_consent_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SLA & Escalation rules for support tickets
CREATE TABLE IF NOT EXISTS `support_ticket_slas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `priority` ENUM('low','normal','high','urgent') DEFAULT 'normal',
  `response_time_minutes` INT DEFAULT 1440,
  `resolve_time_minutes` INT DEFAULT 10080,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_sla_escalations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sla_id` BIGINT UNSIGNED NOT NULL,
  `step_order` INT NOT NULL,
  `escalate_after_minutes` INT NOT NULL,
  `notify_role_id` BIGINT UNSIGNED NULL,
  `notify_user_id` BIGINT UNSIGNED NULL,
  `action` ENUM('notify','reassign','escalate') DEFAULT 'notify',
  FOREIGN KEY (`sla_id`) REFERENCES `support_ticket_slas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`notify_role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`notify_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_sla_escalations_sla` (`sla_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Re-enable checks after import
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;