-- Accounting Phase 3 — Banking & Reconciliation Schema
-- Run AFTER 002_phase2.sql
-- Compatible: MariaDB 10.6+

SET FOREIGN_KEY_CHECKS = 0;

-- ─── Bank Accounts ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bank_accounts (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NOT NULL,
  name           VARCHAR(255) NOT NULL,
  account_number VARCHAR(50)  DEFAULT NULL,
  bank_name      VARCHAR(255) DEFAULT NULL,
  account_id     INT UNSIGNED DEFAULT NULL,   -- FK → GL cash account
  is_deleted     TINYINT(1)   DEFAULT 0,
  created_at     DATETIME     DEFAULT NOW(),
  updated_at     DATETIME     ON UPDATE NOW(),
  INDEX idx_tenant (tenant_id),
  CONSTRAINT fk_ba_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
  CONSTRAINT fk_ba_account FOREIGN KEY (account_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Bank Transactions ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bank_transactions (
  id               INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id        INT UNSIGNED  NOT NULL,
  bank_account_id  INT UNSIGNED  NOT NULL,
  date             DATE          NOT NULL,
  description      TEXT          NOT NULL,
  amount           DECIMAL(15,2) NOT NULL,             -- always positive
  type             ENUM('debit','credit') NOT NULL,    -- debit = money out, credit = money in
  -- matching
  matched_entry_id INT UNSIGNED  DEFAULT NULL,         -- FK → journal_entries (auto-created entry)
  matched_type     ENUM('journal_entry','invoice_payment','bill_payment') DEFAULT NULL,
  matched_id       INT UNSIGNED  DEFAULT NULL,         -- ID within matched_type table
  -- status
  status           ENUM('unmatched','matched','excluded','reconciled') DEFAULT 'unmatched',
  -- reconciliation
  is_cleared       TINYINT(1)    DEFAULT 0,            -- marked during active reconciliation
  -- import metadata
  import_batch     VARCHAR(36)   DEFAULT NULL,         -- groups transactions from one import
  is_duplicate     TINYINT(1)    DEFAULT 0,
  created_at       DATETIME      DEFAULT NOW(),
  INDEX idx_tenant    (tenant_id),
  INDEX idx_bank_acct (bank_account_id),
  INDEX idx_date      (tenant_id, date),
  INDEX idx_status    (tenant_id, status),
  CONSTRAINT fk_bt_tenant    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)       ON DELETE CASCADE,
  CONSTRAINT fk_bt_bank_acct FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Reconciliations ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS reconciliations (
  id               INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id        INT UNSIGNED  NOT NULL,
  bank_account_id  INT UNSIGNED  NOT NULL,
  statement_date   DATE          NOT NULL,
  opening_balance  DECIMAL(15,2) DEFAULT 0.00,
  ending_balance   DECIMAL(15,2) NOT NULL,
  completed_at     DATETIME      DEFAULT NULL,
  created_at       DATETIME      DEFAULT NOW(),
  INDEX idx_tenant    (tenant_id),
  INDEX idx_bank_acct (bank_account_id),
  CONSTRAINT fk_rec_tenant    FOREIGN KEY (tenant_id)       REFERENCES tenants(id)       ON DELETE CASCADE,
  CONSTRAINT fk_rec_bank_acct FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Categorization Rules ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS categorization_rules (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  match_text VARCHAR(255) NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  priority   INT          DEFAULT 0,         -- higher = matched first
  created_at DATETIME     DEFAULT NOW(),
  INDEX idx_tenant   (tenant_id),
  INDEX idx_priority (tenant_id, priority DESC),
  CONSTRAINT fk_cr_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
  CONSTRAINT fk_cr_account FOREIGN KEY (account_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
