CREATE TABLE chart_of_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(150) NOT NULL,
    account_type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
    account_subtype VARCHAR(80) NULL,
    normal_balance ENUM('debit', 'credit') NOT NULL,
    is_control_account BOOLEAN NOT NULL DEFAULT FALSE,
    allow_posting BOOLEAN NOT NULL DEFAULT TRUE,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_account_code (company_id, code),
    CONSTRAINT fk_account_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_account_parent FOREIGN KEY (parent_id) REFERENCES chart_of_accounts (id)
) ENGINE=InnoDB;

CREATE TABLE journal_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    journal_number VARCHAR(60) NOT NULL UNIQUE,
    journal_date DATE NOT NULL,
    journal_type ENUM('general', 'cash', 'bank', 'receivable', 'payable', 'adjustment', 'opening', 'closing') NOT NULL DEFAULT 'general',
    reference_type VARCHAR(60) NULL,
    reference_id BIGINT UNSIGNED NULL,
    reference_number VARCHAR(100) NULL,
    description VARCHAR(500) NOT NULL,
    total_debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    status ENUM('draft', 'posted', 'reversed', 'cancelled') NOT NULL DEFAULT 'draft',
    posted_by BIGINT UNSIGNED NULL,
    posted_at TIMESTAMP NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_journal_company_date (company_id, journal_date),
    CONSTRAINT fk_journal_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_journal_poster FOREIGN KEY (posted_by) REFERENCES users (id),
    CONSTRAINT fk_journal_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE journal_details (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    line_number INT UNSIGNED NOT NULL,
    description VARCHAR(500) NULL,
    debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    cost_center_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_journal_line (journal_entry_id, line_number),
    CONSTRAINT fk_journal_detail_header FOREIGN KEY (journal_entry_id) REFERENCES journal_entries (id),
    CONSTRAINT fk_journal_detail_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_journal_detail_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers (id)
) ENGINE=InnoDB;

CREATE TABLE cash_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    transaction_number VARCHAR(60) NOT NULL UNIQUE,
    transaction_date DATE NOT NULL,
    transaction_type ENUM('receipt', 'payment') NOT NULL,
    cash_account_id BIGINT UNSIGNED NOT NULL,
    contra_account_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(18,4) NOT NULL,
    party_type VARCHAR(40) NULL,
    party_name VARCHAR(150) NULL,
    reference_number VARCHAR(100) NULL,
    description VARCHAR(500) NOT NULL,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    status ENUM('posted', 'cancelled') NOT NULL DEFAULT 'posted',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_cash_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_cash_account FOREIGN KEY (cash_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_cash_contra FOREIGN KEY (contra_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_cash_journal FOREIGN KEY (journal_entry_id) REFERENCES journal_entries (id),
    CONSTRAINT fk_cash_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE bank_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    transaction_number VARCHAR(60) NOT NULL UNIQUE,
    transaction_date DATE NOT NULL,
    transaction_type ENUM('deposit', 'withdrawal', 'transfer_in', 'transfer_out', 'charge', 'interest') NOT NULL,
    bank_account_id BIGINT UNSIGNED NOT NULL,
    contra_account_id BIGINT UNSIGNED NOT NULL,
    bank_name VARCHAR(120) NULL,
    bank_account_number VARCHAR(80) NULL,
    cheque_number VARCHAR(80) NULL,
    amount DECIMAL(18,4) NOT NULL,
    reference_number VARCHAR(100) NULL,
    description VARCHAR(500) NOT NULL,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    status ENUM('posted', 'reconciled', 'cancelled') NOT NULL DEFAULT 'posted',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_bank_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_bank_account FOREIGN KEY (bank_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_bank_contra FOREIGN KEY (contra_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_bank_journal FOREIGN KEY (journal_entry_id) REFERENCES journal_entries (id),
    CONSTRAINT fk_bank_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE receivables (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    document_number VARCHAR(60) NOT NULL UNIQUE,
    buyer_id BIGINT UNSIGNED NULL,
    document_type ENUM('invoice', 'receipt', 'credit_note', 'debit_note') NOT NULL,
    document_date DATE NOT NULL,
    due_date DATE NULL,
    receivable_account_id BIGINT UNSIGNED NOT NULL,
    offset_account_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(18,4) NOT NULL,
    balance_amount DECIMAL(18,4) NOT NULL,
    reference_number VARCHAR(100) NULL,
    description VARCHAR(500) NOT NULL,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    status ENUM('open', 'partial', 'settled', 'overdue', 'cancelled') NOT NULL DEFAULT 'open',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_receivable_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_receivable_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_receivable_account FOREIGN KEY (receivable_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_receivable_offset FOREIGN KEY (offset_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_receivable_journal FOREIGN KEY (journal_entry_id) REFERENCES journal_entries (id),
    CONSTRAINT fk_receivable_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE payables (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    document_number VARCHAR(60) NOT NULL UNIQUE,
    supplier_id BIGINT UNSIGNED NULL,
    document_type ENUM('bill', 'payment', 'credit_note', 'debit_note') NOT NULL,
    document_date DATE NOT NULL,
    due_date DATE NULL,
    payable_account_id BIGINT UNSIGNED NOT NULL,
    offset_account_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(18,4) NOT NULL,
    balance_amount DECIMAL(18,4) NOT NULL,
    reference_number VARCHAR(100) NULL,
    description VARCHAR(500) NOT NULL,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    status ENUM('open', 'partial', 'settled', 'overdue', 'cancelled') NOT NULL DEFAULT 'open',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_payable_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_payable_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_payable_account FOREIGN KEY (payable_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_payable_offset FOREIGN KEY (offset_account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_payable_journal FOREIGN KEY (journal_entry_id) REFERENCES journal_entries (id),
    CONSTRAINT fk_payable_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE general_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    journal_detail_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    posting_date DATE NOT NULL,
    journal_number VARCHAR(60) NOT NULL,
    description VARCHAR(500) NULL,
    debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    signed_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ledger_journal_detail (journal_detail_id),
    KEY idx_ledger_account_date (company_id, account_id, posting_date),
    CONSTRAINT fk_ledger_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_ledger_journal FOREIGN KEY (journal_entry_id) REFERENCES journal_entries (id),
    CONSTRAINT fk_ledger_detail FOREIGN KEY (journal_detail_id) REFERENCES journal_details (id),
    CONSTRAINT fk_ledger_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts (id)
) ENGINE=InnoDB;

CREATE TABLE trial_balance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    snapshot_number VARCHAR(60) NOT NULL,
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    opening_debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    opening_credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    period_debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    period_credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    closing_debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    closing_credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    generated_by BIGINT UNSIGNED NOT NULL,
    generated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_trial_snapshot (company_id, snapshot_number),
    CONSTRAINT fk_trial_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_trial_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts (id),
    CONSTRAINT fk_trial_generator FOREIGN KEY (generated_by) REFERENCES users (id)
) ENGINE=InnoDB;