-- ============================================================
-- CashBook ERP - Complete Database Schema
-- PostgreSQL Database for Ghanaian SME Bookkeeping
-- ============================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================
-- 1. CORE TABLES - Tenants, Companies, Users, Settings
-- ============================================================

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    domain VARCHAR(255),
    plan VARCHAR(50) DEFAULT 'free',           -- free, starter, professional, enterprise
    max_businesses INT DEFAULT 1,
    max_users INT DEFAULT 5,
    is_active BOOLEAN DEFAULT TRUE,
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE companies (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    trading_name VARCHAR(255),
    tin_number VARCHAR(20),          -- Ghana Revenue Authority TIN
    business_registration VARCHAR(50), -- Registrar General's Dept
    vat_registered BOOLEAN DEFAULT FALSE,
    vat_number VARCHAR(20),
    nhil_registered BOOLEAN DEFAULT FALSE,
    getfund_registered BOOLEAN DEFAULT FALSE,
    covid_levy_registered BOOLEAN DEFAULT FALSE,
    address TEXT,
    city VARCHAR(100) DEFAULT 'Accra',
    region VARCHAR(100),             -- Ghana regions
    digital_address VARCHAR(20),     -- Ghana Post GPS
    phone VARCHAR(20),
    email VARCHAR(255),
    website VARCHAR(255),
    logo_url TEXT,
    currency VARCHAR(3) DEFAULT 'GHS',
    supported_currencies VARCHAR(100) DEFAULT '',  -- comma-separated additional currencies
    fiscal_year_start INT DEFAULT 1, -- Month (1=January)
    default_profit_margin DECIMAL(5,2) DEFAULT 0,
    industry VARCHAR(100),
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    role VARCHAR(50) NOT NULL DEFAULT 'staff',  -- super_admin, admin, accountant, cashier, manager, staff
    permissions JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP WITH TIME ZONE,
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE user_businesses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL DEFAULT 'staff',
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, company_id)
);

CREATE TABLE company_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(20) DEFAULT 'string', -- string, number, boolean, json
    UNIQUE(company_id, setting_key)
);

-- ============================================================
-- 2. CHART OF ACCOUNTS
-- ============================================================

CREATE TABLE account_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL, -- asset, liability, equity, revenue, expense
    parent_id UUID REFERENCES account_categories(id),
    sort_order INT DEFAULT 0
);

CREATE TABLE chart_of_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    account_code VARCHAR(20) NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    account_type VARCHAR(50) NOT NULL, -- asset, liability, equity, revenue, expense
    sub_type VARCHAR(50),              -- current_asset, fixed_asset, current_liability, etc.
    category_id UUID REFERENCES account_categories(id),
    parent_account_id UUID REFERENCES chart_of_accounts(id),
    description TEXT,
    is_system_account BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    normal_balance VARCHAR(10) DEFAULT 'debit', -- debit or credit
    opening_balance DECIMAL(15,2) DEFAULT 0.00,
    current_balance DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, account_code)
);

-- ============================================================
-- 3. GENERAL LEDGER / JOURNAL ENTRIES
-- ============================================================

CREATE TABLE journal_entries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    entry_number VARCHAR(50) NOT NULL,
    entry_date DATE NOT NULL,
    description TEXT,
    reference VARCHAR(100),
    source VARCHAR(50),          -- manual, invoice, payment, payroll, pos, adjustment
    source_id UUID,              -- Reference to source document
    status VARCHAR(20) DEFAULT 'draft', -- draft, posted, voided
    total_debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_by UUID REFERENCES users(id),
    approved_by UUID REFERENCES users(id),
    posted_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, entry_number)
);

