-- ============================================================================
--  General Ledger / Journals
--  Created : 2026-08-11
--
--  Adds double-entry bookkeeping on top of the existing AR/AP documents.
--
--  The legacy `tbl_journal_entries` table is left untouched on purpose: it is
--  a single-row-per-transaction shape (one debit account, one credit account)
--  that cannot express a 3+ line entry such as "invoice + VAT", and nothing in
--  the codebase reads or writes it. The new ledger uses the `tbl_gl_` prefix.
--
--  Multi-currency: entries are kept in the currency of their source document
--  and the reports group by currency. No FX rate table exists in this system,
--  so nothing here converts between currencies - that would mean inventing
--  rates. Consolidation into a single reporting currency needs a rate table
--  first.
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. Chart of accounts
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_gl_accounts` (
  `gl_account_id`  INT(11)      NOT NULL AUTO_INCREMENT,
  `account_code`   VARCHAR(20)  NOT NULL,
  `account_name`   VARCHAR(255) NOT NULL,
  `account_type`   ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  `normal_balance` ENUM('debit','credit') NOT NULL,
  `parent_id`      INT(11)      DEFAULT NULL,
  -- A header account (e.g. "Cash and Bank") groups children and takes no lines.
  `is_postable`    TINYINT(1)   NOT NULL DEFAULT 1,
  -- Ties a GL account back to an operational record so posting can resolve it:
  -- 'cash_account' -> tbl_accounts.account_id, 'expense_category' -> tbl_expense_categories.category_id
  `link_type`      ENUM('none','cash_account','expense_category') NOT NULL DEFAULT 'none',
  `link_id`        INT(11)      DEFAULT NULL,
  `description`    VARCHAR(255) DEFAULT NULL,
  `is_system`      TINYINT(1)   NOT NULL DEFAULT 0,
  `status`         TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`gl_account_id`),
  UNIQUE KEY `uniq_gl_account_code` (`account_code`),
  KEY `idx_gl_account_type` (`account_type`),
  KEY `idx_gl_account_link` (`link_type`, `link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------------
