-- SoftedgeLab Finance | MySQL 8.0.16+ | Proposed schema based on ER diagram V1
-- Run on an EMPTY database. It does not import or overwrite the browser prototype's data.
-- All amounts and salaries are numeric. The application must perform authentication,
-- authorization, file encryption, audit policy, and transaction reconciliation.
CREATE DATABASE IF NOT EXISTS softedgelab_finance
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE softedgelab_finance;

CREATE TABLE roles (
 role_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(40) NOT NULL UNIQUE,
 description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE employees (
 employee_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 employee_no VARCHAR(20) NOT NULL UNIQUE,
 full_name VARCHAR(160) NOT NULL,
 job_title VARCHAR(100) NOT NULL,
 work_email VARCHAR(254),
 personal_email VARCHAR(254),
 join_date DATE,
 end_date DATE,
 status ENUM('active','inactive','terminated') NOT NULL DEFAULT 'active',
 epf_base_salary DECIMAL(15,2) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT chk_employee_dates CHECK (end_date IS NULL OR join_date IS NULL OR end_date >= join_date),
 CONSTRAINT chk_employee_salary CHECK (epf_base_salary >= 0),
 INDEX idx_employee_status (status)
) ENGINE=InnoDB;

CREATE TABLE user_accounts (
 user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 role_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NULL UNIQUE,
 username VARCHAR(80) NOT NULL UNIQUE,
 email VARCHAR(254) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL COMMENT 'Argon2id or bcrypt hash generated by backend; never store plaintext',
 status ENUM('active','suspended','disabled') NOT NULL DEFAULT 'active',
 password_changed_at DATETIME NULL,
 last_login_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE RESTRICT,
 FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE employee_bank_details (
 bank_detail_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 employee_id BIGINT UNSIGNED NOT NULL,
 bank_name VARCHAR(120) NOT NULL,
 branch VARCHAR(120),
 account_holder VARCHAR(160) NOT NULL,
 account_number_ciphertext VARBINARY(1024) NOT NULL COMMENT 'Encrypted by application/KMS before insertion',
 account_number_last4 VARCHAR(4),
 is_primary BOOLEAN NOT NULL DEFAULT FALSE,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE RESTRICT,
 INDEX idx_emp_bank_employee (employee_id)
) ENGINE=InnoDB;

CREATE TABLE employee_documents (
 document_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 employee_id BIGINT UNSIGNED NOT NULL,
 document_type VARCHAR(50) NOT NULL,
 file_name VARCHAR(255) NOT NULL,
 content_type VARCHAR(100) NOT NULL,
 storage_key VARCHAR(512) NOT NULL UNIQUE,
 uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 uploaded_by_user_id BIGINT UNSIGNED NULL,
 FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE RESTRICT,
 FOREIGN KEY (uploaded_by_user_id) REFERENCES user_accounts(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE leave_requests (
 leave_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 employee_id BIGINT UNSIGNED NOT NULL,
 leave_type ENUM('annual','casual','sick','unpaid','other') NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 reason TEXT,
 status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
 submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE RESTRICT,
 CONSTRAINT chk_leave_dates CHECK(end_date >= start_date),
 INDEX idx_leave_employee_dates (employee_id,start_date,end_date),
 INDEX idx_leave_status (status)
) ENGINE=InnoDB;

CREATE TABLE leave_decisions (
 decision_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 leave_id BIGINT UNSIGNED NOT NULL,
 decided_by_user_id BIGINT UNSIGNED NOT NULL,
 decision ENUM('approved','rejected','pending') NOT NULL,
 decision_note TEXT,
 rejection_reason TEXT,
 decided_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (leave_id) REFERENCES leave_requests(leave_id) ON DELETE RESTRICT,
 FOREIGN KEY (decided_by_user_id) REFERENCES user_accounts(user_id) ON DELETE RESTRICT,
 CONSTRAINT chk_rejection_reason CHECK(decision <> 'rejected' OR (rejection_reason IS NOT NULL AND CHAR_LENGTH(TRIM(rejection_reason)) > 0)),
 INDEX idx_leave_decision_history (leave_id,decided_at)
) ENGINE=InnoDB;

CREATE TABLE email_notifications (
 notification_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 leave_id BIGINT UNSIGNED NULL,
 recipient_user_id BIGINT UNSIGNED NULL,
 recipient_email VARCHAR(254) NOT NULL,
 event_type VARCHAR(60) NOT NULL,
 subject VARCHAR(255) NOT NULL,
 delivery_status ENUM('pending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
 attempted_at DATETIME NULL,
 sent_at DATETIME NULL,
 error_message TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (leave_id) REFERENCES leave_requests(leave_id) ON DELETE RESTRICT,
 FOREIGN KEY (recipient_user_id) REFERENCES user_accounts(user_id) ON DELETE SET NULL,
 INDEX idx_mail_queue (delivery_status,created_at)
) ENGINE=InnoDB;

CREATE TABLE financial_periods (
 period_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 year SMALLINT UNSIGNED NOT NULL,
 month TINYINT UNSIGNED NOT NULL,
 period_status ENUM('open','closed','locked') NOT NULL DEFAULT 'open',
 closed_at DATETIME NULL,
 closed_by_user_id BIGINT UNSIGNED NULL,
 UNIQUE KEY uq_period (year,month),
 CONSTRAINT chk_period_month CHECK(month BETWEEN 1 AND 12),
 FOREIGN KEY (closed_by_user_id) REFERENCES user_accounts(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE clients (
 client_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(180) NOT NULL,
 contact_email VARCHAR(254),
 phone VARCHAR(40),
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE projects (
 project_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NULL,
 project_name VARCHAR(200) NOT NULL,
 channel ENUM('direct','fiverr','upwork','referral','other') NOT NULL DEFAULT 'direct',
 income_type ENUM('project','maintenance','retainer','consulting','other') NOT NULL DEFAULT 'project',
 other_channel VARCHAR(120) NULL,
 order_date DATE,
 currency CHAR(3) NOT NULL DEFAULT 'LKR',
 contract_value DECIMAL(17,2) NOT NULL DEFAULT 0,
 status ENUM('open','in_progress','completed','cancelled') NOT NULL DEFAULT 'open',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE SET NULL,
 CONSTRAINT chk_project_amount CHECK(contract_value >= 0),
 INDEX idx_project_order (order_date)
) ENGINE=InnoDB;

CREATE TABLE expense_categories (
 category_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 category_name VARCHAR(100) NOT NULL UNIQUE,
 is_active BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB;

CREATE TABLE bank_accounts (
 bank_account_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_name VARCHAR(160) NOT NULL,
 institution VARCHAR(160),
 account_type ENUM('bank','cash','wallet','card','other') NOT NULL DEFAULT 'bank',
 currency CHAR(3) NOT NULL DEFAULT 'LKR',
 opening_balance DECIMAL(17,2) NOT NULL DEFAULT 0,
 opened_on DATE,
 status ENUM('active','inactive','closed') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE income_records (
 income_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 period_id BIGINT UNSIGNED NOT NULL,
 project_id BIGINT UNSIGNED NULL,
 bank_account_id BIGINT UNSIGNED NULL,
 description VARCHAR(255) NOT NULL,
 order_date DATE NOT NULL,
 received_date DATE NULL,
 gross_amount DECIMAL(17,2) NOT NULL DEFAULT 0,
 processing_fees DECIMAL(17,2) NOT NULL DEFAULT 0,
 received_amount DECIMAL(17,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'LKR',
 exchange_rate DECIMAL(18,8) NULL COMMENT 'Original currency to LKR, if applicable',
 payment_status ENUM('pending','partial','received','cancelled') NOT NULL DEFAULT 'pending',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (period_id) REFERENCES financial_periods(period_id) ON DELETE RESTRICT,
 FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE SET NULL,
 FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(bank_account_id) ON DELETE RESTRICT,
 CONSTRAINT chk_income_money CHECK(gross_amount>=0 AND processing_fees>=0 AND received_amount>=0),
 CONSTRAINT chk_income_exchange CHECK(exchange_rate IS NULL OR exchange_rate>0),
 INDEX idx_income_order (order_date),
 INDEX idx_income_received (received_date),
 INDEX idx_income_period (period_id)
) ENGINE=InnoDB;

CREATE TABLE expenses (
 expense_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 period_id BIGINT UNSIGNED NOT NULL,
 category_id BIGINT UNSIGNED NOT NULL,
 bank_account_id BIGINT UNSIGNED NULL,
 description VARCHAR(255) NOT NULL,
 expense_date DATE NOT NULL,
 amount DECIMAL(17,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'LKR',
 other_payment_source VARCHAR(160) NULL,
 status ENUM('draft','approved','paid','void') NOT NULL DEFAULT 'paid',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (period_id) REFERENCES financial_periods(period_id) ON DELETE RESTRICT,
 FOREIGN KEY (category_id) REFERENCES expense_categories(category_id) ON DELETE RESTRICT,
 FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(bank_account_id) ON DELETE RESTRICT,
 CONSTRAINT chk_expense_amount CHECK(amount>=0),
 CONSTRAINT chk_expense_source CHECK(bank_account_id IS NOT NULL OR other_payment_source IS NOT NULL),
 INDEX idx_expense_date (expense_date),
 INDEX idx_expense_period (period_id)
) ENGINE=InnoDB;

CREATE TABLE payroll_runs (
 payroll_run_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 period_id BIGINT UNSIGNED NOT NULL,
 payment_date DATE NULL,
 status ENUM('draft','approved','paid','void') NOT NULL DEFAULT 'draft',
 approved_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (period_id) REFERENCES financial_periods(period_id) ON DELETE RESTRICT,
 FOREIGN KEY (approved_by_user_id) REFERENCES user_accounts(user_id) ON DELETE SET NULL,
 INDEX idx_payroll_period (period_id)
) ENGINE=InnoDB;

CREATE TABLE payroll_items (
 payroll_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 payroll_run_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NOT NULL,
 basic_pay DECIMAL(15,2) NOT NULL DEFAULT 0,
 skilled_pay DECIMAL(15,2) NOT NULL DEFAULT 0,
 allowances DECIMAL(15,2) NOT NULL DEFAULT 0,
 bonus DECIMAL(15,2) NOT NULL DEFAULT 0,
 epf_applicable_base DECIMAL(15,2) NOT NULL DEFAULT 0,
 employee_epf_8pct DECIMAL(15,2) NOT NULL DEFAULT 0,
 employer_epf_12pct DECIMAL(15,2) NOT NULL DEFAULT 0,
 employer_etf_3pct DECIMAL(15,2) NOT NULL DEFAULT 0,
 other_deductions DECIMAL(15,2) NOT NULL DEFAULT 0,
 net_pay DECIMAL(15,2) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_payroll_employee (payroll_run_id,employee_id),
 FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(payroll_run_id) ON DELETE RESTRICT,
 FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE RESTRICT,
 CONSTRAINT chk_payroll_nonnegative CHECK(basic_pay>=0 AND skilled_pay>=0 AND allowances>=0 AND bonus>=0 AND epf_applicable_base>=0 AND employee_epf_8pct>=0 AND employer_epf_12pct>=0 AND employer_etf_3pct>=0 AND other_deductions>=0),
 CONSTRAINT chk_payroll_net CHECK(net_pay = basic_pay + skilled_pay + allowances + bonus - employee_epf_8pct - other_deductions),
 INDEX idx_payroll_employee (employee_id)
) ENGINE=InnoDB;

CREATE TABLE payslips (
 payslip_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 payroll_item_id BIGINT UNSIGNED NOT NULL UNIQUE,
 issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 pdf_storage_key VARCHAR(512) NULL,
 issued_by_user_id BIGINT UNSIGNED NULL,
 FOREIGN KEY (payroll_item_id) REFERENCES payroll_items(payroll_item_id) ON DELETE RESTRICT,
 FOREIGN KEY (issued_by_user_id) REFERENCES user_accounts(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE financial_documents (
 file_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 income_id BIGINT UNSIGNED NULL,
 expense_id BIGINT UNSIGNED NULL,
 payroll_item_id BIGINT UNSIGNED NULL,
 document_type ENUM('invoice','receipt','voucher','bank_slip','other') NOT NULL DEFAULT 'other',
 file_name VARCHAR(255) NOT NULL,
 content_type VARCHAR(100) NOT NULL,
 storage_key VARCHAR(512) NOT NULL UNIQUE,
 uploaded_by_user_id BIGINT UNSIGNED NULL,
 uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (income_id) REFERENCES income_records(income_id) ON DELETE RESTRICT,
 FOREIGN KEY (expense_id) REFERENCES expenses(expense_id) ON DELETE RESTRICT,
 FOREIGN KEY (payroll_item_id) REFERENCES payroll_items(payroll_item_id) ON DELETE RESTRICT,
 FOREIGN KEY (uploaded_by_user_id) REFERENCES user_accounts(user_id) ON DELETE SET NULL,
 CONSTRAINT chk_fin_document_one_parent CHECK((income_id IS NOT NULL) + (expense_id IS NOT NULL) + (payroll_item_id IS NOT NULL) = 1)
) ENGINE=InnoDB;

CREATE TABLE bank_transactions (
 transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 bank_account_id BIGINT UNSIGNED NOT NULL,
 income_id BIGINT UNSIGNED NULL,
 expense_id BIGINT UNSIGNED NULL,
 transaction_date DATE NOT NULL,
 direction ENUM('credit','debit') NOT NULL,
 amount DECIMAL(17,2) NOT NULL,
 reference VARCHAR(180),
 description VARCHAR(255),
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(bank_account_id) ON DELETE RESTRICT,
 FOREIGN KEY (income_id) REFERENCES income_records(income_id) ON DELETE RESTRICT,
 FOREIGN KEY (expense_id) REFERENCES expenses(expense_id) ON DELETE RESTRICT,
 CONSTRAINT chk_transaction_amt CHECK(amount > 0),
 CONSTRAINT chk_transaction_link CHECK(NOT(income_id IS NOT NULL AND expense_id IS NOT NULL)),
 INDEX idx_bank_txn_date (bank_account_id,transaction_date)
) ENGINE=InnoDB;

CREATE TABLE account_balance_snapshots (
 snapshot_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 bank_account_id BIGINT UNSIGNED NOT NULL,
 period_id BIGINT UNSIGNED NOT NULL,
 closing_balance DECIMAL(17,2) NOT NULL,
 recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_snapshot_period (bank_account_id,period_id),
 FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(bank_account_id) ON DELETE RESTRICT,
 FOREIGN KEY (period_id) REFERENCES financial_periods(period_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Historical balances from old Excel may represent company-level results rather than
-- account-by-account verified closing balances. Do not force them into bank snapshots.
CREATE TABLE historical_company_balances (
 historical_balance_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 period_id BIGINT UNSIGNED NOT NULL UNIQUE,
 reported_amount DECIMAL(17,2) NOT NULL,
 note VARCHAR(255),
 FOREIGN KEY (period_id) REFERENCES financial_periods(period_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE shareholders (
 shareholder_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL UNIQUE,
 display_name VARCHAR(160) NOT NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 FOREIGN KEY (user_id) REFERENCES user_accounts(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE profit_allocation_rules (
 allocation_rule_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 effective_from DATE NOT NULL UNIQUE,
 company_reserve_pct DECIMAL(5,2) NOT NULL,
 shareholder_pool_pct DECIMAL(5,2) NOT NULL,
 CONSTRAINT chk_allocation_pcts CHECK(company_reserve_pct>=0 AND company_reserve_pct<=100 AND shareholder_pool_pct>=0 AND shareholder_pool_pct<=100 AND company_reserve_pct+shareholder_pool_pct=100)
) ENGINE=InnoDB;

CREATE TABLE share_distributions (
 distribution_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 period_id BIGINT UNSIGNED NOT NULL,
 shareholder_id BIGINT UNSIGNED NOT NULL,
 allocation_rule_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(17,2) NOT NULL,
 approved_at DATETIME NULL,
 paid_at DATETIME NULL,
 status ENUM('proposed','approved','paid','cancelled') NOT NULL DEFAULT 'proposed',
 FOREIGN KEY (period_id) REFERENCES financial_periods(period_id) ON DELETE RESTRICT,
 FOREIGN KEY (shareholder_id) REFERENCES shareholders(shareholder_id) ON DELETE RESTRICT,
 FOREIGN KEY (allocation_rule_id) REFERENCES profit_allocation_rules(allocation_rule_id) ON DELETE RESTRICT,
 CONSTRAINT chk_distribution_amount CHECK(amount>=0),
 INDEX idx_distribution_period (period_id)
) ENGINE=InnoDB;

-- Safe reference data only. No default users/passwords or personal data.
INSERT IGNORE INTO roles (role_id,name,description) VALUES
 (1,'admin','Full finance and HR administration'),
 (2,'employee','Own profile, leave requests and payslips only'),
 (3,'shareholder','Read-only authorized financial summaries');
INSERT IGNORE INTO expense_categories (category_name) VALUES
 ('Software & Subscriptions'),('Marketing'),('Communication'),('Travel'),('Events'),
 ('Compliance'),('Tax'),('Payroll'),('Statutory Contributions'),('Other');
INSERT IGNORE INTO profit_allocation_rules (effective_from,company_reserve_pct,shareholder_pool_pct)
 VALUES ('2025-08-01',70.00,30.00);

-- The following optional query shows ACTUAL ledger-derived balance (when bank transactions
-- are complete), NOT the arithmetic sum of monthly closing snapshots.
CREATE OR REPLACE VIEW v_account_ledger_balances AS
 SELECT ba.bank_account_id,ba.account_name,ba.currency,
        ba.opening_balance + COALESCE(SUM(CASE WHEN bt.direction='credit' THEN bt.amount ELSE -bt.amount END),0) AS ledger_balance
 FROM bank_accounts ba LEFT JOIN bank_transactions bt ON bt.bank_account_id=ba.bank_account_id
 GROUP BY ba.bank_account_id,ba.account_name,ba.currency,ba.opening_balance;

-- Monthly dashboard totals. Ensure income/expense records are entered in LKR for these
-- totals, or add explicit LKR conversion accounting before using multi-currency values.
CREATE OR REPLACE VIEW v_monthly_financial_summary AS
 SELECT p.period_id,p.year,p.month,
    COALESCE((SELECT SUM(i.received_amount) FROM income_records i WHERE i.period_id=p.period_id AND i.currency='LKR' AND i.payment_status IN ('partial','received')),0) AS received_income_lkr,
    COALESCE((SELECT SUM(e.amount) FROM expenses e WHERE e.period_id=p.period_id AND e.currency='LKR' AND e.status IN ('approved','paid')),0) AS expenses_lkr,
    COALESCE((SELECT SUM(pi.basic_pay + pi.skilled_pay + pi.allowances + pi.bonus + pi.employer_epf_12pct + pi.employer_etf_3pct)
             FROM payroll_items pi JOIN payroll_runs pr ON pr.payroll_run_id=pi.payroll_run_id
             WHERE pr.period_id=p.period_id AND pr.status IN ('approved','paid')),0) AS payroll_employer_cost_lkr
 FROM financial_periods p;