USE db_abos_v0.1;

-- =============================================================================================
-- accounting_ledger_module.sql — Chart of Accounts, true double-entry journal postings, tax
-- rate catalog, accounting period locking, and third-party accounting integration scaffolding
-- (CA-level audit follow-up: the existing Finance module has real pay/void/transfer ledger
-- entries but no Chart of Accounts, no accrual support, no tax engine, no period locking, and no
-- QBO/Xero/Sage data model or integration).
--
-- dim_accounts is a lookup table (not a hardcoded Java enum), mirroring dim_business_types /
-- dim_order_types exactly: tenant_id NULL = system-seeded standard Chart of Accounts, visible to
-- every tenant; a tenant-owned row has tenant_id set for a custom account.
--
-- fact_journal_entries is a new header table grouping the FactLedgerEntry rows of one balanced
-- journal entry (see LedgerService.postJournalEntry, the double-entry validation point).
-- fact_ledger_entries gains account_id/journal_entry_id/debit_amount/credit_amount columns;
-- wallet_id is relaxed from NOT NULL to NULL because a pure GL leg (e.g. the Revenue/Expense side
-- of a journal entry) has no wallet role at all — existing wallet-bearing rows are unaffected.
--
-- dim_tax_rates is tenant-scoped (NOT system-seeded — every tenant defines its own jurisdiction
-- rates). fact_invoices and fact_order_line_items each gain an optional tax_rate_id.
--
-- dim_accounting_periods backs real period locking, enforced in LedgerService.postJournalEntry
-- (not just documented — every new journal entry is checked against locked periods before insert).
--
-- dim_accounting_integrations is scaffolding ONLY for QuickBooks Online / Xero / Sage: status is
-- always NOT_CONNECTED and the oauth_* columns stay NULL. No real API credentials exist anywhere
-- in this repo (checked abos-agents/.env.example and abos-api/deploy/backend-core.env.example —
-- neither defines QBO/Xero/Sage keys), so AccountingIntegrationService.connectIntegration always
-- throws rather than faking a successful connection.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS, information_schema-guarded ALTERs, WHERE NOT EXISTS
-- seed guards — exact pattern as business_types_module.sql / order_types_module.sql.
-- =============================================================================================

SET @db := DATABASE();

