-- ============================================================
-- Schema Update v8
-- Run this AFTER schema_update_v7.sql.
-- Adds a full double-entry accounting layer: Chart of Accounts, General
-- Ledger (journal entries), Trial Balance, Income Statement (P&L), Balance
-- Sheet, Accounts Payable, and Accounts Receivable.
-- ============================================================

-- ---------------------------------------------------------
-- Account types: classify each account for financial statement placement.
-- statement: which financial statement it appears on.
-- classification: 'asset'/'liability'/'equity' - only meaningful for
--   balance_sheet types (income_statement types are grouped by name instead).
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS account_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  statement ENUM('balance_sheet','income_statement') NOT NULL,
  classification ENUM('asset','liability','equity') NULL,
  normal_balance ENUM('debit','credit') NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT IGNORE INTO account_types (name, statement, classification, normal_balance, sort_order) VALUES
 ('Property, Plant and Equipment', 'balance_sheet', 'asset', 'debit', 10),
 ('Inventories', 'balance_sheet', 'asset', 'debit', 20),
 ('Trade Receivables', 'balance_sheet', 'asset', 'debit', 30),
 ('Other Current Asset', 'balance_sheet', 'asset', 'debit', 40),
 ('Cash and Cash Equivalents', 'balance_sheet', 'asset', 'debit', 50),
 ('Trade Payables', 'balance_sheet', 'liability', 'credit', 60),
 ('Other Current Liability', 'balance_sheet', 'liability', 'credit', 70),
 ('Other Non Current Liability', 'balance_sheet', 'liability', 'credit', 80),
 ('Share Capital', 'balance_sheet', 'equity', 'credit', 90),
 ('Retained Earnings', 'balance_sheet', 'equity', 'credit', 100),
 ('Non Distributable Reserves', 'balance_sheet', 'equity', 'credit', 110),
 ('Revenue', 'income_statement', NULL, 'credit', 120),
 ('Other Income', 'income_statement', NULL, 'credit', 130),
 ('Cost of Sales', 'income_statement', NULL, 'debit', 140),
 ('Administrative Expense', 'income_statement', NULL, 'debit', 150),
 ('Other Expense', 'income_statement', NULL, 'debit', 160),
 ('Profit/Loss on Sale of Non-Current Asset', 'income_statement', NULL, 'credit', 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 with the chart of accounts supplied. Note: account 41001 was listed
-- twice with identical details in the source list (inserted once here,
-- de-duplicated by its unique account_code). Account 81000 was supplied with
-- description "Income-Interest Received" under type "Profit/Loss on Sale of
-- Non-Current Asset" - that description looked like a copy/paste mismatch
-- from account 41001, so it's seeded here as "Profit/Loss on Disposal"
-- instead; rename it on the Chart of Accounts page if you intended something
-- else.
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';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '00071', 'Inventory-Cellar Beverage', id FROM account_types WHERE name='Inventories';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '12000', 'City Ledger-Accounts Receivables', id FROM account_types WHERE name='Trade Receivables';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '13100', 'Prepayments', id FROM account_types WHERE name='Other Current Asset';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '16300', 'POS POLARIS', id FROM account_types WHERE name='Cash and Cash Equivalents';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '20000', 'Trade Payables', id FROM account_types WHERE name='Trade Payables';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '21000', 'Cash Account', id FROM account_types WHERE name='Other Current Liability';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '24100', 'SDY-NHIS', id FROM account_types WHERE name='Other Non Current Liability';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '25100', 'Deferred Audit Fee', id FROM account_types WHERE name='Other Current Asset';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '30000', 'Ordinary Share', id FROM account_types WHERE name='Share Capital';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '32000', 'Retained Earnings-Current Year', id FROM account_types WHERE name='Retained Earnings';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '34000', 'Asset Revaluation Reserves', id FROM account_types WHERE name='Non Distributable Reserves';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '40100', 'Income Room', id FROM account_types WHERE name='Revenue';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '41001', 'Income-Interest Received', id FROM account_types WHERE name='Other Income';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '50100', 'Cost of Sales - Accommodation', id FROM account_types WHERE name='Cost of Sales';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '70000', 'Salary & Wages', id FROM account_types WHERE name='Administrative Expense';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '70102', 'Travelling Expense', id FROM account_types WHERE name='Other Expense';
INSERT IGNORE INTO chart_of_accounts (account_code, description, account_type_id)
SELECT '81000', 'Profit/Loss on Disposal', id FROM account_types WHERE name='Profit/Loss on Sale of Non-Current Asset';

-- ---------------------------------------------------------
-- General Ledger: journal entries (header) + lines (double-entry)
-- ---------------------------------------------------------
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(30) DEFAULT 'MANUAL', -- 'MANUAL','AP_BILL','AP_PAYMENT','AR_INVOICE','AR_RECEIPT'
  reference_id INT NULL,
  status ENUM('posted','voided') DEFAULT 'posted',
  voided_by_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) 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) DEFAULT 0,
  credit DECIMAL(14,2) 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 Payable (bills against existing suppliers + payments)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS ap_bills (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bill_number VARCHAR(30) UNIQUE NOT NULL,
  supplier_id INT NOT NULL,
  grn_id INT NULL, -- optional link back to a goods received note, for future integration
  expense_account_id INT NOT NULL, -- what was purchased (debit side)
  bill_date DATE NOT NULL,
  due_date DATE NULL,
  amount DECIMAL(14,2) NOT NULL,
  amount_paid DECIMAL(14,2) DEFAULT 0,
  status ENUM('open','partially_paid','paid','void') DEFAULT 'open',
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  FOREIGN KEY (grn_id) REFERENCES goods_received_notes(id) ON DELETE SET NULL,
  FOREIGN KEY (expense_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ap_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bill_id INT NOT NULL,
  cash_account_id INT NOT NULL, -- cash/bank account credited
  payment_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  payment_method VARCHAR(50) NULL,
  reference VARCHAR(100) NULL,
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (bill_id) REFERENCES ap_bills(id),
  FOREIGN KEY (cash_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Accounts Receivable (debtors/customers, invoices, receipts)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS ar_customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  contact_person VARCHAR(100),
  phone VARCHAR(30),
  email VARCHAR(100),
  address VARCHAR(255),
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ar_invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(30) UNIQUE NOT NULL,
  customer_id INT NOT NULL,
  revenue_account_id INT NOT NULL, -- what income this invoice represents (credit side)
  invoice_date DATE NOT NULL,
  due_date DATE NULL,
  amount DECIMAL(14,2) NOT NULL,
  amount_received DECIMAL(14,2) DEFAULT 0,
  status ENUM('open','partially_received','received','void') DEFAULT 'open',
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES ar_customers(id),
  FOREIGN KEY (revenue_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ar_receipts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  deposit_account_id INT NOT NULL, -- cash/bank account debited
  receipt_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  payment_method VARCHAR(50) NULL,
  reference VARCHAR(100) NULL,
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id),
  FOREIGN KEY (deposit_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Give admin access to the new accounting module by default (everyone else
-- starts at none, same pattern as every other permission)
INSERT IGNORE INTO role_permissions (role, permission_code) VALUES
 ('admin','accounting.coa_manage'), ('admin','accounting.journal_post'),
 ('admin','accounting.reports_view'), ('admin','accounting.ap_manage'), ('admin','accounting.ar_manage');
