-- ============================================================
-- MVP SCHEMA + FIFO — 19 tables
-- Changes from MVP base:
--   items        : removed purchase_cost (replaced by layers)
--   inventory_cost_layers : NEW table
--   inventory_adjustment_lines : NEW table (needed to track layer consumption on adjustments)
-- ============================================================


-- ------------------------------------------------------------
-- 1. companies
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `companies` (
  `company_id`   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(200)    NOT NULL,
  `email`        VARCHAR(150)    NULL,
  `phone`        VARCHAR(30)     NULL,
  `address`      TEXT            NULL,
  `tax_number`   VARCHAR(50)     NULL COMMENT 'TIN',
  `currency`     CHAR(3)         NOT NULL DEFAULT 'LKR',
  `active`       TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. users
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `user_id`       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id`    BIGINT UNSIGNED NOT NULL,
  `name`          VARCHAR(150)    NOT NULL,
  `email`         VARCHAR(150)    NOT NULL,
  `password_hash` VARCHAR(255)    NOT NULL,
  `role`          ENUM('ADMIN','MANAGER','CASHIER','ACCOUNTANT') NOT NULL DEFAULT 'CASHIER',
  `active`        TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uq_users_company_email` (`company_id`, `email`),
  KEY `idx_users_company` (`company_id`),
  CONSTRAINT `fk_users_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. accounts  (Chart of Accounts)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts` (
  `account_id`        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id`        BIGINT UNSIGNED NOT NULL,
  `account_number`    VARCHAR(20)     NOT NULL,
  `name`              VARCHAR(200)    NOT NULL,
  `account_type`      ENUM('ASSET','LIABILITY','EQUITY','INCOME','EXPENSE') NOT NULL,
  `account_sub_type`  ENUM(
                        'CASH','BANK','ACCOUNTS_RECEIVABLE',
                        'INVENTORY_ASSET','FIXED_ASSET',
                        'ACCOUNTS_PAYABLE','VAT_PAYABLE',
                        'INPUT_VAT','SALES_REVENUE','COGS',
                        'OPERATING_EXPENSE','WRITE_OFF','OTHER'
                      ) NOT NULL DEFAULT 'OTHER',
  `parent_account_id` BIGINT UNSIGNED NULL,
  `active`            TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `uq_account_number_company` (`company_id`, `account_number`),
  KEY `idx_accounts_company` (`company_id`),
  KEY `idx_accounts_parent`  (`parent_account_id`),
  CONSTRAINT `fk_accounts_company` FOREIGN KEY (`company_id`)        REFERENCES `companies` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_accounts_parent`  FOREIGN KEY (`parent_account_id`) REFERENCES `accounts`  (`account_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. customers
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `customers` (
  `customer_id`   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id`    BIGINT UNSIGNED NOT NULL,
  `display_name`  VARCHAR(200)    NOT NULL,
  `email`         VARCHAR(150)    NULL,
  `phone`         VARCHAR(30)     NULL,
  `address`       TEXT            NULL,
  `customer_type` ENUM('RETAIL','WHOLESALE','CORPORATE','GOVERNMENT') NOT NULL DEFAULT 'RETAIL',
  `payment_terms` ENUM('NET_30','NET_60','NET_90','IMMEDIATE') NOT NULL DEFAULT 'IMMEDIATE',
  `balance`       DECIMAL(20,4)   NOT NULL DEFAULT 0.0000 COMMENT 'Running AR balance',
  `active`        TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  KEY `idx_customers_company` (`company_id`),
  CONSTRAINT `fk_customers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. vendors
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vendors` (
  `vendor_id`     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id`    BIGINT UNSIGNED NOT NULL,
  `display_name`  VARCHAR(200)    NOT NULL,
  `email`         VARCHAR(150)    NULL,
  `phone`         VARCHAR(30)     NULL,
  `address`       TEXT            NULL,
  `payment_terms` ENUM('NET_30','NET_60','NET_90','IMMEDIATE') NOT NULL DEFAULT 'NET_30',
  `balance`       DECIMAL(20,4)   NOT NULL DEFAULT 0.0000 COMMENT 'Running AP balance',
  `active`        TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`vendor_id`),
  KEY `idx_vendors_company` (`company_id`),
  CONSTRAINT `fk_vendors_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. tax_codes
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tax_codes` (
  `tax_code_id`  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id`   BIGINT UNSIGNED NOT NULL,
  `name`         VARCHAR(50)     NOT NULL COMMENT 'e.g. VAT18, EXEMPT, ZERO_RATED',
  `rate_percent` DECIMAL(6,2)    NOT NULL DEFAULT 0.00,
  `is_taxable`   TINYINT(1)      NOT NULL DEFAULT 1,
  `active`       TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`tax_code_id`),
  UNIQUE KEY `uq_tax_code_company` (`company_id`, `name`),
  KEY `idx_tax_codes_company` (`company_id`),
  CONSTRAINT `fk_tax_codes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. items
-- FIFO change: removed purchase_cost
--   Cost is now derived from inventory_cost_layers, not a
--   single average field. Use SUM(qty_remaining * unit_cost)
--   / SUM(qty_remaining) for a display cost if needed.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `items` (
  `item_id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id`         BIGINT UNSIGNED NOT NULL,
  `name`               VARCHAR(250)    NOT NULL,
  `sku`                VARCHAR(80)     NOT NULL,
  `item_type`          ENUM('INVENTORY','SERVICE','NON_INVENTORY') NOT NULL DEFAULT 'INVENTORY',
  `unit_of_measure`    ENUM('UNIT','BOX','SET','PAIR','KG','LITRE','METRE') NOT NULL DEFAULT 'UNIT',
  `unit_price`         DECIMAL(20,4)   NOT NULL DEFAULT 0.0000 COMMENT 'Selling price',
  `qty_on_hand`        DECIMAL(14,4)   NOT NULL DEFAULT 0.0000 COMMENT 'Updated on every bill receive and invoice post',
  `reorder_point`      DECIMAL(14,4)   NOT NULL DEFAULT 0.0000,
  `taxable`            TINYINT(1)      NOT NULL DEFAULT 1,
  `active`             TINYINT(1)      NOT NULL DEFAULT 1,
  `income_account_id`  BIGINT UNSIGNED NULL COMMENT 'Sales Revenue account',
  `expense_account_id` BIGINT UNSIGNED NULL COMMENT 'COGS account',
  `asset_account_id`   BIGINT UNSIGNED NULL COMMENT 'Inventory Asset account',
  `created_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `uq_item_sku_company` (`company_id`, `sku`),
  KEY `idx_items_company`          (`company_id`),
  KEY `idx_items_income_account`   (`income_account_id`),
  KEY `idx_items_expense_account`  (`expense_account_id`),
  KEY `idx_items_asset_account`    (`asset_account_id`),
  CONSTRAINT `fk_items_company`         FOREIGN KEY (`company_id`)         REFERENCES `companies` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_items_income_account`  FOREIGN KEY (`income_account_id`)  REFERENCES `accounts`  (`account_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_items_expense_account` FOREIGN KEY (`expense_account_id`) REFERENCES `accounts`  (`account_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_items_asset_account`   FOREIGN KEY (`asset_account_id`)   REFERENCES `accounts`  (`account_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. transactions  (master registry spine)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `txn_id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id`         BIGINT UNSIGNED NOT NULL,
  `txn_type`           ENUM('BILL','INVOICE','PAYMENT','BILL_PAYMENT','JOURNAL_ENTRY','EXPENSE') NOT NULL,
  `status`             ENUM('DRAFT','OPEN','PARTIAL','PAID','VOIDED') NOT NULL DEFAULT 'DRAFT',
  `created_by_user_id` BIGINT UNSIGNED NULL,
  `created_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`txn_id`),
  KEY `idx_txn_company`    (`company_id`),
  KEY `idx_txn_type`       (`txn_type`),
  KEY `idx_txn_status`     (`status`),
  KEY `idx_txn_created_by` (`created_by_user_id`),
  CONSTRAINT `fk_txn_company`    FOREIGN KEY (`company_id`)         REFERENCES `companies` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_txn_created_by` FOREIGN KEY (`created_by_user_id`) REFERENCES `users`     (`user_id`)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. inventory_cost_layers  *** FIFO TABLE ***
--
-- Created when: bill is marked received_into_inventory = 1
-- Consumed when: invoice is posted (oldest date_acquired first)
-- One row per purchase batch per item.
-- qty_remaining counts down as stock is sold.
-- When qty_remaining = 0 the layer is fully consumed.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_cost_layers` (
  `layer_id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id`           BIGINT UNSIGNED NOT NULL,
  `company_id`        BIGINT UNSIGNED NOT NULL,
  `reference_txn_id`  BIGINT UNSIGNED NOT NULL COMMENT 'The bill txn_id that created this layer',
  `bill_line_id`      BIGINT UNSIGNED NOT NULL COMMENT 'Exact bill line — for void/reversal tracing',
  `qty_purchased`     DECIMAL(14,4)   NOT NULL COMMENT 'Original qty received in this batch',
  `qty_remaining`     DECIMAL(14,4)   NOT NULL COMMENT 'Decremented on each sale — 0 = fully consumed',
  `unit_cost`         DECIMAL(20,4)   NOT NULL COMMENT 'Cost per unit for this batch',
  `total_cost`        DECIMAL(20,4)   NOT NULL COMMENT 'qty_purchased × unit_cost — for fast reporting',
  `date_acquired`     DATE            NOT NULL COMMENT 'Bill txn_date — determines FIFO order',
  `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`layer_id`),
  KEY `idx_layers_item_date`  (`item_id`, `date_acquired`),  -- FIFO consumption query
  KEY `idx_layers_company`    (`company_id`),
  KEY `idx_layers_ref_txn`    (`reference_txn_id`),
  KEY `idx_layers_bill_line`  (`bill_line_id`),
  CONSTRAINT `fk_layers_item`      FOREIGN KEY (`item_id`)         REFERENCES `items`        (`item_id`)  ON DELETE CASCADE,
  CONSTRAINT `fk_layers_company`   FOREIGN KEY (`company_id`)      REFERENCES `companies`    (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_layers_ref_txn`   FOREIGN KEY (`reference_txn_id`) REFERENCES `transactions` (`txn_id`)  ON DELETE CASCADE,
  CONSTRAINT `fk_layers_bill_line` FOREIGN KEY (`bill_line_id`)    REFERENCES `bill_lines`   (`line_id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. invoices
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoices` (
  `invoice_id`        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `txn_id`            BIGINT UNSIGNED NOT NULL,
  `company_id`        BIGINT UNSIGNED NOT NULL,
  `invoice_number`    VARCHAR(50)     NOT NULL,
  `customer_id`       BIGINT UNSIGNED NOT NULL,
  `customer_snapshot` JSON            NOT NULL COMMENT 'Frozen customer name/address at invoice time',
  `txn_date`          DATE            NOT NULL,
  `due_date`          DATE            NULL,
  `payment_terms`     ENUM('NET_30','NET_60','NET_90','IMMEDIATE') NOT NULL DEFAULT 'IMMEDIATE',
  `sub_total`         DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `discount_total`    DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `tax_total`         DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `total_amount`      DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `balance`           DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `status`            ENUM('DRAFT','OPEN','PARTIAL','PAID','VOIDED') NOT NULL DEFAULT 'DRAFT',
  `memo`              TEXT            NULL COMMENT 'Printed on invoice',
  `notes`             TEXT            NULL COMMENT 'Internal notes only',
  `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`invoice_id`),
  UNIQUE KEY `uq_invoice_number_company` (`company_id`, `invoice_number`),
  KEY `idx_invoice_txn`      (`txn_id`),
  KEY `idx_invoice_customer` (`customer_id`),
  KEY `idx_invoice_company`  (`company_id`),
  KEY `idx_invoice_status`   (`status`),
  KEY `idx_invoice_due_date` (`due_date`),
  CONSTRAINT `fk_invoice_txn`      FOREIGN KEY (`txn_id`)      REFERENCES `transactions` (`txn_id`)     ON DELETE CASCADE,
  CONSTRAINT `fk_invoice_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`    (`customer_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_invoice_company`  FOREIGN KEY (`company_id`)  REFERENCES `companies`    (`company_id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. invoice_lines
-- FIFO change: added cogs_amount (computed during FIFO
--   consumption, stored here so voiding can reverse exactly)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice_lines` (
  `line_id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id`       BIGINT UNSIGNED NOT NULL,
  `txn_id`           BIGINT UNSIGNED NOT NULL,
  `item_id`          BIGINT UNSIGNED NULL,
  `item_snapshot`    JSON            NOT NULL COMMENT 'Frozen item name/price at invoice time',
  `line_number`      SMALLINT UNSIGNED NOT NULL,
  `qty`              DECIMAL(14,4)   NOT NULL,
  `unit_price`       DECIMAL(20,4)   NOT NULL,
  `discount_percent` DECIMAL(6,2)    NOT NULL DEFAULT 0.00,
  `discount_amount`  DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `tax_code_id`      BIGINT UNSIGNED NULL,
  `tax_amount`       DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `line_total`       DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `cogs_amount`      DECIMAL(20,4)   NOT NULL DEFAULT 0.0000 COMMENT 'FIFO cost consumed for this line — used for COGS journal entry and void reversal',
  PRIMARY KEY (`line_id`),
  KEY `idx_inv_lines_invoice`  (`invoice_id`),
  KEY `idx_inv_lines_item`     (`item_id`),
  KEY `idx_inv_lines_tax_code` (`tax_code_id`),
  CONSTRAINT `fk_inv_lines_invoice`  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`   (`invoice_id`)  ON DELETE CASCADE,
  CONSTRAINT `fk_inv_lines_txn`      FOREIGN KEY (`txn_id`)     REFERENCES `transactions` (`txn_id`)    ON DELETE CASCADE,
  CONSTRAINT `fk_inv_lines_item`     FOREIGN KEY (`item_id`)    REFERENCES `items`       (`item_id`)    ON DELETE RESTRICT,
  CONSTRAINT `fk_inv_lines_tax_code` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`tax_code_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. invoice_line_layers  *** FIFO TABLE ***
--
-- Records exactly which cost layers were consumed per
-- invoice line and how many units came from each layer.
-- Essential for: COGS accuracy, void reversal, audit trail.
-- One invoice line may span multiple layers.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice_line_layers` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_line_id` BIGINT UNSIGNED NOT NULL,
  `layer_id`        BIGINT UNSIGNED NOT NULL,
  `qty_consumed`    DECIMAL(14,4)   NOT NULL COMMENT 'Units taken from this layer for this line',
  `unit_cost`       DECIMAL(20,4)   NOT NULL COMMENT 'Snapshot of layer unit_cost at time of sale',
  `total_cost`      DECIMAL(20,4)   NOT NULL COMMENT 'qty_consumed × unit_cost',
  `created_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ill_invoice_line` (`invoice_line_id`),
  KEY `idx_ill_layer`        (`layer_id`),
  CONSTRAINT `fk_ill_invoice_line` FOREIGN KEY (`invoice_line_id`) REFERENCES `invoice_lines`        (`line_id`)  ON DELETE CASCADE,
  CONSTRAINT `fk_ill_layer`        FOREIGN KEY (`layer_id`)        REFERENCES `inventory_cost_layers` (`layer_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13. bills
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bills` (
  `bill_id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `txn_id`                  BIGINT UNSIGNED NOT NULL,
  `company_id`              BIGINT UNSIGNED NOT NULL,
  `vendor_id`               BIGINT UNSIGNED NOT NULL,
  `vendor_snapshot`         JSON            NOT NULL COMMENT 'Frozen vendor name/address at bill time',
  `bill_number`             VARCHAR(50)     NOT NULL,
  `txn_date`                DATE            NOT NULL,
  `due_date`                DATE            NULL,
  `sub_total`               DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `discount_total`          DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `tax_total`               DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `total_amount`            DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `balance`                 DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `status`                  ENUM('DRAFT','OPEN','PARTIAL','PAID','VOIDED') NOT NULL DEFAULT 'DRAFT',
  `received_into_inventory` TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Flip to 1 when stock received — creates cost layers and locks qty edits',
  `memo`                    TEXT            NULL,
  `created_at`              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bill_id`),
  UNIQUE KEY `uq_bill_number_company` (`company_id`, `bill_number`),
  KEY `idx_bill_txn`     (`txn_id`),
  KEY `idx_bill_vendor`  (`vendor_id`),
  KEY `idx_bill_company` (`company_id`),
  KEY `idx_bill_status`  (`status`),
  CONSTRAINT `fk_bill_txn`     FOREIGN KEY (`txn_id`)     REFERENCES `transactions` (`txn_id`)    ON DELETE CASCADE,
  CONSTRAINT `fk_bill_vendor`  FOREIGN KEY (`vendor_id`)  REFERENCES `vendors`      (`vendor_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_bill_company` FOREIGN KEY (`company_id`) REFERENCES `companies`    (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 14. bill_lines
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bill_lines` (
  `line_id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `bill_id`          BIGINT UNSIGNED NOT NULL,
  `txn_id`           BIGINT UNSIGNED NOT NULL,
  `item_id`          BIGINT UNSIGNED NULL,
  `item_snapshot`    JSON            NOT NULL COMMENT 'Frozen item data at bill time',
  `qty`              DECIMAL(14,4)   NOT NULL,
  `unit_price`       DECIMAL(20,4)   NOT NULL,
  `discount_percent` DECIMAL(6,2)    NOT NULL DEFAULT 0.00,
  `tax_code_id`      BIGINT UNSIGNED NULL,
  `tax_amount`       DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `line_total`       DECIMAL(20,4)   NOT NULL DEFAULT 0.0000,
  `notes`            VARCHAR(500)    NULL,
  PRIMARY KEY (`line_id`),
  KEY `idx_bill_lines_bill`     (`bill_id`),
  KEY `idx_bill_lines_item`     (`item_id`),
  KEY `idx_bill_lines_tax_code` (`tax_code_id`),
  CONSTRAINT `fk_bill_lines_bill`     FOREIGN KEY (`bill_id`)    REFERENCES `bills`     (`bill_id`)     ON DELETE CASCADE,
  CONSTRAINT `fk_bill_lines_txn`      FOREIGN KEY (`txn_id`)     REFERENCES `transactions` (`txn_id`)   ON DELETE CASCADE,
  CONSTRAINT `fk_bill_lines_item`     FOREIGN KEY (`item_id`)    REFERENCES `items`     (`item_id`)     ON DELETE RESTRICT,
  CONSTRAINT `fk_bill_lines_tax_code` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`tax_code_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 15. payments
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
  `payment_id`     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `txn_id`         BIGINT UNSIGNED NOT NULL,
  `company_id`     BIGINT UNSIGNED NOT NULL,
  `payment_number` VARCHAR(50)     NOT NULL,
  `customer_id`    BIGINT UNSIGNED NOT NULL,
  `txn_date`       DATE            NOT NULL,
  `total_amount`   DECIMAL(20,4)   NOT NULL,
  `payment_method` ENUM('CASH','CARD','BANK_TRANSFER','CHEQUE','ONLINE') NOT NULL DEFAULT 'CASH',
  `ref_number`     VARCHAR(100)    NULL COMMENT 'Cheque no / transfer ref',
  `memo`           TEXT            NULL,
  `created_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  UNIQUE KEY `uq_payment_number_company` (`company_id`, `payment_number`),
  KEY `idx_payment_txn`      (`txn_id`),
  KEY `idx_payment_customer` (`customer_id`),
  KEY `idx_payment_company`  (`company_id`),
  CONSTRAINT `fk_payment_txn`      FOREIGN KEY (`txn_id`)      REFERENCES `transactions` (`txn_id`)     ON DELETE CASCADE,
  CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`    (`customer_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_payment_company`  FOREIGN KEY (`company_id`)  REFERENCES `companies`    (`company_id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 16. payment_allocations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_allocations` (
  `allocation_id`  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_txn_id` BIGINT UNSIGNED NOT NULL,
  `invoice_txn_id` BIGINT UNSIGNED NOT NULL,
  `amount_applied` DECIMAL(20,4)   NOT NULL,
  `created_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`allocation_id`),
  KEY `idx_pa_payment` (`payment_txn_id`),
  KEY `idx_pa_invoice` (`invoice_txn_id`),
  CONSTRAINT `fk_pa_payment` FOREIGN KEY (`payment_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pa_invoice` FOREIGN KEY (`invoice_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 17. bill_payments
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bill_payments` (
  `bp_id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `txn_id`         BIGINT UNSIGNED NOT NULL,
  `company_id`     BIGINT UNSIGNED NOT NULL,
  `payment_number` VARCHAR(50)     NOT NULL,
  `vendor_id`      BIGINT UNSIGNED NOT NULL,
  `txn_date`       DATE            NOT NULL,
  `total_amount`   DECIMAL(20,4)   NOT NULL,
  `payment_method` ENUM('CASH','CARD','BANK_TRANSFER','CHEQUE','ONLINE') NOT NULL DEFAULT 'CASH',
  `ref_number`     VARCHAR(100)    NULL,
  `memo`           TEXT            NULL,
  `created_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bp_id`),
  UNIQUE KEY `uq_bp_number_company` (`company_id`, `payment_number`),
  KEY `idx_bp_txn`     (`txn_id`),
  KEY `idx_bp_vendor`  (`vendor_id`),
  KEY `idx_bp_company` (`company_id`),
  CONSTRAINT `fk_bp_txn`     FOREIGN KEY (`txn_id`)     REFERENCES `transactions` (`txn_id`)    ON DELETE CASCADE,
  CONSTRAINT `fk_bp_vendor`  FOREIGN KEY (`vendor_id`)  REFERENCES `vendors`      (`vendor_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_bp_company` FOREIGN KEY (`company_id`) REFERENCES `companies`    (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 18. bill_payment_allocations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bill_payment_allocations` (
  `allocation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `bp_txn_id`     BIGINT UNSIGNED NOT NULL,
  `bill_txn_id`   BIGINT UNSIGNED NOT NULL,
  `amount_applied` DECIMAL(20,4)  NOT NULL,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`allocation_id`),
  KEY `idx_bpa_bp`   (`bp_txn_id`),
  KEY `idx_bpa_bill` (`bill_txn_id`),
  CONSTRAINT `fk_bpa_bp`   FOREIGN KEY (`bp_txn_id`)   REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bpa_bill` FOREIGN KEY (`bill_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 19. journal_entries
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `journal_entries` (
  `je_id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `txn_id`             BIGINT UNSIGNED NOT NULL,
  `company_id`         BIGINT UNSIGNED NOT NULL,
  `je_number`          VARCHAR(50)     NOT NULL,
  `txn_date`           DATE            NOT NULL,
  `reference_txn_id`   BIGINT UNSIGNED NULL COMMENT 'Which invoice/bill triggered this JE',
  `memo`               TEXT            NULL,
  `is_auto_generated`  TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '1 = system, 0 = manual',
  `created_by_user_id` BIGINT UNSIGNED NULL,
  `created_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`je_id`),
  UNIQUE KEY `uq_je_number_company` (`company_id`, `je_number`),
  KEY `idx_je_txn`       (`txn_id`),
  KEY `idx_je_company`   (`company_id`),
  KEY `idx_je_ref_txn`   (`reference_txn_id`),
  CONSTRAINT `fk_je_txn`        FOREIGN KEY (`txn_id`)             REFERENCES `transactions` (`txn_id`)  ON DELETE CASCADE,
  CONSTRAINT `fk_je_company`    FOREIGN KEY (`company_id`)         REFERENCES `companies`    (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_je_ref_txn`    FOREIGN KEY (`reference_txn_id`)   REFERENCES `transactions` (`txn_id`)  ON DELETE SET NULL,
  CONSTRAINT `fk_je_created_by` FOREIGN KEY (`created_by_user_id`) REFERENCES `users`        (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 20. journal_entry_lines
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `journal_entry_lines` (
  `jel_id`       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `je_id`        BIGINT UNSIGNED NOT NULL,
  `txn_id`       BIGINT UNSIGNED NOT NULL,
  `line_number`  SMALLINT UNSIGNED NOT NULL,
  `account_id`   BIGINT UNSIGNED NOT NULL,
  `posting_type` ENUM('DEBIT','CREDIT') NOT NULL,
  `amount`       DECIMAL(20,4)   NOT NULL,
  `entity_type`  ENUM('CUSTOMER','VENDOR') NULL COMMENT 'Sub-ledger drill-down',
  `entity_id`    BIGINT UNSIGNED NULL COMMENT 'customer_id or vendor_id',
  `description`  VARCHAR(500)    NULL,
  PRIMARY KEY (`jel_id`),
  KEY `idx_jel_je`      (`je_id`),
  KEY `idx_jel_account` (`account_id`),
  CONSTRAINT `fk_jel_je`      FOREIGN KEY (`je_id`)      REFERENCES `journal_entries` (`je_id`)      ON DELETE CASCADE,
  CONSTRAINT `fk_jel_txn`     FOREIGN KEY (`txn_id`)     REFERENCES `transactions`    (`txn_id`)     ON DELETE CASCADE,
  CONSTRAINT `fk_jel_account` FOREIGN KEY (`account_id`) REFERENCES `accounts`        (`account_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- CHANGELOG vs MVP base schema
-- ============================================================
-- items                  : REMOVED purchase_cost
--                          COGS now comes from inventory_cost_layers
-- invoice_lines          : ADDED cogs_amount
--                          Stores FIFO cost consumed — needed for
--                          COGS journal entry and void reversal
-- inventory_cost_layers  : NEW — one row per purchase batch
--                          qty_remaining counts down as stock sells
-- invoice_line_layers    : NEW — maps each invoice line to the
--                          exact layers it consumed (may be multiple)
-- All other tables       : UNCHANGED
-- ============================================================
-- FIFO flow summary
-- ============================================================
-- ON BILL RECEIVE (received_into_inventory flipped to 1):
--   INSERT inventory_cost_layers (one row per bill_line)
--   UPDATE items.qty_on_hand += qty
--
-- ON INVOICE POST:
--   For each invoice_line with item_type = INVENTORY:
--     SELECT layers WHERE item_id = ? AND qty_remaining > 0
--     ORDER BY date_acquired ASC  -- oldest first
--     LOCK FOR UPDATE             -- prevent race conditions
--     Consume layers oldest-first until qty satisfied
--     INSERT invoice_line_layers  (one row per layer touched)
--     UPDATE inventory_cost_layers.qty_remaining
--     UPDATE invoice_lines.cogs_amount = sum of layer costs
--     UPDATE items.qty_on_hand -= qty
--   POST journal entries:
--     DR Accounts Receivable   invoice total
--     CR Sales Revenue         invoice total
--     DR Cost of Goods Sold    cogs_amount
--     CR Inventory Asset       cogs_amount
--
-- ON INVOICE VOID:
--   SELECT invoice_line_layers WHERE invoice_line_id IN (...)
--   RESTORE inventory_cost_layers.qty_remaining += qty_consumed
--   UPDATE items.qty_on_hand += qty
--   POST reversing journal entries (swap DR/CR)
-- ============================================================
-- Table count : 20 (18 MVP + 2 FIFO)
-- ============================================================