CREATE TABLE journal_entry_lines (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    journal_entry_id UUID NOT NULL REFERENCES journal_entries(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES chart_of_accounts(id),
    debit_amount DECIMAL(15,2) DEFAULT 0.00,
    credit_amount DECIMAL(15,2) DEFAULT 0.00,
    description TEXT,
    tax_code VARCHAR(20),
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 4. CONTACTS (Customers & Suppliers)
-- ============================================================

CREATE TABLE contacts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    contact_type VARCHAR(20) NOT NULL, -- customer, supplier, both
    name VARCHAR(255) NOT NULL,
    trading_name VARCHAR(255),
    tin_number VARCHAR(20),
    vat_number VARCHAR(20),
    email VARCHAR(255),
    phone VARCHAR(20),
    mobile VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    region VARCHAR(100),
    digital_address VARCHAR(20),
    payment_terms INT DEFAULT 30,      -- days
    credit_limit DECIMAL(15,2) DEFAULT 0.00,
    outstanding_balance DECIMAL(15,2) DEFAULT 0.00,
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 5. INVOICING & BILLING
-- ============================================================

CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    contact_id UUID NOT NULL REFERENCES contacts(id),
    invoice_number VARCHAR(50) NOT NULL,
    invoice_type VARCHAR(20) NOT NULL, -- sales, purchase
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    reference VARCHAR(100),
    status VARCHAR(20) DEFAULT 'draft', -- draft, sent, partially_paid, paid, overdue, voided
    subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    discount_type VARCHAR(10),          -- percent, fixed
    discount_value DECIMAL(15,2) DEFAULT 0.00,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    nhil_amount DECIMAL(15,2) DEFAULT 0.00,     -- National Health Insurance Levy
    getfund_amount DECIMAL(15,2) DEFAULT 0.00,  -- GETFund Levy
    covid_levy_amount DECIMAL(15,2) DEFAULT 0.00, -- COVID-19 Levy
    vat_amount DECIMAL(15,2) DEFAULT 0.00,       -- VAT (flat rate or standard)
    total_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    amount_paid DECIMAL(15,2) DEFAULT 0.00,
    balance_due DECIMAL(15,2) DEFAULT 0.00,
    currency VARCHAR(3) DEFAULT 'GHS',
    exchange_rate DECIMAL(10,6) DEFAULT 1.000000,
    notes TEXT,
    terms TEXT,
    journal_entry_id UUID REFERENCES journal_entries(id),
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, invoice_number)
);

CREATE TABLE invoice_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    product_id UUID,                    -- REFERENCES products(id)
    description TEXT NOT NULL,
    quantity DECIMAL(10,3) NOT NULL DEFAULT 1,
    unit_price DECIMAL(15,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0.00,
    tax_rate DECIMAL(5,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    line_total DECIMAL(15,2) NOT NULL,
    account_id UUID REFERENCES chart_of_accounts(id),
    sort_order INT DEFAULT 0
);

CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    contact_id UUID REFERENCES contacts(id),
    payment_number VARCHAR(50) NOT NULL,
    payment_type VARCHAR(20) NOT NULL,  -- received, made
    payment_date DATE NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(50),         -- cash, bank_transfer, mobile_money, cheque, card
    mobile_money_provider VARCHAR(50),  -- mtn, vodafone, airteltigo
    mobile_money_number VARCHAR(20),
    reference VARCHAR(100),
    bank_account_id UUID REFERENCES chart_of_accounts(id),
    status VARCHAR(20) DEFAULT 'completed', -- pending, completed, voided
    notes TEXT,
    journal_entry_id UUID REFERENCES journal_entries(id),
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, payment_number)
);

CREATE TABLE payment_allocations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    payment_id UUID NOT NULL REFERENCES payments(id) ON DELETE CASCADE,
    invoice_id UUID NOT NULL REFERENCES invoices(id),
    amount DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 6. PRODUCTS & INVENTORY
-- ============================================================

CREATE TABLE product_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    parent_id UUID REFERENCES product_categories(id),
    description TEXT,
    sort_order INT DEFAULT 0
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    category_id UUID REFERENCES product_categories(id),
    sku VARCHAR(50),
    barcode VARCHAR(50),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    unit_of_measure VARCHAR(20) DEFAULT 'unit', -- unit, kg, litre, pack, box, etc.
    cost_price DECIMAL(15,2) DEFAULT 0.00,
    selling_price DECIMAL(15,2) DEFAULT 0.00,
    wholesale_price DECIMAL(15,2),
    tax_inclusive BOOLEAN DEFAULT FALSE,
    tax_rate DECIMAL(5,2) DEFAULT 0.00,
    track_inventory BOOLEAN DEFAULT TRUE,
    quantity_on_hand DECIMAL(10,3) DEFAULT 0,
    reorder_level DECIMAL(10,3) DEFAULT 0,
    reorder_quantity DECIMAL(10,3) DEFAULT 0,
    income_account_id UUID REFERENCES chart_of_accounts(id),
    expense_account_id UUID REFERENCES chart_of_accounts(id),
    inventory_account_id UUID REFERENCES chart_of_accounts(id),
    image_url TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    is_service BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, sku)
);

CREATE TABLE inventory_movements (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id),
    movement_type VARCHAR(30) NOT NULL, -- purchase, sale, adjustment, transfer, return
    quantity DECIMAL(10,3) NOT NULL,
    unit_cost DECIMAL(15,2),
    total_cost DECIMAL(15,2),
    reference_type VARCHAR(30),         -- invoice, pos_sale, adjustment, purchase_order
    reference_id UUID,
    notes TEXT,
    quantity_before DECIMAL(10,3),
    quantity_after DECIMAL(10,3),
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 7. FIXED ASSETS
-- ============================================================

