BEGIN;

CREATE SCHEMA IF NOT EXISTS accounting;

CREATE TABLE IF NOT EXISTS accounting.account_types (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    type_code varchar(40) NOT NULL,
    type_name varchar(120) NOT NULL,
    category varchar(40) NOT NULL DEFAULT 'balance_sheet',
    account_type_code varchar(40) NOT NULL,
    account_type_name varchar(120) NOT NULL,
    normal_balance varchar(20) NOT NULL DEFAULT 'debit',
    statement_type varchar(40) NOT NULL DEFAULT 'balance_sheet',
    display_order integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.chart_of_accounts (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    account_type_id bigint,
    parent_account_id bigint,
    account_code varchar(40) NOT NULL,
    account_name varchar(180) NOT NULL,
    account_type varchar(40) NOT NULL,
    normal_balance varchar(20) NOT NULL DEFAULT 'debit',
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    opening_balance numeric(18, 2) NOT NULL DEFAULT 0,
    current_balance numeric(18, 2) NOT NULL DEFAULT 0,
    description text,
    level integer NOT NULL DEFAULT 0,
    child_count integer NOT NULL DEFAULT 0,
    transaction_count integer NOT NULL DEFAULT 0,
    is_postable boolean NOT NULL DEFAULT TRUE,
    status varchar(40) NOT NULL DEFAULT 'active',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.journals (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    journal_no varchar(80) NOT NULL,
    reference_no varchar(120),
    journal_date date NOT NULL DEFAULT CURRENT_DATE,
    description text,
    source_module varchar(80),
    source_type varchar(80),
    source_id bigint,
    source_no varchar(120),
    debit_total numeric(18, 2) NOT NULL DEFAULT 0,
    credit_total numeric(18, 2) NOT NULL DEFAULT 0,
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    exchange_rate numeric(18, 8) NOT NULL DEFAULT 1,
    status varchar(40) NOT NULL DEFAULT 'draft',
    posted_at timestamp without time zone,
    posted_by uuid,
    posted_by_name varchar(180),
    reversed_at timestamp without time zone,
    reversed_by uuid,
    reversed_by_name varchar(180),
    reversal_journal_id bigint,
    created_by uuid,
    created_by_name varchar(180),
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.journal_entries (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    journal_id bigint NOT NULL,
    account_id bigint,
    account_code varchar(40) NOT NULL,
    account_name varchar(180),
    line_no integer NOT NULL DEFAULT 1,
    description text,
    debit_amount numeric(18, 2) NOT NULL DEFAULT 0,
    credit_amount numeric(18, 2) NOT NULL DEFAULT 0,
    running_balance numeric(18, 2),
    cost_center_id bigint,
    cost_center_name varchar(180),
    department_id bigint,
    department_name varchar(180),
    notes text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.customer_invoices (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    invoice_no varchar(80) NOT NULL,
    customer_id bigint,
    customer_code varchar(80),
    customer_name varchar(180) NOT NULL,
    customer_email varchar(180),
    invoice_date date NOT NULL DEFAULT CURRENT_DATE,
    due_date date,
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    subtotal_amount numeric(18, 2) NOT NULL DEFAULT 0,
    discount_amount numeric(18, 2) NOT NULL DEFAULT 0,
    tax_amount numeric(18, 2) NOT NULL DEFAULT 0,
    total_amount numeric(18, 2) NOT NULL DEFAULT 0,
    paid_amount numeric(18, 2) NOT NULL DEFAULT 0,
    balance_due numeric(18, 2) NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'draft',
    payment_terms varchar(80),
    description text,
    category varchar(120),
    journal_id bigint,
    sent_at timestamp without time zone,
    paid_at timestamp without time zone,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.vendor_bills (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    bill_no varchar(80) NOT NULL,
    supplier_id bigint,
    supplier_code varchar(80),
    supplier_name varchar(180),
    vendor_id bigint,
    vendor_code varchar(80),
    vendor_name varchar(180) NOT NULL,
    vendor_invoice_no varchar(120),
    bill_date date NOT NULL DEFAULT CURRENT_DATE,
    due_date date,
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    subtotal_amount numeric(18, 2) NOT NULL DEFAULT 0,
    discount_amount numeric(18, 2) NOT NULL DEFAULT 0,
    tax_amount numeric(18, 2) NOT NULL DEFAULT 0,
    total_amount numeric(18, 2) NOT NULL DEFAULT 0,
    paid_amount numeric(18, 2) NOT NULL DEFAULT 0,
    balance_due numeric(18, 2) NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'draft',
    payment_method varchar(80),
    bank_account_name varchar(180),
    description text,
    category varchar(120),
    journal_id bigint,
    paid_at timestamp without time zone,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.payments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    payment_no varchar(80) NOT NULL,
    vendor_bill_id bigint,
    supplier_id bigint,
    supplier_name varchar(180),
    vendor_id bigint,
    vendor_name varchar(180),
    payment_date date NOT NULL DEFAULT CURRENT_DATE,
    amount numeric(18, 2) NOT NULL DEFAULT 0,
    payment_method varchar(80),
    bank_account_id bigint,
    bank_account_name varchar(180),
    reference_no varchar(120),
    status varchar(40) NOT NULL DEFAULT 'posted',
    journal_id bigint,
    notes text,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.receipts (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    receipt_no varchar(80) NOT NULL,
    customer_invoice_id bigint,
    customer_id bigint,
    customer_name varchar(180),
    receipt_date date NOT NULL DEFAULT CURRENT_DATE,
    amount numeric(18, 2) NOT NULL DEFAULT 0,
    payment_method varchar(80),
    bank_account_id bigint,
    bank_account_name varchar(180),
    reference_no varchar(120),
    status varchar(40) NOT NULL DEFAULT 'posted',
    journal_id bigint,
    notes text,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE IF EXISTS accounting.account_types
    ADD COLUMN IF NOT EXISTS type_code varchar(40),
    ADD COLUMN IF NOT EXISTS type_name varchar(120),
    ADD COLUMN IF NOT EXISTS category varchar(40) DEFAULT 'balance_sheet',
    ADD COLUMN IF NOT EXISTS account_type_code varchar(40),
    ADD COLUMN IF NOT EXISTS account_type_name varchar(120),
    ADD COLUMN IF NOT EXISTS normal_balance varchar(20) DEFAULT 'debit',
    ADD COLUMN IF NOT EXISTS statement_type varchar(40) DEFAULT 'balance_sheet',
    ADD COLUMN IF NOT EXISTS display_order integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS is_active boolean DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.chart_of_accounts
    ADD COLUMN IF NOT EXISTS account_type_id bigint,
    ADD COLUMN IF NOT EXISTS parent_account_id bigint,
    ADD COLUMN IF NOT EXISTS account_code varchar(40),
    ADD COLUMN IF NOT EXISTS account_name varchar(180),
    ADD COLUMN IF NOT EXISTS account_type varchar(40),
    ADD COLUMN IF NOT EXISTS normal_balance varchar(20) DEFAULT 'debit',
    ADD COLUMN IF NOT EXISTS currency_code varchar(10) DEFAULT 'GHS',
    ADD COLUMN IF NOT EXISTS opening_balance numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS current_balance numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS description text,
    ADD COLUMN IF NOT EXISTS level integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS child_count integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS transaction_count integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS is_postable boolean DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS status varchar(40) DEFAULT 'active',
    ADD COLUMN IF NOT EXISTS is_active boolean DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.journals
    ADD COLUMN IF NOT EXISTS journal_no varchar(80),
    ADD COLUMN IF NOT EXISTS reference_no varchar(120),
    ADD COLUMN IF NOT EXISTS journal_date date DEFAULT CURRENT_DATE,
    ADD COLUMN IF NOT EXISTS description text,
    ADD COLUMN IF NOT EXISTS source_module varchar(80),
    ADD COLUMN IF NOT EXISTS source_type varchar(80),
    ADD COLUMN IF NOT EXISTS source_id bigint,
    ADD COLUMN IF NOT EXISTS source_no varchar(120),
    ADD COLUMN IF NOT EXISTS debit_total numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS credit_total numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS currency_code varchar(10) DEFAULT 'GHS',
    ADD COLUMN IF NOT EXISTS exchange_rate numeric(18, 8) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS status varchar(40) DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS posted_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS posted_by uuid,
    ADD COLUMN IF NOT EXISTS posted_by_name varchar(180),
    ADD COLUMN IF NOT EXISTS reversed_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS reversed_by uuid,
    ADD COLUMN IF NOT EXISTS reversed_by_name varchar(180),
    ADD COLUMN IF NOT EXISTS reversal_journal_id bigint,
    ADD COLUMN IF NOT EXISTS created_by uuid,
    ADD COLUMN IF NOT EXISTS created_by_name varchar(180),
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.journal_entries
    ADD COLUMN IF NOT EXISTS journal_id bigint,
    ADD COLUMN IF NOT EXISTS account_id bigint,
    ADD COLUMN IF NOT EXISTS account_code varchar(40),
    ADD COLUMN IF NOT EXISTS account_name varchar(180),
    ADD COLUMN IF NOT EXISTS line_no integer DEFAULT 1,
    ADD COLUMN IF NOT EXISTS description text,
    ADD COLUMN IF NOT EXISTS debit_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS credit_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS running_balance numeric(18, 2),
    ADD COLUMN IF NOT EXISTS cost_center_id bigint,
    ADD COLUMN IF NOT EXISTS cost_center_name varchar(180),
    ADD COLUMN IF NOT EXISTS department_id bigint,
    ADD COLUMN IF NOT EXISTS department_name varchar(180),
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.customer_invoices
    ADD COLUMN IF NOT EXISTS invoice_no varchar(80),
    ADD COLUMN IF NOT EXISTS customer_id bigint,
    ADD COLUMN IF NOT EXISTS customer_code varchar(80),
    ADD COLUMN IF NOT EXISTS customer_name varchar(180),
    ADD COLUMN IF NOT EXISTS customer_email varchar(180),
    ADD COLUMN IF NOT EXISTS invoice_date date DEFAULT CURRENT_DATE,
    ADD COLUMN IF NOT EXISTS due_date date,
    ADD COLUMN IF NOT EXISTS currency_code varchar(10) DEFAULT 'GHS',
    ADD COLUMN IF NOT EXISTS subtotal_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS discount_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS tax_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS total_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS paid_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS balance_due numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS status varchar(40) DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS payment_terms varchar(80),
    ADD COLUMN IF NOT EXISTS description text,
    ADD COLUMN IF NOT EXISTS category varchar(120),
    ADD COLUMN IF NOT EXISTS journal_id bigint,
    ADD COLUMN IF NOT EXISTS sent_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS paid_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.vendor_bills
    ADD COLUMN IF NOT EXISTS bill_no varchar(80),
    ADD COLUMN IF NOT EXISTS supplier_id bigint,
    ADD COLUMN IF NOT EXISTS supplier_code varchar(80),
    ADD COLUMN IF NOT EXISTS supplier_name varchar(180),
    ADD COLUMN IF NOT EXISTS vendor_id bigint,
    ADD COLUMN IF NOT EXISTS vendor_code varchar(80),
    ADD COLUMN IF NOT EXISTS vendor_name varchar(180),
    ADD COLUMN IF NOT EXISTS vendor_invoice_no varchar(120),
    ADD COLUMN IF NOT EXISTS bill_date date DEFAULT CURRENT_DATE,
    ADD COLUMN IF NOT EXISTS due_date date,
    ADD COLUMN IF NOT EXISTS currency_code varchar(10) DEFAULT 'GHS',
    ADD COLUMN IF NOT EXISTS subtotal_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS discount_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS tax_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS total_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS paid_amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS balance_due numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS status varchar(40) DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS payment_method varchar(80),
    ADD COLUMN IF NOT EXISTS bank_account_name varchar(180),
    ADD COLUMN IF NOT EXISTS description text,
    ADD COLUMN IF NOT EXISTS category varchar(120),
    ADD COLUMN IF NOT EXISTS journal_id bigint,
    ADD COLUMN IF NOT EXISTS paid_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.payments
    ADD COLUMN IF NOT EXISTS payment_no varchar(80),
    ADD COLUMN IF NOT EXISTS vendor_bill_id bigint,
    ADD COLUMN IF NOT EXISTS supplier_id bigint,
    ADD COLUMN IF NOT EXISTS supplier_name varchar(180),
    ADD COLUMN IF NOT EXISTS vendor_id bigint,
    ADD COLUMN IF NOT EXISTS vendor_name varchar(180),
    ADD COLUMN IF NOT EXISTS payment_date date DEFAULT CURRENT_DATE,
    ADD COLUMN IF NOT EXISTS amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS payment_method varchar(80),
    ADD COLUMN IF NOT EXISTS bank_account_id bigint,
    ADD COLUMN IF NOT EXISTS bank_account_name varchar(180),
    ADD COLUMN IF NOT EXISTS reference_no varchar(120),
    ADD COLUMN IF NOT EXISTS status varchar(40) DEFAULT 'posted',
    ADD COLUMN IF NOT EXISTS journal_id bigint,
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.receipts
    ADD COLUMN IF NOT EXISTS receipt_no varchar(80),
    ADD COLUMN IF NOT EXISTS customer_invoice_id bigint,
    ADD COLUMN IF NOT EXISTS customer_id bigint,
    ADD COLUMN IF NOT EXISTS customer_name varchar(180),
    ADD COLUMN IF NOT EXISTS receipt_date date DEFAULT CURRENT_DATE,
    ADD COLUMN IF NOT EXISTS amount numeric(18, 2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS payment_method varchar(80),
    ADD COLUMN IF NOT EXISTS bank_account_id bigint,
    ADD COLUMN IF NOT EXISTS bank_account_name varchar(180),
    ADD COLUMN IF NOT EXISTS reference_no varchar(120),
    ADD COLUMN IF NOT EXISTS status varchar(40) DEFAULT 'posted',
    ADD COLUMN IF NOT EXISTS journal_id bigint,
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE IF EXISTS accounting.expenses
    ADD COLUMN IF NOT EXISTS supplier_id bigint,
    ADD COLUMN IF NOT EXISTS vendor_id bigint,
    ADD COLUMN IF NOT EXISTS vendor_name varchar(180);

CREATE TABLE IF NOT EXISTS accounting.fiscal_periods (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    period_code varchar(40) NOT NULL,
    period_name varchar(120) NOT NULL,
    fiscal_year integer NOT NULL,
    period_number integer NOT NULL,
    start_date date NOT NULL,
    end_date date NOT NULL,
    status varchar(40) NOT NULL DEFAULT 'open',
    closed_at timestamp without time zone,
    closed_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.bank_accounts (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    account_id bigint,
    account_code varchar(40),
    bank_account_code varchar(80) NOT NULL,
    bank_account_name varchar(180) NOT NULL,
    bank_name varchar(180),
    account_number varchar(80),
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    opening_balance numeric(18, 2) NOT NULL DEFAULT 0,
    current_balance numeric(18, 2) NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'active',
    is_default boolean NOT NULL DEFAULT FALSE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.bank_transactions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    bank_account_id bigint,
    transaction_no varchar(80) NOT NULL,
    transaction_date date NOT NULL DEFAULT CURRENT_DATE,
    value_date date,
    transaction_type varchar(60) NOT NULL,
    description text,
    amount numeric(18, 2) NOT NULL DEFAULT 0,
    direction varchar(20) NOT NULL DEFAULT 'out',
    reference_no varchar(120),
    status varchar(40) NOT NULL DEFAULT 'posted',
    journal_id bigint,
    matched_document_type varchar(80),
    matched_document_id bigint,
    matched_document_no varchar(120),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.expense_categories (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    category_code varchar(80) NOT NULL,
    category_name varchar(120) NOT NULL,
    default_debit_account_id bigint,
    default_debit_account_code varchar(40),
    default_debit_account_name varchar(180),
    status varchar(40) NOT NULL DEFAULT 'active',
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.expenses (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    expense_no varchar(80) NOT NULL,
    supplier_id bigint,
    vendor_id bigint,
    vendor_name varchar(180) NOT NULL,
    expense_category_id bigint,
    category varchar(120),
    amount numeric(18, 2) NOT NULL DEFAULT 0,
    expense_date date NOT NULL DEFAULT CURRENT_DATE,
    payment_method varchar(80),
    status varchar(40) NOT NULL DEFAULT 'draft',
    description text,
    debit_account_id bigint,
    debit_account_code varchar(40),
    debit_account_name varchar(180),
    credit_account_id bigint,
    credit_account_code varchar(40),
    credit_account_name varchar(180),
    attachment_count integer NOT NULL DEFAULT 0,
    submitted_by uuid,
    submitted_by_name varchar(180),
    journal_id bigint,
    journal_no varchar(80),
    approved_at timestamp without time zone,
    paid_at timestamp without time zone,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.customer_invoice_items (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    customer_invoice_id bigint NOT NULL,
    item_id bigint,
    item_code varchar(80),
    item_name varchar(180) NOT NULL,
    description text,
    quantity numeric(18, 4) NOT NULL DEFAULT 1,
    unit_of_measure varchar(40),
    unit_price numeric(18, 4) NOT NULL DEFAULT 0,
    discount_percent numeric(8, 4) NOT NULL DEFAULT 0,
    tax_rate numeric(8, 4) NOT NULL DEFAULT 0,
    tax_amount numeric(18, 2) NOT NULL DEFAULT 0,
    line_total numeric(18, 2) NOT NULL DEFAULT 0,
    revenue_account_id bigint,
    revenue_account_code varchar(40),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.vendor_bill_items (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    vendor_bill_id bigint NOT NULL,
    item_id bigint,
    item_code varchar(80),
    item_name varchar(180) NOT NULL,
    description text,
    quantity numeric(18, 4) NOT NULL DEFAULT 1,
    unit_of_measure varchar(40),
    unit_price numeric(18, 4) NOT NULL DEFAULT 0,
    discount_percent numeric(8, 4) NOT NULL DEFAULT 0,
    tax_rate numeric(8, 4) NOT NULL DEFAULT 0,
    tax_amount numeric(18, 2) NOT NULL DEFAULT 0,
    line_total numeric(18, 2) NOT NULL DEFAULT 0,
    expense_account_id bigint,
    expense_account_code varchar(40),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.receipt_allocations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    receipt_id bigint NOT NULL,
    customer_invoice_id bigint NOT NULL,
    allocated_amount numeric(18, 2) NOT NULL DEFAULT 0,
    allocated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS accounting.payment_allocations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    payment_id bigint NOT NULL,
    vendor_bill_id bigint NOT NULL,
    allocated_amount numeric(18, 2) NOT NULL DEFAULT 0,
    allocated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS accounting.tax_codes (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    tax_code varchar(40) NOT NULL,
    tax_name varchar(120) NOT NULL,
    tax_type varchar(80) NOT NULL,
    rate_percent numeric(8, 4),
    collected_account_id bigint,
    paid_account_id bigint,
    status varchar(40) NOT NULL DEFAULT 'active',
    is_deleted boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.tax_filings (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    filing_no varchar(80) NOT NULL,
    tax_code_id bigint,
    tax_type varchar(120) NOT NULL,
    tax_code varchar(40),
    period_label varchar(80) NOT NULL,
    period_start date,
    period_end date,
    due_date date,
    collected_amount numeric(18, 2) NOT NULL DEFAULT 0,
    paid_amount numeric(18, 2) NOT NULL DEFAULT 0,
    net_payable numeric(18, 2) NOT NULL DEFAULT 0,
    transaction_count integer NOT NULL DEFAULT 0,
    filing_status varchar(40) NOT NULL DEFAULT 'draft',
    submitted_at timestamp without time zone,
    submitted_by uuid,
    submitted_by_name varchar(180),
    reference_no varchar(120),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.cash_flow_categories (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    category_code varchar(80) NOT NULL,
    category_name varchar(180) NOT NULL,
    section varchar(40) NOT NULL,
    display_order integer NOT NULL DEFAULT 0,
    description text,
    status varchar(40) NOT NULL DEFAULT 'active',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.accounting_document_links (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    journal_id bigint,
    source_document_type varchar(80) NOT NULL,
    source_document_id bigint,
    source_document_no varchar(120) NOT NULL,
    linked_document_type varchar(80),
    linked_document_id bigint,
    linked_document_no varchar(120),
    amount numeric(18, 2) NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS accounting.accounting_attachments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    entity_type varchar(80) NOT NULL,
    entity_id bigint NOT NULL,
    file_name varchar(255) NOT NULL,
    file_reference text NOT NULL,
    file_type varchar(120),
    file_size_bytes bigint,
    uploaded_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uploaded_by uuid,
    uploaded_by_name varchar(180),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS accounting.accounting_audit_events (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    entity_type varchar(80) NOT NULL,
    entity_id bigint,
    entity_no varchar(120),
    event_type varchar(80) NOT NULL,
    event_title varchar(180),
    event_details text,
    from_status varchar(80),
    to_status varchar(80),
    occurred_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    occurred_by uuid,
    occurred_by_name varchar(180),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_account_types_code
    ON accounting.account_types (tenant_id, account_type_code)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_account_types_type_code
    ON accounting.account_types (tenant_id, type_code)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_chart_of_accounts_code
    ON accounting.chart_of_accounts (tenant_id, account_code)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_journals_no
    ON accounting.journals (tenant_id, journal_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE INDEX IF NOT EXISTS idx_journal_entries_account_date
    ON accounting.journal_entries (tenant_id, account_code, journal_id);
CREATE UNIQUE INDEX IF NOT EXISTS uq_customer_invoices_no
    ON accounting.customer_invoices (tenant_id, invoice_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_vendor_bills_no
    ON accounting.vendor_bills (tenant_id, bill_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_payments_no
    ON accounting.payments (tenant_id, payment_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_receipts_no
    ON accounting.receipts (tenant_id, receipt_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_fiscal_periods_code
    ON accounting.fiscal_periods (tenant_id, period_code);
CREATE UNIQUE INDEX IF NOT EXISTS uq_bank_accounts_code
    ON accounting.bank_accounts (tenant_id, bank_account_code)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_expense_categories_code
    ON accounting.expense_categories (tenant_id, category_code)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_expenses_no
    ON accounting.expenses (tenant_id, expense_no)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_tax_codes_code
    ON accounting.tax_codes (tenant_id, tax_code)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_tax_filings_no
    ON accounting.tax_filings (tenant_id, filing_no);
CREATE UNIQUE INDEX IF NOT EXISTS uq_cash_flow_categories_code
    ON accounting.cash_flow_categories (tenant_id, category_code);

COMMIT;