CREATE TABLE IF NOT EXISTS finance_accounts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    business_id INT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    code VARCHAR(50) NOT NULL,
    type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
    subtype VARCHAR(100) DEFAULT NULL,
    normal_balance ENUM('debit', 'credit') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY finance_accounts_business_code_unique (business_id, code),
    KEY finance_accounts_business_type_idx (business_id, type, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_business_settings (
    business_id INT UNSIGNED NOT NULL,
    fiscal_year_start_month TINYINT UNSIGNED NOT NULL DEFAULT 1,
    fiscal_year_start_day TINYINT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (business_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_payees (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    business_id INT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    payee_type VARCHAR(50) NOT NULL DEFAULT 'vendor',
    contact_name VARCHAR(190) DEFAULT NULL,
    email VARCHAR(190) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY finance_payees_business_name_unique (business_id, name),
    KEY finance_payees_business_active_idx (business_id, is_active, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_journal_entries (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    business_id INT UNSIGNED NOT NULL,
    entry_date DATE NOT NULL,
    memo VARCHAR(255) DEFAULT NULL,
    reference_number VARCHAR(100) DEFAULT NULL,
    source_type ENUM('manual', 'income', 'expense', 'transfer', 'owner_contribution', 'owner_draw', 'loan', 'asset_purchase') NOT NULL DEFAULT 'manual',
    created_by INT UNSIGNED DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY finance_journal_entries_business_date_idx (business_id, entry_date),
    KEY finance_journal_entries_source_idx (source_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_journal_entry_lines (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    journal_entry_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    debit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY finance_journal_lines_entry_idx (journal_entry_id),
    KEY finance_journal_lines_account_idx (account_id),
    CONSTRAINT finance_journal_lines_entry_fk FOREIGN KEY (journal_entry_id) REFERENCES finance_journal_entries (id) ON DELETE CASCADE,
    CONSTRAINT finance_journal_lines_account_fk FOREIGN KEY (account_id) REFERENCES finance_accounts (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_transactions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    business_id INT UNSIGNED NOT NULL,
    transaction_date DATE NOT NULL,
    type VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    counterparty_name VARCHAR(190) DEFAULT NULL,
    linked_journal_entry_id INT UNSIGNED DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY finance_transactions_business_date_idx (business_id, transaction_date),
    KEY finance_transactions_entry_idx (linked_journal_entry_id),
    CONSTRAINT finance_transactions_entry_fk FOREIGN KEY (linked_journal_entry_id) REFERENCES finance_journal_entries (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE finance_transactions
    ADD COLUMN IF NOT EXISTS payee_id INT UNSIGNED DEFAULT NULL AFTER counterparty_name,
    ADD COLUMN IF NOT EXISTS register_account_id INT UNSIGNED DEFAULT NULL AFTER payee_id,
    ADD COLUMN IF NOT EXISTS offset_account_id INT UNSIGNED DEFAULT NULL AFTER register_account_id,
    ADD COLUMN IF NOT EXISTS payment_method VARCHAR(50) DEFAULT NULL AFTER offset_account_id,
    ADD COLUMN IF NOT EXISTS transaction_number VARCHAR(100) DEFAULT NULL AFTER payment_method,
    ADD COLUMN IF NOT EXISTS direction ENUM('inflow', 'outflow', 'transfer') NOT NULL DEFAULT 'outflow' AFTER transaction_number,
    ADD COLUMN IF NOT EXISTS status VARCHAR(50) NOT NULL DEFAULT 'posted' AFTER direction;

CREATE TABLE IF NOT EXISTS finance_products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    business_id INT UNSIGNED NOT NULL,
    sku VARCHAR(100) DEFAULT NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT DEFAULT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    income_account_id INT UNSIGNED NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY finance_products_business_active_idx (business_id, is_active, name),
    KEY finance_products_income_account_idx (income_account_id),
    CONSTRAINT finance_products_income_account_fk FOREIGN KEY (income_account_id) REFERENCES finance_accounts (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_invoices (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    business_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    invoice_number VARCHAR(100) NOT NULL,
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'draft',
    notes TEXT DEFAULT NULL,
    subtotal DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    linked_journal_entry_id INT UNSIGNED DEFAULT NULL,
    created_by INT UNSIGNED DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY finance_invoices_business_number_unique (business_id, invoice_number),
    KEY finance_invoices_business_date_idx (business_id, issue_date, status),
    KEY finance_invoices_customer_idx (customer_id),
    KEY finance_invoices_journal_idx (linked_journal_entry_id),
    CONSTRAINT finance_invoices_customer_fk FOREIGN KEY (customer_id) REFERENCES finance_payees (id) ON DELETE RESTRICT,
    CONSTRAINT finance_invoices_journal_fk FOREIGN KEY (linked_journal_entry_id) REFERENCES finance_journal_entries (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_invoice_lines (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice_id INT UNSIGNED NOT NULL,
    line_number INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED DEFAULT NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(14,2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    revenue_account_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY finance_invoice_lines_invoice_idx (invoice_id, line_number),
    KEY finance_invoice_lines_product_idx (product_id),
    KEY finance_invoice_lines_revenue_account_idx (revenue_account_id),
    CONSTRAINT finance_invoice_lines_invoice_fk FOREIGN KEY (invoice_id) REFERENCES finance_invoices (id) ON DELETE CASCADE,
    CONSTRAINT finance_invoice_lines_product_fk FOREIGN KEY (product_id) REFERENCES finance_products (id) ON DELETE SET NULL,
    CONSTRAINT finance_invoice_lines_revenue_account_fk FOREIGN KEY (revenue_account_id) REFERENCES finance_accounts (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;