CREATE TABLE fixed_assets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    asset_code VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    purchase_date DATE NOT NULL,
    purchase_price DECIMAL(15,2) NOT NULL,
    salvage_value DECIMAL(15,2) DEFAULT 0.00,
    useful_life_months INT NOT NULL,
    depreciation_method VARCHAR(30) DEFAULT 'straight_line', -- straight_line, declining_balance
    depreciation_rate DECIMAL(5,2),
    accumulated_depreciation DECIMAL(15,2) DEFAULT 0.00,
    net_book_value DECIMAL(15,2),
    asset_account_id UUID REFERENCES chart_of_accounts(id),
    depreciation_account_id UUID REFERENCES chart_of_accounts(id),
    accumulated_dep_account_id UUID REFERENCES chart_of_accounts(id),
    status VARCHAR(20) DEFAULT 'active', -- active, disposed, fully_depreciated
    disposal_date DATE,
    disposal_amount DECIMAL(15,2),
    serial_number VARCHAR(100),
    location VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, asset_code)
);

CREATE TABLE asset_depreciation_schedule (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    asset_id UUID NOT NULL REFERENCES fixed_assets(id) ON DELETE CASCADE,
    period_date DATE NOT NULL,
    depreciation_amount DECIMAL(15,2) NOT NULL,
    accumulated_depreciation DECIMAL(15,2) NOT NULL,
    net_book_value DECIMAL(15,2) NOT NULL,
    journal_entry_id UUID REFERENCES journal_entries(id),
    is_posted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 8. POINT OF SALE (POS)
-- ============================================================

CREATE TABLE pos_terminals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    terminal_name VARCHAR(100) NOT NULL,
    location VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE pos_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    terminal_id UUID NOT NULL REFERENCES pos_terminals(id),
    cashier_id UUID NOT NULL REFERENCES users(id),
    opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    closing_balance DECIMAL(15,2),
    total_sales DECIMAL(15,2) DEFAULT 0.00,
    total_cash DECIMAL(15,2) DEFAULT 0.00,
    total_mobile_money DECIMAL(15,2) DEFAULT 0.00,
    total_card DECIMAL(15,2) DEFAULT 0.00,
    total_refunds DECIMAL(15,2) DEFAULT 0.00,
    cash_difference DECIMAL(15,2),
    status VARCHAR(20) DEFAULT 'open', -- open, closed
    opened_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    closed_at TIMESTAMP WITH TIME ZONE,
    notes TEXT
);

CREATE TABLE pos_sales (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    session_id UUID NOT NULL REFERENCES pos_sessions(id),
    sale_number VARCHAR(50) NOT NULL,
    customer_id UUID REFERENCES contacts(id),
    customer_name VARCHAR(255),
    sale_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    nhil_amount DECIMAL(15,2) DEFAULT 0.00,
    getfund_amount DECIMAL(15,2) DEFAULT 0.00,
    covid_levy_amount DECIMAL(15,2) DEFAULT 0.00,
    vat_amount DECIMAL(15,2) DEFAULT 0.00,
    total_amount DECIMAL(15,2) NOT NULL,
    amount_tendered DECIMAL(15,2),
    change_given DECIMAL(15,2),
    payment_method VARCHAR(50) NOT NULL, -- cash, mobile_money, card, split
    mobile_money_provider VARCHAR(50),
    mobile_money_number VARCHAR(20),
    status VARCHAR(20) DEFAULT 'completed', -- completed, refunded, voided
    notes TEXT,
    journal_entry_id UUID REFERENCES journal_entries(id),
    invoice_id UUID REFERENCES invoices(id),
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, sale_number)
);

CREATE TABLE pos_sale_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sale_id UUID NOT NULL REFERENCES pos_sales(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id),
    product_name VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,3) NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    cost_price DECIMAL(15,2) DEFAULT 0.00,
    discount_percent DECIMAL(5,2) DEFAULT 0.00,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    line_total DECIMAL(15,2) NOT NULL
);

CREATE TABLE pos_split_payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sale_id UUID NOT NULL REFERENCES pos_sales(id) ON DELETE CASCADE,
    payment_method VARCHAR(50) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    reference VARCHAR(100),
    mobile_money_provider VARCHAR(50),
    mobile_money_number VARCHAR(20)
);

-- ============================================================
-- 9. PAYROLL - Ghana PAYE, SSNIT, Tier 2, Tier 3
-- ============================================================

