-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · General Ledger foundation
-- journal_entries + journal_lines (double-entry).
-- Every cash-out / cash-in event posts a JE. Sum(debit) MUST equal Sum(credit).
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS journal_lines;
DROP TABLE IF EXISTS journal_entries;

CREATE TABLE journal_entries (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  je_no             VARCHAR(30)  NOT NULL,
  entry_date        DATE         NOT NULL,
  source_type       VARCHAR(30)  NOT NULL DEFAULT 'JOURNAL', -- PAYMENT|RECEIPT|INVOICE|OPENING|MANUAL|REVERSAL
  source_id         INT          NOT NULL DEFAULT 0,
  source_ref        VARCHAR(60)  NOT NULL DEFAULT '',        -- e.g. PAY-2026-0001
  narrative         TEXT,
  currency          VARCHAR(10)  NOT NULL DEFAULT 'TZS',
  fx_rate           DECIMAL(20,6) NOT NULL DEFAULT 1.000000,
  total_debit       DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  total_credit      DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  status            VARCHAR(20)  NOT NULL DEFAULT 'POSTED',  -- POSTED|REVERSED
  reversed_by_je_id INT          NOT NULL DEFAULT 0,         -- self-FK when this JE reverses another
  posted_by         INT          NOT NULL DEFAULT 0,
  posted_by_name    VARCHAR(120) NOT NULL DEFAULT '',
  posted_on         TIMESTAMP    NULL DEFAULT NULL,
  created_by        INT          NOT NULL DEFAULT 0,
  created_on        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by         INT          NOT NULL DEFAULT 0,
  edited_on         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted           TINYINT(1)   NOT NULL DEFAULT 0,
  UNIQUE KEY uq_je_no (je_no),
  KEY ix_source (source_type, source_id),
  KEY ix_entry_date (entry_date),
  KEY ix_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE journal_lines (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  journal_entry_id INT NOT NULL,
  line_no          INT NOT NULL DEFAULT 1,
  account_id       INT NOT NULL,                          -- FK to chart_of_accounts
  account_code     VARCHAR(20)  NOT NULL DEFAULT '',      -- denormalized for read-perf
  account_name     VARCHAR(160) NOT NULL DEFAULT '',      -- denormalized
  debit            DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  credit           DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  narrative        VARCHAR(255) NOT NULL DEFAULT '',
  -- Analytical tagging (nullable — used for reports) ------------------------
  project_id       INT NOT NULL DEFAULT 0,
  lpo_id           INT NOT NULL DEFAULT 0,
  payment_id       INT NOT NULL DEFAULT 0,
  bank_account_id  INT NOT NULL DEFAULT 0,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_je (journal_entry_id),
  KEY ix_account (account_id),
  KEY ix_project (project_id),
  KEY ix_bank (bank_account_id),
  KEY ix_lpo (lpo_id),
  KEY ix_payment (payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Extend budget_item_categories with a default expense COA mapping ──────

ALTER TABLE budget_item_categories
  ADD COLUMN default_expense_coa_id INT NOT NULL DEFAULT 0 AFTER description;

-- ── Extend chart_of_accounts with 7 line-level expense accounts ───────────

INSERT IGNORE INTO chart_of_accounts (account_code, account_name, account_type, parent_id, is_bank, is_control, is_active, currency) VALUES
  ('6210', 'Transport & Fuel',            'EXPENSE', 0, 0, 0, 1, 'TZS'),
  ('6220', 'Manpower & Site Labour',      'EXPENSE', 0, 0, 0, 1, 'TZS'),
  ('6230', 'Materials & Consumables',     'EXPENSE', 0, 0, 0, 1, 'TZS'),
  ('6240', 'Equipment Hire',              'EXPENSE', 0, 0, 0, 1, 'TZS'),
  ('6250', 'Subcontract',                 'EXPENSE', 0, 0, 0, 1, 'TZS'),
  ('6260', 'Mobilisation',                'EXPENSE', 0, 0, 0, 1, 'TZS'),
  ('6290', 'Other Operating Expenses',    'EXPENSE', 0, 0, 0, 1, 'TZS');

-- Mark 1200 Bank & Cash as a bank-family account so future auto-picking works
UPDATE chart_of_accounts SET is_bank = 1 WHERE account_code IN ('1200');

-- Wire bank_accounts.coa_id to 1200 (was left NULL/0 in seed) --------------
UPDATE bank_accounts
   SET coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '1200' LIMIT 1)
 WHERE coa_id = 0 OR coa_id IS NULL;

-- Map budget_item_categories → default expense COA -------------------------

UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6230' LIMIT 1) WHERE category_code = 'MAT';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6220' LIMIT 1) WHERE category_code = 'MAN';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6240' LIMIT 1) WHERE category_code = 'EQP';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6290' LIMIT 1) WHERE category_code = 'TOL';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6260' LIMIT 1) WHERE category_code = 'MOB';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6210' LIMIT 1) WHERE category_code = 'TRP';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6250' LIMIT 1) WHERE category_code = 'SUB';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6290' LIMIT 1) WHERE category_code = 'OVH';
UPDATE budget_item_categories SET default_expense_coa_id = (SELECT c0d FROM chart_of_accounts WHERE account_code = '6290' LIMIT 1) WHERE category_code = 'CNT';