-- 2. Fiscal periods - a closed period refuses new postings
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_gl_fiscal_periods` (
  `period_id`   INT(11)     NOT NULL AUTO_INCREMENT,
  `fiscal_year` SMALLINT(6) NOT NULL,
  `period_no`   TINYINT(4)  NOT NULL,
  `period_name` VARCHAR(50) NOT NULL,
  `start_date`  DATE        NOT NULL,
  `end_date`    DATE        NOT NULL,
  `status`      ENUM('open','closed') NOT NULL DEFAULT 'open',
  `closed_by`   INT(11)     DEFAULT NULL,
  `closed_at`   DATETIME    DEFAULT NULL,
  `created_at`  DATETIME    DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`period_id`),
  UNIQUE KEY `uniq_gl_period` (`fiscal_year`, `period_no`),
  KEY `idx_gl_period_range` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------------
-- 3. Journal entry header
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_gl_journal_entries` (
  `entry_id`      INT(11)     NOT NULL AUTO_INCREMENT,
  `entry_number`  VARCHAR(30) NOT NULL,
  `entry_date`    DATE        NOT NULL,
  `period_id`     INT(11)     DEFAULT NULL,
  `journal_type`  ENUM('general','sales','purchase','cash_receipt','cash_payment','opening','adjustment') NOT NULL DEFAULT 'general',
  `reference`     VARCHAR(100) DEFAULT NULL,
  `description`   TEXT        DEFAULT NULL,
  `currency_code` VARCHAR(10) NOT NULL DEFAULT 'RWF',
  -- source_type/source_id link an auto-generated entry back to the document it
  -- came from. The unique key is what makes re-running the sync idempotent;
  -- manual entries leave both NULL and MySQL allows repeated NULLs in a
  -- unique index, so they are never blocked by it.
  --
  -- source_revision lets a document be re-posted after it was edited: the stale
  -- entry is reversed and a fresh one is written at the next revision, so both
  -- stay linked to the document instead of the correction losing its origin.
  `source_type`     VARCHAR(50) DEFAULT NULL,
  `source_id`       INT(11)     DEFAULT NULL,
  `source_revision` SMALLINT(6) NOT NULL DEFAULT 1,
  `status`        ENUM('draft','posted','reversed') NOT NULL DEFAULT 'draft',
  `reversal_of`   INT(11)     DEFAULT NULL,
  `total_debit`   DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `total_credit`  DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `created_by`    INT(11)     DEFAULT NULL,
  `created_at`    DATETIME    DEFAULT CURRENT_TIMESTAMP,
  `posted_by`     INT(11)     DEFAULT NULL,
  `posted_at`     DATETIME    DEFAULT NULL,
  PRIMARY KEY (`entry_id`),
  UNIQUE KEY `uniq_gl_entry_number` (`entry_number`),
  UNIQUE KEY `uniq_gl_entry_source` (`source_type`, `source_id`, `source_revision`),
  KEY `idx_gl_entry_date` (`entry_date`),
  KEY `idx_gl_entry_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------------
-- 4. Journal entry lines
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_gl_journal_lines` (
  `line_id`       INT(11)      NOT NULL AUTO_INCREMENT,
  `entry_id`      INT(11)      NOT NULL,
  `line_no`       SMALLINT(6)  NOT NULL DEFAULT 1,
  `gl_account_id` INT(11)      NOT NULL,
  `debit`         DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `credit`        DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `description`   VARCHAR(255) DEFAULT NULL,
  -- Analytical dimensions: who the line is about, so the ledger can be sliced
  -- per client / vendor / bank account without another join table.
  `client_id`     INT(11)      DEFAULT NULL,
  `vendor_id`     INT(11)      DEFAULT NULL,
  `account_id`    INT(11)      DEFAULT NULL,
  PRIMARY KEY (`line_id`),
  KEY `idx_gl_line_entry` (`entry_id`),
  KEY `idx_gl_line_account` (`gl_account_id`),
  CONSTRAINT `fk_gl_line_entry` FOREIGN KEY (`entry_id`)
      REFERENCES `tbl_gl_journal_entries` (`entry_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gl_line_account` FOREIGN KEY (`gl_account_id`)
      REFERENCES `tbl_gl_accounts` (`gl_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------------
-- 5. Posting rules - which GL account each document type hits
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_gl_settings` (
  `setting_key`   VARCHAR(60)  NOT NULL,
  `gl_account_id` INT(11)      DEFAULT NULL,
  `description`   VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`setting_key`),
  CONSTRAINT `fk_gl_setting_account` FOREIGN KEY (`gl_account_id`)
      REFERENCES `tbl_gl_accounts` (`gl_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================================
--  SEED
-- ============================================================================

-- 5.1 Base chart of accounts -------------------------------------------------
INSERT IGNORE INTO `tbl_gl_accounts`
  (`account_code`, `account_name`, `account_type`, `normal_balance`, `is_postable`, `is_system`, `description`)
VALUES
  ('1000', 'Cash and Bank',             'asset',     'debit',  0, 1, 'Header for every cash, bank and mobile money account'),
  ('1200', 'Accounts Receivable',       'asset',     'debit',  1, 1, 'Control account - what clients owe'),
  ('1400', 'Prepaid Expenses',          'asset',     'debit',  1, 0, 'Amounts paid in advance'),
  ('2000', 'Accounts Payable',          'liability', 'credit', 1, 1, 'Control account - what we owe vendors'),
  ('2300', 'VAT Payable',               'liability', 'credit', 1, 0, 'Output VAT collected on behalf of RRA'),
  ('2400', 'Payroll Liabilities',       'liability', 'credit', 1, 0, 'Net salaries and statutory deductions not yet paid'),
  ('3000', 'Share Capital',             'equity',    'credit', 1, 0, 'Owner contributions'),
  ('3900', 'Retained Earnings',         'equity',    'credit', 1, 1, 'Accumulated result of prior periods'),
  ('4000', 'Service Revenue',           'revenue',   'credit', 1, 1, 'Revenue from client invoices'),
  ('4900', 'Other Income',              'revenue',   'credit', 1, 0, 'Income outside normal operations'),
  ('5000', 'Cost of Services',          'expense',   'debit',  1, 1, 'Vendor invoices and direct costs'),
  ('5200', 'Operating Expenses',        'expense',   'debit',  0, 1, 'Header for the expense categories'),
  ('5900', 'General Expenses',          'expense',   'debit',  1, 1, 'Fallback when an expense has no mapped category'),
  ('5950', 'Bank Charges',              'expense',   'debit',  1, 1, 'Transaction fees charged on payments');

-- Parent links for the two header accounts
UPDATE `tbl_gl_accounts` c
  JOIN `tbl_gl_accounts` p ON p.`account_code` = '5200'
  SET c.`parent_id` = p.`gl_account_id`
  WHERE c.`account_code` IN ('5900', '5950') AND c.`parent_id` IS NULL;

-- 5.2 One asset account per cash/bank account -------------------------------
-- Codes are derived from account_id so re-running the migration is stable and
-- never renumbers an account that already carries postings. The 11xxx range is
-- used rather than 10xx so it cannot collide with 1200 / 1400 below.
INSERT IGNORE INTO `tbl_gl_accounts`
  (`account_code`, `account_name`, `account_type`, `normal_balance`, `parent_id`, `is_postable`, `link_type`, `link_id`, `is_system`, `description`)
SELECT
  CONCAT('11', LPAD(a.`account_id`, 3, '0')),
  a.`account_name`,
  'asset',
  'debit',
  (SELECT `gl_account_id` FROM `tbl_gl_accounts` WHERE `account_code` = '1000'),
  1,
  'cash_account',
  a.`account_id`,
  1,
  CONCAT('Cash/bank account #', a.`account_id`)
FROM `tbl_accounts` a;

-- 5.3 One expense account per expense category ------------------------------
INSERT IGNORE INTO `tbl_gl_accounts`
  (`account_code`, `account_name`, `account_type`, `normal_balance`, `parent_id`, `is_postable`, `link_type`, `link_id`, `is_system`, `description`)
SELECT
  CONCAT('52', LPAD(c.`category_id`, 3, '0')),
  c.`category_name`,
  'expense',
  'debit',
  (SELECT `gl_account_id` FROM `tbl_gl_accounts` WHERE `account_code` = '5200'),
  1,
  'expense_category',
  c.`category_id`,
  1,
  CONCAT('Expense category #', c.`category_id`)
FROM `tbl_expense_categories` c;

-- 5.4 Posting rules ---------------------------------------------------------
INSERT IGNORE INTO `tbl_gl_settings` (`setting_key`, `gl_account_id`, `description`)
SELECT 'ar_control',       `gl_account_id`, 'Debited by client invoices, credited by client payments'      FROM `tbl_gl_accounts` WHERE `account_code` = '1200'
UNION ALL
SELECT 'ap_control',       `gl_account_id`, 'Credited by vendor invoices, debited by vendor payments'      FROM `tbl_gl_accounts` WHERE `account_code` = '2000'
UNION ALL
SELECT 'sales_revenue',    `gl_account_id`, 'Credited by client invoices, debited by credit notes'         FROM `tbl_gl_accounts` WHERE `account_code` = '4000'
UNION ALL
SELECT 'purchase_expense', `gl_account_id`, 'Debited by vendor invoices, credited by debit notes'          FROM `tbl_gl_accounts` WHERE `account_code` = '5000'
UNION ALL
SELECT 'vat_payable',      `gl_account_id`, 'Output VAT - only used when an invoice carries a tax amount'  FROM `tbl_gl_accounts` WHERE `account_code` = '2300'
UNION ALL
SELECT 'bank_charges',     `gl_account_id`, 'Transaction fees recorded alongside a payment'                FROM `tbl_gl_accounts` WHERE `account_code` = '5950'
UNION ALL
SELECT 'default_expense',  `gl_account_id`, 'Used when an expense category has no dedicated GL account'    FROM `tbl_gl_accounts` WHERE `account_code` = '5900'
UNION ALL
SELECT 'default_cash',     `gl_account_id`, 'Used when a payment has no cash/bank account attached'        FROM `tbl_gl_accounts` WHERE `account_code` = '5900'
UNION ALL
SELECT 'retained_earnings',`gl_account_id`, 'Carries the accumulated result when a year is closed'         FROM `tbl_gl_accounts` WHERE `account_code` = '3900';

-- default_cash should point at a real bank account, not the expense fallback:
-- pick the lowest-numbered cash account if one exists.
UPDATE `tbl_gl_settings` s
  SET s.`gl_account_id` = COALESCE(
        (SELECT `gl_account_id` FROM `tbl_gl_accounts`
          WHERE `link_type` = 'cash_account' ORDER BY `link_id` LIMIT 1),
        s.`gl_account_id`)
  WHERE s.`setting_key` = 'default_cash';

-- 5.5 Monthly periods for the current fiscal year ---------------------------
-- Further years are generated on demand by the API; entries dated outside any
-- existing period get one created automatically, open.
INSERT IGNORE INTO `tbl_gl_fiscal_periods` (`fiscal_year`, `period_no`, `period_name`, `start_date`, `end_date`)
SELECT
  YEAR(CURDATE()),
  m.n,
  CONCAT(DATE_FORMAT(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.n - 1) MONTH, '%M'), ' ', YEAR(CURDATE())),
  MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.n - 1) MONTH,
  LAST_DAY(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.n - 1) MONTH)
FROM (
  SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
  UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) m;

-- ============================================================================
--  VERIFY - read the output of these four queries after running the migration.
--
--  The whole script is safe to run again: every table is CREATE TABLE IF NOT
--  EXISTS and every seed is INSERT IGNORE, so nothing already in place is
--  touched or renumbered.
-- ============================================================================

-- 1. Counts. Expect: 14 base accounts + one per cash account + one per expense
--    category; 9 posting rules; 12 periods for the current year.
SELECT
  (SELECT COUNT(*) FROM `tbl_gl_accounts`)                                    AS gl_accounts,
  (SELECT COUNT(*) FROM `tbl_gl_accounts` WHERE `link_type` = 'cash_account') AS cash_accounts_mapped,
  (SELECT COUNT(*) FROM `tbl_accounts`)                                       AS cash_accounts_total,
  (SELECT COUNT(*) FROM `tbl_gl_accounts` WHERE `link_type` = 'expense_category') AS categories_mapped,
  (SELECT COUNT(*) FROM `tbl_expense_categories`)                             AS categories_total,
  (SELECT COUNT(*) FROM `tbl_gl_settings` WHERE `gl_account_id` IS NOT NULL)  AS posting_rules_set,
  (SELECT COUNT(*) FROM `tbl_gl_fiscal_periods`)                              AS periods;

-- 2. Cash/bank accounts with no GL account. Must return no rows - a payment on
--    an unmapped account falls back to the default and lands in the wrong place.
SELECT a.`account_id`, a.`account_name`
  FROM `tbl_accounts` a
 WHERE NOT EXISTS (SELECT 1 FROM `tbl_gl_accounts` g
                    WHERE g.`link_type` = 'cash_account' AND g.`link_id` = a.`account_id`);

-- 3. Expense categories with no GL account. Must return no rows.
SELECT c.`category_id`, c.`category_name`
  FROM `tbl_expense_categories` c
 WHERE NOT EXISTS (SELECT 1 FROM `tbl_gl_accounts` g
                    WHERE g.`link_type` = 'expense_category' AND g.`link_id` = c.`category_id`);

-- 4. Posting rules that were left unset. Must return no rows.
SELECT `setting_key`, `description` FROM `tbl_gl_settings` WHERE `gl_account_id` IS NULL;