CREATE TABLE departments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    manager_id UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE employees (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    employee_number VARCHAR(50) NOT NULL,
    user_id UUID REFERENCES users(id),
    department_id UUID REFERENCES departments(id),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    other_names VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(10),
    marital_status VARCHAR(20),
    ghana_card_number VARCHAR(20),      -- National ID
    ssnit_number VARCHAR(20),           -- SSNIT Number
    tin_number VARCHAR(20),             -- Tax Identification Number
    email VARCHAR(255),
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    region VARCHAR(100),
    digital_address VARCHAR(20),
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(20),
    hire_date DATE NOT NULL,
    termination_date DATE,
    employment_type VARCHAR(30), -- full_time, part_time, contract, casual
    job_title VARCHAR(100),
    basic_salary DECIMAL(15,2) NOT NULL,
    salary_currency VARCHAR(3) DEFAULT 'GHS',
    payment_method VARCHAR(50) DEFAULT 'bank_transfer', -- bank_transfer, mobile_money, cash
    bank_name VARCHAR(100),
    bank_branch VARCHAR(100),
    bank_account_number VARCHAR(50),
    bank_sort_code VARCHAR(20),
    mobile_money_provider VARCHAR(50),
    mobile_money_number VARCHAR(20),
    -- Allowances
    housing_allowance DECIMAL(15,2) DEFAULT 0.00,
    transport_allowance DECIMAL(15,2) DEFAULT 0.00,
    meal_allowance DECIMAL(15,2) DEFAULT 0.00,
    utility_allowance DECIMAL(15,2) DEFAULT 0.00,
    other_allowances DECIMAL(15,2) DEFAULT 0.00,
    -- Deductions
    ssnit_employee_rate DECIMAL(5,2) DEFAULT 5.50,   -- Employee SSNIT contribution (5.5%)
    ssnit_employer_rate DECIMAL(5,2) DEFAULT 13.00,   -- Employer SSNIT contribution (13%)
    tier2_employee_rate DECIMAL(5,2) DEFAULT 5.00,    -- Tier 2 (5%)
    tier3_rate DECIMAL(5,2) DEFAULT 0.00,             -- Voluntary Tier 3
    provident_fund_rate DECIMAL(5,2) DEFAULT 0.00,
    loan_deduction DECIMAL(15,2) DEFAULT 0.00,
    other_deductions DECIMAL(15,2) DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'active', -- active, on_leave, terminated, suspended
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, employee_number)
);

-- Ghana PAYE Tax Brackets (2024/2025 rates)
CREATE TABLE paye_tax_brackets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    effective_date DATE NOT NULL,
    bracket_order INT NOT NULL,
    lower_limit DECIMAL(15,2) NOT NULL,
    upper_limit DECIMAL(15,2),          -- NULL means no upper limit
    rate DECIMAL(5,2) NOT NULL,         -- Percentage
    cumulative_tax DECIMAL(15,2) DEFAULT 0.00,
    description VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE
);

-- Insert Ghana PAYE Tax Brackets (2024/2025 Annual)
INSERT INTO paye_tax_brackets (effective_date, bracket_order, lower_limit, upper_limit, rate, cumulative_tax, description) VALUES
('2024-01-01', 1, 0, 490, 0, 0, 'First GHS 490 - Free'),
('2024-01-01', 2, 490, 600, 5, 5.50, 'Next GHS 110 at 5%'),
('2024-01-01', 3, 600, 730, 10, 18.50, 'Next GHS 130 at 10%'),
('2024-01-01', 4, 730, 3896.67, 17.5, 572.67, 'Next GHS 3,166.67 at 17.5%'),
('2024-01-01', 5, 3896.67, 20000, 25, 4598.50, 'Next GHS 16,103.33 at 25%'),
('2024-01-01', 6, 20000, 50000, 30, 13598.50, 'Next GHS 30,000 at 30%'),
('2024-01-01', 7, 50000, NULL, 35, NULL, 'Exceeding GHS 50,000 at 35%');

