-- ═══════════════════════════════════════════════════════════════════════
-- Finance & Procurement Suite  (add-on: finance_suite)
-- Bundles: (2) expenditure approval workflow, (3) receipt attachments,
--          (4) event / excursion expense tracking.
-- Idempotent — safe to run repeatedly. The app also self-heals these tables
-- via finance/inc/finance_lib.php:finance_ensure_tables().
-- ═══════════════════════════════════════════════════════════════════════

-- (2) Expenditure requests -------------------------------------------------
CREATE TABLE IF NOT EXISTS `expense_requests` (
  `id`             INT AUTO_INCREMENT PRIMARY KEY,
  `request_no`     VARCHAR(30)  NOT NULL,
  `requester_id`   INT          NULL,
  `requester_name` VARCHAR(120) NOT NULL,
  `expense_date`   DATE         NOT NULL,
  `category`       VARCHAR(60)  NOT NULL,
  `description`    TEXT         NOT NULL,
  `amount`         DECIMAL(12,2) NOT NULL,
  `vendor`         VARCHAR(160) NULL,
  `payment_method` VARCHAR(30)  NOT NULL DEFAULT 'cash',
  `event_id`       INT          NULL,
  `status`         ENUM('pending','approved','rejected','disbursed') NOT NULL DEFAULT 'pending',
  `current_step`   INT          NOT NULL DEFAULT 0,
  `expense_id`     INT          NULL,
  `created_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_request_no` (`request_no`),
  KEY `idx_status` (`status`),
  KEY `idx_requester` (`requester_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Approval matrix (which role approves, in what order, above what amount) ----
CREATE TABLE IF NOT EXISTS `expense_approval_steps` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `step_order`    INT          NOT NULL,
  `approver_role` VARCHAR(40)  NOT NULL,
  `label`         VARCHAR(80)  NOT NULL,
  `min_amount`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `active`        TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_order` (`step_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Audit trail of every approve/reject action -------------------------------
CREATE TABLE IF NOT EXISTS `expense_approval_log` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `request_id`    INT          NOT NULL,
  `step_order`    INT          NOT NULL,
  `approver_id`   INT          NULL,
  `approver_name` VARCHAR(120) NOT NULL,
  `approver_role` VARCHAR(40)  NOT NULL,
  `action`        ENUM('approved','rejected') NOT NULL,
  `comment`       VARCHAR(500) NULL,
  `created_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_request` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- (3) Receipt / document attachments (links to a request and/or an expense) -
CREATE TABLE IF NOT EXISTS `expense_attachments` (
  `id`               INT AUTO_INCREMENT PRIMARY KEY,
  `request_id`       INT          NULL,
  `expense_id`       INT          NULL,
  `event_expense_id` INT          NULL,
  `file_path`        VARCHAR(255) NOT NULL,
  `original_name`    VARCHAR(200) NULL,
  `mime`             VARCHAR(80)  NULL,
  `size`             INT          NULL,
  `uploaded_by`      INT          NULL,
  `uploaded_by_name` VARCHAR(120) NULL,
  `created_at`       DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_request` (`request_id`),
  KEY `idx_expense` (`expense_id`),
  KEY `idx_event_expense` (`event_expense_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- (4) Event / excursion accounts -------------------------------------------
CREATE TABLE IF NOT EXISTS `event_accounts` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(160) NOT NULL,
  `event_date`  DATE         NULL,
  `location`    VARCHAR(160) NULL,
  `description` TEXT         NULL,
  `budget`      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status`      ENUM('planning','active','closed') NOT NULL DEFAULT 'planning',
  `created_by`  INT          NULL,
  `created_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `event_expenses` (
  `id`             INT AUTO_INCREMENT PRIMARY KEY,
  `event_id`       INT          NOT NULL,
  `expense_date`   DATE         NOT NULL,
  `category`       VARCHAR(60)  NOT NULL,
  `description`    VARCHAR(255) NOT NULL,
  `amount`         DECIMAL(12,2) NOT NULL,
  `vendor`         VARCHAR(160) NULL,
  `payment_method` VARCHAR(30)  NOT NULL DEFAULT 'cash',
  `created_by`     INT          NULL,
  `created_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_event` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `event_participants` (
  `id`             INT AUTO_INCREMENT PRIMARY KEY,
  `event_id`       INT          NOT NULL,
  `student_id`     INT          NOT NULL,
  `student_name`   VARCHAR(160) NULL,
  `class_name`     VARCHAR(60)  NULL,
  `amount_charged` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `amount_paid`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_event_student` (`event_id`,`student_id`),
  KEY `idx_event` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Default approval matrix (only inserted when the table is empty) -----------
INSERT INTO `expense_approval_steps` (`step_order`,`approver_role`,`label`,`min_amount`,`active`)
SELECT * FROM (
  SELECT 1 AS step_order, 'accountant' AS approver_role, 'Bursar'    AS label, 0.00    AS min_amount, 1 AS active UNION ALL
  SELECT 2,              'headteacher',                  'Principal',            1000.00,               1 UNION ALL
  SELECT 3,              'director',                     'Board',                5000.00,               1
) AS seed
WHERE NOT EXISTS (SELECT 1 FROM `expense_approval_steps`);