-- ── Chart of Accounts ───────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_accounts (
    id                BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT NULL,
    account_code      VARCHAR(20) NOT NULL,
    account_name      VARCHAR(255) NOT NULL,
    account_type      VARCHAR(20) NOT NULL,
    parent_account_id BIGINT NULL,
    is_system         TINYINT NOT NULL DEFAULT 0,
    status            VARCHAR(20) NOT NULL DEFAULT 'Active',
    created_at        TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by        INT NULL,
    updated_at        TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by        INT NULL,
    is_deleted        TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_accounts_code (tenant_id, account_code),
    INDEX idx_dim_accounts_tenant (tenant_id),
    INDEX idx_dim_accounts_parent (parent_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Journal entry header (groups balanced FactLedgerEntry legs) ─────────────────────────────
CREATE TABLE IF NOT EXISTS fact_journal_entries (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT NOT NULL,
    business_id      INT NULL,
    posting_date     DATE NOT NULL,
    memo             VARCHAR(500) NULL,
    reference_entity VARCHAR(100) NULL,
    reference_id     BIGINT NULL,
    created_at       TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by       INT NULL,
    updated_at       TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by       INT NULL,
    is_deleted       TINYINT NOT NULL DEFAULT 0,
    INDEX idx_fact_journal_entries_tenant (tenant_id),
    INDEX idx_fact_journal_entries_business (business_id),
    INDEX idx_fact_journal_entries_posting_date (posting_date),
    INDEX idx_fact_journal_entries_reference (reference_entity, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Tax rate catalog (tenant-scoped, not system-seeded) ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_tax_rates (
    id             BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT NOT NULL,
    name           VARCHAR(255) NOT NULL,
    rate_percentage DECIMAL(7,4) NOT NULL,
    jurisdiction   VARCHAR(100) NULL,
    is_default     TINYINT NOT NULL DEFAULT 0,
    created_at     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by     INT NULL,
    updated_at     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by     INT NULL,
    is_deleted     TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_tax_rates_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Accounting period locking ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_accounting_periods (
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT NOT NULL,
    business_id  INT NULL,
    period_start DATE NOT NULL,
    period_end   DATE NOT NULL,
    is_locked    TINYINT NOT NULL DEFAULT 0,
    locked_at    TIMESTAMP(6) NULL,
    locked_by    INT NULL,
    created_at   TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by   INT NULL,
    updated_at   TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by   INT NULL,
    is_deleted   TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_accounting_periods_tenant (tenant_id),
    INDEX idx_dim_accounting_periods_range (tenant_id, period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Third-party accounting integration scaffolding (NOT functional) ─────────────────────────
CREATE TABLE IF NOT EXISTS dim_accounting_integrations (
    id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id           INT NOT NULL,
    provider            VARCHAR(20) NOT NULL,
    status              VARCHAR(20) NOT NULL DEFAULT 'NOT_CONNECTED',
    oauth_access_token  TEXT NULL,
    oauth_refresh_token TEXT NULL,
    oauth_expires_at    TIMESTAMP(6) NULL,
    created_at          TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by          INT NULL,
    updated_at          TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by          INT NULL,
    is_deleted          TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_accounting_integrations_provider (tenant_id, provider),
    INDEX idx_dim_accounting_integrations_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── fact_ledger_entries: add account_id / journal_entry_id / debit_amount / credit_amount ───
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_ledger_entries' AND COLUMN_NAME='account_id'),
  'ALTER TABLE fact_ledger_entries ADD COLUMN account_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_ledger_entries' AND COLUMN_NAME='journal_entry_id'),
  'ALTER TABLE fact_ledger_entries ADD COLUMN journal_entry_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_ledger_entries' AND COLUMN_NAME='debit_amount'),
  'ALTER TABLE fact_ledger_entries ADD COLUMN debit_amount DECIMAL(15,2) NULL DEFAULT 0.00');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_ledger_entries' AND COLUMN_NAME='credit_amount'),
  'ALTER TABLE fact_ledger_entries ADD COLUMN credit_amount DECIMAL(15,2) NULL DEFAULT 0.00');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- MODIFY COLUMN is naturally idempotent (re-running is a no-op once already nullable) — relax
-- wallet_id from NOT NULL so a pure GL leg (Revenue/Expense side of a journal entry) can omit it.
-- Every pre-existing wallet-bearing row is untouched; every existing caller keeps setting it.
ALTER TABLE fact_ledger_entries MODIFY COLUMN wallet_id INT NULL;

-- ── fact_invoices: add tax_rate_id ───────────────────────────────────────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_invoices' AND COLUMN_NAME='tax_rate_id'),
  'ALTER TABLE fact_invoices ADD COLUMN tax_rate_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── fact_order_line_items: add tax_rate_id / tax_amount ──────────────────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_order_line_items' AND COLUMN_NAME='tax_rate_id'),
  'ALTER TABLE fact_order_line_items ADD COLUMN tax_rate_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_order_line_items' AND COLUMN_NAME='tax_amount'),
  'ALTER TABLE fact_order_line_items ADD COLUMN tax_amount DECIMAL(15,2) NULL DEFAULT 0.00');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Seed the standard Chart of Accounts (tenant_id NULL = visible to every tenant) ──────────
INSERT INTO dim_accounts (tenant_id, account_code, account_name, account_type, parent_account_id, is_system, status, is_deleted)
SELECT NULL, v.account_code, v.account_name, v.account_type, NULL, 1, 'Active', 0
FROM (
    SELECT '1000' account_code, 'Cash' account_name, 'ASSET' account_type
    UNION ALL SELECT '1100', 'Accounts Receivable', 'ASSET'
    UNION ALL SELECT '2000', 'Accounts Payable', 'LIABILITY'
    UNION ALL SELECT '3000', "Owner's Equity", 'EQUITY'
    UNION ALL SELECT '4000', 'Revenue / Sales Income', 'REVENUE'
    UNION ALL SELECT '5000', 'Cost of Goods Sold', 'EXPENSE'
    UNION ALL SELECT '6000', 'Operating Expenses', 'EXPENSE'
) v
WHERE NOT EXISTS (
    SELECT 1 FROM dim_accounts a WHERE a.tenant_id IS NULL AND a.account_code = v.account_code
);