CREATE TABLE payroll_periods (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    period_name VARCHAR(100) NOT NULL,
    period_type VARCHAR(20) DEFAULT 'monthly', -- weekly, bi_weekly, monthly
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    payment_date DATE,
    status VARCHAR(20) DEFAULT 'draft', -- draft, processing, approved, paid, voided
    total_gross_pay DECIMAL(15,2) DEFAULT 0.00,
    total_deductions DECIMAL(15,2) DEFAULT 0.00,
    total_net_pay DECIMAL(15,2) DEFAULT 0.00,
    total_employer_cost DECIMAL(15,2) DEFAULT 0.00,
    approved_by UUID REFERENCES users(id),
    approved_at TIMESTAMP WITH TIME ZONE,
    journal_entry_id UUID REFERENCES journal_entries(id),
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE payslips (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    payroll_period_id UUID NOT NULL REFERENCES payroll_periods(id) ON DELETE CASCADE,
    employee_id UUID NOT NULL REFERENCES employees(id),
    -- Earnings
    basic_salary DECIMAL(15,2) NOT NULL,
    housing_allowance DECIMAL(15,2) DEFAULT 0.00,
    transport_allowance DECIMAL(15,2) DEFAULT 0.00,
    meal_allowance DECIMAL(15,2) DEFAULT 0.00,
    utility_allowance DECIMAL(15,2) DEFAULT 0.00,
    overtime_pay DECIMAL(15,2) DEFAULT 0.00,
    bonus DECIMAL(15,2) DEFAULT 0.00,
    commission DECIMAL(15,2) DEFAULT 0.00,
    other_earnings DECIMAL(15,2) DEFAULT 0.00,
    gross_salary DECIMAL(15,2) NOT NULL,
    -- Deductions
    ssnit_employee DECIMAL(15,2) DEFAULT 0.00, -- 5.5% of basic
    tier2_employee DECIMAL(15,2) DEFAULT 0.00,  -- 5% of basic
    tier3_voluntary DECIMAL(15,2) DEFAULT 0.00,
    total_relief DECIMAL(15,2) DEFAULT 0.00,    -- Total tax relief
    taxable_income DECIMAL(15,2) DEFAULT 0.00,
    paye_tax DECIMAL(15,2) DEFAULT 0.00,        -- Income tax
    loan_deduction DECIMAL(15,2) DEFAULT 0.00,
    provident_fund DECIMAL(15,2) DEFAULT 0.00,
    other_deductions DECIMAL(15,2) DEFAULT 0.00,
    total_deductions DECIMAL(15,2) DEFAULT 0.00,
    -- Net Pay
    net_salary DECIMAL(15,2) NOT NULL,
    -- Employer Contributions
    ssnit_employer DECIMAL(15,2) DEFAULT 0.00, -- 13% of basic
    total_employer_cost DECIMAL(15,2) DEFAULT 0.00,
    -- Payment
    payment_method VARCHAR(50),
    payment_reference VARCHAR(100),
    payment_status VARCHAR(20) DEFAULT 'pending', -- pending, paid
    paid_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(payroll_period_id, employee_id)
);

-- ============================================================
-- 10. TAX COMPLIANCE
-- ============================================================

CREATE TABLE tax_rates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
    tax_name VARCHAR(100) NOT NULL,
    tax_code VARCHAR(20) NOT NULL,
    rate DECIMAL(5,4) NOT NULL,          -- Rate as decimal (e.g., 0.15 for 15%)
    tax_type VARCHAR(30) NOT NULL,       -- vat, nhil, getfund, covid_levy, withholding, paye
    effective_date DATE NOT NULL,
    end_date DATE,
    is_compound BOOLEAN DEFAULT FALSE,   -- Applied on top of other taxes
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Insert Ghana Tax Rates
INSERT INTO tax_rates (tax_name, tax_code, rate, tax_type, effective_date, description) VALUES
('Standard VAT', 'VAT_STD', 0.15, 'vat', '2024-01-01', 'Standard VAT Rate 15%'),
('VAT Flat Rate', 'VAT_FLAT', 0.04, 'vat', '2024-01-01', 'VAT Flat Rate Scheme 4%'),
('NHIL', 'NHIL', 0.025, 'nhil', '2024-01-01', 'National Health Insurance Levy 2.5%'),
('GETFund Levy', 'GETFUND', 0.025, 'getfund', '2024-01-01', 'GETFund Levy 2.5%'),
('COVID-19 Levy', 'COVID', 0.01, 'covid_levy', '2024-01-01', 'COVID-19 Health Recovery Levy 1%'),
('WHT - Goods & Services', 'WHT_GS', 0.05, 'withholding', '2024-01-01', 'Withholding Tax on Goods & Services 5%'),
('WHT - Rent', 'WHT_RENT', 0.08, 'withholding', '2024-01-01', 'Withholding Tax on Rent 8%'),
('WHT - Management Fees', 'WHT_MGMT', 0.15, 'withholding', '2024-01-01', 'Withholding Tax on Management & Consultancy 15%'),
('CST', 'CST', 0.05, 'cst', '2024-01-01', 'Communication Service Tax 5%');

CREATE TABLE tax_filings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    tax_type VARCHAR(30) NOT NULL,       -- vat, paye, withholding
    filing_period VARCHAR(20) NOT NULL,  -- e.g., '2024-01', '2024-Q1'
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    output_tax DECIMAL(15,2) DEFAULT 0.00,  -- Tax collected
    input_tax DECIMAL(15,2) DEFAULT 0.00,   -- Tax paid
    tax_payable DECIMAL(15,2) DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'draft', -- draft, filed, paid
    filing_date DATE,
    payment_date DATE,
    reference_number VARCHAR(100),
    notes TEXT,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 11. BANK RECONCILIATION
-- ============================================================

CREATE TABLE bank_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES chart_of_accounts(id),
    bank_name VARCHAR(100) NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    branch VARCHAR(100),
    sort_code VARCHAR(20),
    swift_code VARCHAR(20),
    currency VARCHAR(3) DEFAULT 'GHS',
    opening_balance DECIMAL(15,2) DEFAULT 0.00,
    current_balance DECIMAL(15,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE bank_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    bank_account_id UUID NOT NULL REFERENCES bank_accounts(id),
    transaction_date DATE NOT NULL,
    description TEXT,
    reference VARCHAR(100),
    debit_amount DECIMAL(15,2) DEFAULT 0.00,
    credit_amount DECIMAL(15,2) DEFAULT 0.00,
    balance DECIMAL(15,2),
    is_reconciled BOOLEAN DEFAULT FALSE,
    reconciled_with UUID REFERENCES journal_entry_lines(id),
    imported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 12. FINANCIAL GOALS & BUDGETS
-- ============================================================

CREATE TABLE budgets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    budget_name VARCHAR(255) NOT NULL,
    fiscal_year INT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft', -- draft, approved, active, closed
    notes TEXT,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE budget_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    budget_id UUID NOT NULL REFERENCES budgets(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES chart_of_accounts(id),
    month_1 DECIMAL(15,2) DEFAULT 0.00,
    month_2 DECIMAL(15,2) DEFAULT 0.00,
    month_3 DECIMAL(15,2) DEFAULT 0.00,
    month_4 DECIMAL(15,2) DEFAULT 0.00,
    month_5 DECIMAL(15,2) DEFAULT 0.00,
    month_6 DECIMAL(15,2) DEFAULT 0.00,
    month_7 DECIMAL(15,2) DEFAULT 0.00,
    month_8 DECIMAL(15,2) DEFAULT 0.00,
    month_9 DECIMAL(15,2) DEFAULT 0.00,
    month_10 DECIMAL(15,2) DEFAULT 0.00,
    month_11 DECIMAL(15,2) DEFAULT 0.00,
    month_12 DECIMAL(15,2) DEFAULT 0.00,
    annual_total DECIMAL(15,2) GENERATED ALWAYS AS (
        month_1 + month_2 + month_3 + month_4 + month_5 + month_6 +
        month_7 + month_8 + month_9 + month_10 + month_11 + month_12
    ) STORED
);

CREATE TABLE financial_goals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    goal_name VARCHAR(255) NOT NULL,
    goal_type VARCHAR(50) NOT NULL,     -- revenue, profit, expense_reduction, savings
    target_amount DECIMAL(15,2) NOT NULL,
    current_amount DECIMAL(15,2) DEFAULT 0.00,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'active', -- active, achieved, failed, cancelled
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 13. AUDIT LOG
-- ============================================================

CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID REFERENCES companies(id),
    user_id UUID REFERENCES users(id),
    action VARCHAR(50) NOT NULL,        -- create, update, delete, login, logout, export
    entity_type VARCHAR(50) NOT NULL,   -- invoice, payment, journal_entry, etc.
    entity_id UUID,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 14. NOTIFICATIONS
-- ============================================================

CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id),     -- NULL = broadcast to all company users
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(30),                   -- info, warning, error, success
    entity_type VARCHAR(50),
    entity_id UUID,
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX idx_users_company ON users(company_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_tenants_slug ON tenants(slug);
CREATE INDEX idx_companies_tenant ON companies(tenant_id);
CREATE INDEX idx_user_businesses_user ON user_businesses(user_id);
CREATE INDEX idx_user_businesses_company ON user_businesses(company_id);
CREATE INDEX idx_journal_entries_company_date ON journal_entries(company_id, entry_date);
CREATE INDEX idx_journal_entries_status ON journal_entries(status);
CREATE INDEX idx_journal_entry_lines_entry ON journal_entry_lines(journal_entry_id);
CREATE INDEX idx_journal_entry_lines_account ON journal_entry_lines(account_id);
CREATE INDEX idx_invoices_company_date ON invoices(company_id, invoice_date);
CREATE INDEX idx_invoices_contact ON invoices(contact_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_payments_company_date ON payments(company_id, payment_date);
CREATE INDEX idx_contacts_company ON contacts(company_id);
CREATE INDEX idx_products_company ON products(company_id);
CREATE INDEX idx_products_sku ON products(company_id, sku);
CREATE INDEX idx_products_barcode ON products(barcode);
CREATE INDEX idx_pos_sales_session ON pos_sales(session_id);
CREATE INDEX idx_pos_sales_date ON pos_sales(company_id, sale_date);
CREATE INDEX idx_pos_sale_items_sale ON pos_sale_items(sale_id);
CREATE INDEX idx_employees_company ON employees(company_id);
CREATE INDEX idx_payslips_period ON payslips(payroll_period_id);
CREATE INDEX idx_payslips_employee ON payslips(employee_id);
CREATE INDEX idx_inventory_movements_product ON inventory_movements(product_id);
CREATE INDEX idx_audit_log_company ON audit_log(company_id);
CREATE INDEX idx_audit_log_entity ON audit_log(entity_type, entity_id);
CREATE INDEX idx_notifications_user ON notifications(user_id, is_read);
CREATE INDEX idx_bank_transactions_account ON bank_transactions(bank_account_id);
CREATE INDEX idx_chart_of_accounts_company ON chart_of_accounts(company_id);
CREATE INDEX idx_tax_filings_company ON tax_filings(company_id, tax_type);

-- ============================================================
-- 15. FILE ATTACHMENTS
-- ============================================================

CREATE TABLE IF NOT EXISTS file_attachments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INTEGER NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    uploaded_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_file_attachments_entity ON file_attachments(company_id, entity_type, entity_id);

-- ============================================================
-- 16. TRANSACTIONS (Income & Expense Tracking)
-- ============================================================

CREATE TABLE IF NOT EXISTS transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    transaction_number VARCHAR(50) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL, -- income, expense
    transaction_date DATE NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    description TEXT NOT NULL,
    category VARCHAR(100),
    payment_method VARCHAR(50) NOT NULL,     -- cash, mobile_money, bank_transfer, card, cheque
    mobile_money_provider VARCHAR(50),
    mobile_money_number VARCHAR(20),
    reference VARCHAR(100),
    contact_id UUID REFERENCES contacts(id),
    account_id UUID REFERENCES chart_of_accounts(id),
    notes TEXT,
    status VARCHAR(20) DEFAULT 'completed',  -- completed, voided
    journal_entry_id UUID REFERENCES journal_entries(id),
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(company_id, transaction_number)
);

CREATE INDEX IF NOT EXISTS idx_transactions_company_date ON transactions(company_id, transaction_date);
CREATE INDEX IF NOT EXISTS idx_transactions_type ON transactions(company_id, transaction_type);

-- ============================================================
-- 17. POS RETURNS
-- ============================================================

CREATE TABLE IF NOT EXISTS pos_returns (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    sale_id UUID NOT NULL REFERENCES pos_sales(id),
    sale_item_id UUID NOT NULL REFERENCES pos_sale_items(id),
    return_number VARCHAR(50) NOT NULL,
    product_id UUID NOT NULL REFERENCES products(id),
    product_name VARCHAR(255) NOT NULL,
    quantity_returned DECIMAL(10,3) NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    refund_amount DECIMAL(15,2) NOT NULL,
    reason TEXT NOT NULL,
    journal_entry_id UUID REFERENCES journal_entries(id),
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pos_returns_sale ON pos_returns(sale_id);
CREATE INDEX IF NOT EXISTS idx_pos_returns_company ON pos_returns(company_id, created_at);

-- ============================================================
-- DEFAULT CHART OF ACCOUNTS (Ghana Standard)
-- ============================================================

-- Function to populate default chart of accounts for a new company
CREATE OR REPLACE FUNCTION create_default_chart_of_accounts(p_company_id UUID)
RETURNS VOID AS $$
BEGIN
    -- Assets
    INSERT INTO chart_of_accounts (company_id, account_code, account_name, account_type, sub_type, is_system_account, normal_balance) VALUES
    (p_company_id, '1000', 'Cash on Hand', 'asset', 'current_asset', TRUE, 'debit'),
    (p_company_id, '1010', 'Petty Cash', 'asset', 'current_asset', TRUE, 'debit'),
    (p_company_id, '1100', 'Bank Account - GHS', 'asset', 'current_asset', TRUE, 'debit'),
    (p_company_id, '1110', 'Bank Account - USD', 'asset', 'current_asset', FALSE, 'debit'),
    (p_company_id, '1200', 'Accounts Receivable', 'asset', 'current_asset', TRUE, 'debit'),
    (p_company_id, '1300', 'Inventory', 'asset', 'current_asset', TRUE, 'debit'),
    (p_company_id, '1400', 'Prepaid Expenses', 'asset', 'current_asset', FALSE, 'debit'),
    (p_company_id, '1500', 'VAT Input (Receivable)', 'asset', 'current_asset', TRUE, 'debit'),
    (p_company_id, '1600', 'Fixed Assets', 'asset', 'fixed_asset', TRUE, 'debit'),
    (p_company_id, '1610', 'Furniture & Equipment', 'asset', 'fixed_asset', FALSE, 'debit'),
    (p_company_id, '1620', 'Motor Vehicles', 'asset', 'fixed_asset', FALSE, 'debit'),
    (p_company_id, '1630', 'Computer Equipment', 'asset', 'fixed_asset', FALSE, 'debit'),
    (p_company_id, '1700', 'Accumulated Depreciation', 'asset', 'fixed_asset', TRUE, 'credit'),
    (p_company_id, '1800', 'Mobile Money Float', 'asset', 'current_asset', FALSE, 'debit'),
    -- Liabilities
    (p_company_id, '2000', 'Accounts Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2100', 'VAT Output (Payable)', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2110', 'NHIL Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2120', 'GETFund Levy Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2130', 'COVID-19 Levy Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2200', 'PAYE Tax Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2210', 'SSNIT Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2220', 'Tier 2 Pension Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2300', 'Withholding Tax Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2400', 'Salaries Payable', 'liability', 'current_liability', TRUE, 'credit'),
    (p_company_id, '2500', 'Other Current Liabilities', 'liability', 'current_liability', FALSE, 'credit'),
    (p_company_id, '2600', 'Bank Loan', 'liability', 'long_term_liability', FALSE, 'credit'),
    -- Equity
    (p_company_id, '3000', 'Owner''s Equity / Capital', 'equity', 'equity', TRUE, 'credit'),
    (p_company_id, '3100', 'Retained Earnings', 'equity', 'equity', TRUE, 'credit'),
    (p_company_id, '3200', 'Drawings', 'equity', 'equity', FALSE, 'debit'),
    -- Revenue
    (p_company_id, '4000', 'Sales Revenue', 'revenue', 'operating_revenue', TRUE, 'credit'),
    (p_company_id, '4010', 'POS Sales', 'revenue', 'operating_revenue', TRUE, 'credit'),
    (p_company_id, '4100', 'Service Revenue', 'revenue', 'operating_revenue', FALSE, 'credit'),
    (p_company_id, '4200', 'Interest Income', 'revenue', 'other_revenue', FALSE, 'credit'),
    (p_company_id, '4300', 'Other Income', 'revenue', 'other_revenue', FALSE, 'credit'),
    (p_company_id, '4400', 'Discount Received', 'revenue', 'other_revenue', FALSE, 'credit'),
    -- Expenses
    (p_company_id, '5000', 'Cost of Goods Sold', 'expense', 'cost_of_sales', TRUE, 'debit'),
    (p_company_id, '5100', 'Salaries & Wages', 'expense', 'operating_expense', TRUE, 'debit'),
    (p_company_id, '5110', 'Employer SSNIT Contribution', 'expense', 'operating_expense', TRUE, 'debit'),
    (p_company_id, '5120', 'Employer Tier 2 Contribution', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5200', 'Rent Expense', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5210', 'Utilities Expense', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5220', 'Internet & Communication', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5300', 'Office Supplies', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5400', 'Transportation', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5500', 'Marketing & Advertising', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5600', 'Insurance', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5700', 'Depreciation Expense', 'expense', 'operating_expense', TRUE, 'debit'),
    (p_company_id, '5800', 'Bank Charges', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5810', 'Mobile Money Charges', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5900', 'Miscellaneous Expense', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '5910', 'Discount Given', 'expense', 'operating_expense', FALSE, 'debit'),
    (p_company_id, '6000', 'Interest Expense', 'expense', 'other_expense', FALSE, 'debit');
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- DEFAULT ADMIN SEED
-- Creates a demo company + admin user for first-time setup.
-- Password: admin123  (bcrypt hashed)
-- ============================================================
DO $$
DECLARE
    v_company_id UUID;
    v_user_id    UUID;
    v_tenant_id  UUID;
BEGIN
    -- Only seed if no users exist yet
    IF NOT EXISTS (SELECT 1 FROM users LIMIT 1) THEN
        -- Create default tenant
        INSERT INTO tenants (name, slug, plan, max_businesses, max_users)
        VALUES ('CashBook Demo', 'demo', 'professional', 100, 100)
        RETURNING id INTO v_tenant_id;

        -- Create demo company
        INSERT INTO companies (name, email, phone, city, region, currency, tenant_id)
        VALUES ('CashBook Demo', 'admin@cashbook.com', '0200000000', 'Accra', 'Greater Accra', 'GHS', v_tenant_id)
        RETURNING id INTO v_company_id;

        -- Create admin user  (password: admin123)
        INSERT INTO users (company_id, tenant_id, email, password_hash, first_name, last_name, phone, role, is_active)
        VALUES (
            v_company_id,
            v_tenant_id,
            'admin@cashbook.com',
            '$2y$12$p1ecB4yZThGSnT4.ic09t.HTy9Fy/hsF18BJZ1OabzMAJadHCJonS',
            'Admin', 'User', '0200000000', 'admin', TRUE
        )
        RETURNING id INTO v_user_id;

        -- Link admin to business
        INSERT INTO user_businesses (user_id, company_id, role, is_default)
        VALUES (v_user_id, v_company_id, 'admin', TRUE);

        -- Bootstrap default chart of accounts
        PERFORM create_default_chart_of_accounts(v_company_id);

        RAISE NOTICE 'Seeded admin user: admin@cashbook.com / admin123';
    END IF;
END;
$$;
