-- ============================================================
-- Schema Update v9
-- Run this AFTER schema_update_v8.sql.
-- Core double-entry accounting: Chart of Accounts + General Ledger.
-- Everything else (Trial Balance, P&L, Balance Sheet, AR, AP) is built on
-- top of these two tables.
-- ============================================================

-- ---------------------------------------------------------
-- Account Types (classification that drives which financial statement an
-- account appears on, and which side - debit or credit - increases it)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS account_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  classification ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  normal_balance ENUM('debit','credit') NOT NULL,
  statement ENUM('balance_sheet','income_statement') NOT NULL,
  sort_order INT DEFAULT 0
) ENGINE=InnoDB;

INSERT IGNORE INTO account_types (name, classification, normal_balance, statement, sort_order) VALUES
 ('Property, Plant and Equipment', 'asset', 'debit', 'balance_sheet', 10),
 ('Inventories', 'asset', 'debit', 'balance_sheet', 20),
 ('Trade Receivables', 'asset', 'debit', 'balance_sheet', 30),
 ('Other Current Asset', 'asset', 'debit', 'balance_sheet', 40),
 ('Cash and Cash Equivalents', 'asset', 'debit', 'balance_sheet', 50),
 ('Trade Payables', 'liability', 'credit', 'balance_sheet', 60),
 ('Other Current Liability', 'liability', 'credit', 'balance_sheet', 70),
 ('Other Non Current Liability', 'liability', 'credit', 'balance_sheet', 80),
 ('Share Capital', 'equity', 'credit', 'balance_sheet', 90),
 ('Retained Earnings', 'equity', 'credit', 'balance_sheet', 100),
 ('Non Distributable Reserves', 'equity', 'credit', 'balance_sheet', 110),
 ('Revenue', 'revenue', 'credit', 'income_statement', 120),
 ('Other Income', 'revenue', 'credit', 'income_statement', 130),
 ('Cost of Sales', 'expense', 'debit', 'income_statement', 140),
 ('Administrative Expense', 'expense', 'debit', 'income_statement', 150),
 ('Other Expense', 'expense', 'debit', 'income_statement', 160),
 ('Profit/Loss on Sale of Non-Current Asset', 'revenue', 'credit', 'income_statement', 170);

-- ---------------------------------------------------------
-- Chart of Accounts
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS chart_of_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_code VARCHAR(20) NOT NULL UNIQUE,
  description VARCHAR(150) NOT NULL,
  account_type_id INT NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_type_id) REFERENCES account_types(id)
) ENGINE=InnoDB;

-- Seed data as supplied. Note: account code 41001 ("Income-Interest
-- Received") was listed three times with different Account Types in the
-- original request - since codes must be unique, only the first ("Other
-- Income") is seeded here. Add a second account (e.g. 41002) manually via
-- Chart of Accounts if you also want a distinct "Profit/Loss on Sale of
-- Non-Current Asset" line. "Deefred Audit Fee" has been corrected to
-- "Deferred Audit Fee" as an apparent typo.
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '00010', 'Leasehold Improvement', id FROM account_types WHERE name='Property, Plant and Equipment'
UNION ALL SELECT '00071', 'Inventory-Cellar Beverage', id FROM account_types WHERE name='Inventories'
UNION ALL SELECT '12000', 'City Ledger-Accounts Receivables', id FROM account_types WHERE name='Trade Receivables'
UNION ALL SELECT '13100', 'Prepayments', id FROM account_types WHERE name='Other Current Asset'
UNION ALL SELECT '16300', 'POS POLARIS', id FROM account_types WHERE name='Cash and Cash Equivalents'
UNION ALL SELECT '20000', 'Trade Payables', id FROM account_types WHERE name='Trade Payables'
UNION ALL SELECT '21000', 'Cash Account', id FROM account_types WHERE name='Other Current Liability'
UNION ALL SELECT '24100', 'SDY-NHIS', id FROM account_types WHERE name='Other Non Current Liability'
UNION ALL SELECT '25100', 'Deferred Audit Fee', id FROM account_types WHERE name='Other Current Asset'
UNION ALL SELECT '30000', 'Ordinary Share', id FROM account_types WHERE name='Share Capital'
UNION ALL SELECT '32000', 'Retained Earnings-Current Year', id FROM account_types WHERE name='Retained Earnings'
UNION ALL SELECT '34000', 'Asset Revaluation Reserves', id FROM account_types WHERE name='Non Distributable Reserves'
UNION ALL SELECT '40100', 'Income Room', id FROM account_types WHERE name='Revenue'
UNION ALL SELECT '41001', 'Income-Interest Received', id FROM account_types WHERE name='Other Income'
UNION ALL SELECT '50100', 'Cost of Sales - Accommodation', id FROM account_types WHERE name='Cost of Sales'
UNION ALL SELECT '70000', 'Salary & Wages', id FROM account_types WHERE name='Administrative Expense'
UNION ALL SELECT '70102', 'Travelling Expense', id FROM account_types WHERE name='Other Expense';

-- ---------------------------------------------------------
-- Journal Entries (the General Ledger is simply every posted line below)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS journal_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entry_number VARCHAR(30) UNIQUE NOT NULL,
  entry_date DATE NOT NULL,
  description VARCHAR(255) NULL,
  reference_type VARCHAR(40) NULL, -- e.g. 'AP_BILL','AP_PAYMENT','AR_INVOICE','AR_RECEIPT','MANUAL'
  reference_id INT NULL,
  status ENUM('posted','voided') DEFAULT 'posted',
  voided_by_entry_id INT NULL, -- points to the reversing entry, if voided
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS journal_entry_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entry_id INT NOT NULL,
  account_id INT NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  description VARCHAR(255) NULL,
  FOREIGN KEY (entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
  FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id)
) ENGINE=InnoDB;

CREATE INDEX idx_jel_account ON journal_entry_lines (account_id);
CREATE INDEX idx_je_date ON journal_entries (entry_date);

-- accounts role needs this module by default; everyone else stays at none
-- until turned on via Role Permissions (same pattern as every other module)
INSERT IGNORE INTO role_permissions (role, permission_code) VALUES
 ('accounts','accounting.coa_manage'),
 ('accounts','accounting.journal_post'),
 ('accounts','accounting.reports_view'),
 ('accounts','accounting.ap_manage'),
 ('accounts','accounting.ar_manage'),
 ('admin','accounting.coa_manage'),
 ('admin','accounting.journal_post'),
 ('admin','accounting.reports_view'),
 ('admin','accounting.ap_manage'),
 ('admin','accounting.ar_manage');
