-- Assets and Liabilities for Financial Statement / Balance Sheet
-- Run once or let the app create tables via PHP.

USE cmr_ecommerce;

CREATE TABLE IF NOT EXISTS assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100) NULL COMMENT 'e.g. Equipment, Property, Vehicle, Cash',
    value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    date_acquired DATE NULL COMMENT 'Date purchased (can be back-dated)',
    depreciation_percent DECIMAL(5,2) NULL COMMENT 'Annual straight-line % for net book value',
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT NULL
);

CREATE TABLE IF NOT EXISTS liabilities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100) NULL COMMENT 'e.g. Tax payable, Accruals, Other payables',
    amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    due_date DATE NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT NULL
);

-- Optional: VAT/tax rate on ingredients (for input VAT when purchasing). Run only if column does not exist.
-- ALTER TABLE ingredients ADD COLUMN tax_rate_id INT NULL COMMENT 'VAT/tax rate for this ingredient (e.g. 16% VAT)';