-- --------------------------------------------------------
-- Migration: Add FinLite General Ledger (GL) to STUMIS
-- --------------------------------------------------------

-- 1. Chart of Accounts Table
CREATE TABLE IF NOT EXISTS `tbl_gl_accounts` (
  `account_id` int(11) NOT NULL AUTO_INCREMENT,
  `account_code` varchar(50) NOT NULL,
  `account_name` varchar(150) NOT NULL,
  `account_type` enum('Asset','Liability','Equity','Revenue','Expense') NOT NULL,
  `description` text DEFAULT NULL,
  `is_postable` tinyint(1) DEFAULT 1,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `idx_account_code` (`account_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Fiscal Periods Table
CREATE TABLE IF NOT EXISTS `tbl_gl_fiscal_periods` (
  `period_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_name` varchar(100) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `is_closed` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Journal Entries (Header) Table
CREATE TABLE IF NOT EXISTS `tbl_gl_journal_entries` (
  `entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_number` varchar(100) NOT NULL,
  `entry_date` date NOT NULL,
  `period_id` int(11) DEFAULT NULL,
  `journal_type` varchar(50) DEFAULT 'general',
  `reference` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` enum('draft','posted','reversed') DEFAULT 'draft',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`entry_id`),
  KEY `idx_entry_date` (`entry_date`),
  KEY `fk_period` (`period_id`),
  CONSTRAINT `fk_gl_period` FOREIGN KEY (`period_id`) REFERENCES `tbl_gl_fiscal_periods` (`period_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Journal Lines (Debits and Credits) Table
CREATE TABLE IF NOT EXISTS `tbl_gl_journal_lines` (
  `line_id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  `debit` decimal(15,2) DEFAULT 0.00,
  `credit` decimal(15,2) DEFAULT 0.00,
  PRIMARY KEY (`line_id`),
  KEY `fk_entry` (`entry_id`),
  KEY `fk_account` (`account_id`),
  CONSTRAINT `fk_gl_entry` FOREIGN KEY (`entry_id`) REFERENCES `tbl_gl_journal_entries` (`entry_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gl_account` FOREIGN KEY (`account_id`) REFERENCES `tbl_gl_accounts` (`account_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Seed Data: Educational Chart of Accounts with Explicit IDs
-- --------------------------------------------------------
INSERT INTO `tbl_gl_accounts` (`account_id`, `account_code`, `account_name`, `account_type`, `description`, `is_postable`, `is_active`) VALUES
(1000, '1000', 'Cash and Bank Equivalents', 'Asset', 'Main Bank Accounts', 1, 1),
(1200, '1200', 'Accounts Receivable - Students', 'Asset', 'Unpaid Tuition and Fees', 1, 1),
(2000, '2000', 'Accounts Payable', 'Liability', 'Owed to Suppliers', 1, 1),
(3000, '3000', 'Retained Earnings', 'Equity', 'Retained Earnings', 1, 1),
(4000, '4000', 'Tuition Revenue', 'Revenue', 'Income from Student Tuition', 1, 1),
(4100, '4100', 'Registration Fee Revenue', 'Revenue', 'Income from Registration', 1, 1),
(4200, '4200', 'Other Income', 'Revenue', 'Other operating income', 1, 1),
(5000, '5000', 'Payroll Expenses', 'Expense', 'Staff Salaries and Wages', 1, 1),
(5100, '5100', 'Administrative Expenses', 'Expense', 'General office expenses', 1, 1),
(5200, '5200', 'Bank Charges', 'Expense', 'Bank fees and charges', 1, 1)
ON DUPLICATE KEY UPDATE `account_name` = VALUES(`account_name`);

-- Seed Data: Fiscal Periods
INSERT INTO `tbl_gl_fiscal_periods` (`period_id`, `period_name`, `start_date`, `end_date`, `is_closed`) VALUES
(1, 'Historical Period', '1970-01-01', '2025-12-31', 0),
(2, 'FY-2026', '2026-01-01', '2026-12-31', 0)
ON DUPLICATE KEY UPDATE `period_name` = VALUES(`period_name`);