-- ─────────────────────────────────────────────────────────────────────────────
-- Accounting — Complete Schema (Fresh Install)
-- Compatible: MariaDB 10.6+
--
-- Usage:
--   mysql -u <user> -p <database> < schema.sql
--
-- Tables are ordered by foreign-key dependency so this file can be run
-- top-to-bottom on an empty database without errors.
-- ─────────────────────────────────────────────────────────────────────────────

SET FOREIGN_KEY_CHECKS = 0;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

-- ─── Tenants ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tenants (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  plan       ENUM('starter','professional','enterprise') DEFAULT 'starter',
  created_at DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Users ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(255) NOT NULL,
  email         VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','accountant') DEFAULT 'accountant',
  is_active     TINYINT(1)   DEFAULT 1,
  totp_secret   VARCHAR(64)  NULL,
  totp_enabled  TINYINT(1)   DEFAULT 0,
  created_at    DATETIME     DEFAULT NOW(),
  updated_at    DATETIME     ON UPDATE NOW(),
  UNIQUE KEY uq_email (email),
  INDEX idx_tenant (tenant_id),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── API Keys ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS api_keys (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(255) NOT NULL,
  key_prefix    VARCHAR(32)  NOT NULL,
  key_hash      CHAR(64)     NOT NULL,
  scopes        JSON         NOT NULL,
  created_by_id INT UNSIGNED NOT NULL,
  expires_at    DATETIME     DEFAULT NULL,
  last_used_at  DATETIME     DEFAULT NULL,
  is_active     TINYINT(1)   DEFAULT 1,
  created_at    DATETIME     DEFAULT NOW(),
  UNIQUE KEY uq_api_keys_hash (key_hash),
  INDEX idx_api_keys_tenant (tenant_id),
  INDEX idx_api_keys_active (tenant_id, is_active),
  CONSTRAINT fk_api_keys_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_api_keys_user   FOREIGN KEY (created_by_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Chart of Accounts ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS accounts (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  code       VARCHAR(20)  NOT NULL,
  name       VARCHAR(255) NOT NULL,
  type       ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  parent_id  INT UNSIGNED DEFAULT NULL,
  is_deleted TINYINT(1)   DEFAULT 0,
  created_at DATETIME     DEFAULT NOW(),
  updated_at DATETIME     ON UPDATE NOW(),
  UNIQUE KEY uq_code_per_tenant (tenant_id, code),
  INDEX idx_tenant (tenant_id),
  INDEX idx_parent (parent_id),
  INDEX idx_type   (tenant_id, type),
  CONSTRAINT fk_accounts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_accounts_parent FOREIGN KEY (parent_id) REFERENCES accounts(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Journal Entries ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS journal_entries (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  date       DATE         NOT NULL,
  memo       TEXT         DEFAULT NULL,
  created_by INT UNSIGNED NOT NULL,
  is_deleted TINYINT(1)   DEFAULT 0,
  created_at DATETIME     DEFAULT NOW(),
  updated_at DATETIME     ON UPDATE NOW(),
  INDEX idx_tenant_date (tenant_id, date),
  INDEX idx_tenant      (tenant_id),
  CONSTRAINT fk_je_tenant FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_je_user   FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Journal Lines ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS journal_lines (
  id          INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  entry_id    INT UNSIGNED  NOT NULL,
  account_id  INT UNSIGNED  NOT NULL,
  debit       DECIMAL(15,2) DEFAULT 0.00,
  credit      DECIMAL(15,2) DEFAULT 0.00,
  description VARCHAR(500)  DEFAULT NULL,
  created_at  DATETIME      DEFAULT NOW(),
  INDEX idx_entry   (entry_id),
  INDEX idx_account (account_id),
  CONSTRAINT fk_jl_entry   FOREIGN KEY (entry_id)   REFERENCES journal_entries(id) ON DELETE CASCADE,
  CONSTRAINT fk_jl_account FOREIGN KEY (account_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Customers ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS customers (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  name       VARCHAR(255) NOT NULL,
  email      VARCHAR(255) DEFAULT NULL,
  phone      VARCHAR(50)  DEFAULT NULL,
  address    TEXT         DEFAULT NULL,
  is_deleted TINYINT(1)   DEFAULT 0,
  created_at DATETIME     DEFAULT NOW(),
  updated_at DATETIME     ON UPDATE NOW(),
  INDEX idx_tenant (tenant_id),
  CONSTRAINT fk_cust_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Vendors ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS vendors (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  name       VARCHAR(255) NOT NULL,
  email      VARCHAR(255) DEFAULT NULL,
  phone      VARCHAR(50)  DEFAULT NULL,
  address    TEXT         DEFAULT NULL,
  is_deleted TINYINT(1)   DEFAULT 0,
  created_at DATETIME     DEFAULT NOW(),
  updated_at DATETIME     ON UPDATE NOW(),
  INDEX idx_tenant (tenant_id),
  CONSTRAINT fk_vend_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Products & Inventory ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
  id                   INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id            INT UNSIGNED  NOT NULL,
  name                 VARCHAR(255)  NOT NULL,
  sku                  VARCHAR(100)  DEFAULT NULL,
  description          TEXT          DEFAULT NULL,
  unit_price           DECIMAL(15,2) DEFAULT 0.00,
  unit_cost            DECIMAL(15,2) DEFAULT 0.00,
  quantity_on_hand     DECIMAL(15,4) DEFAULT 0.0000,
  reorder_point        DECIMAL(15,4) DEFAULT 0.0000,
  inventory_account_id INT UNSIGNED  DEFAULT NULL,
  cogs_account_id      INT UNSIGNED  DEFAULT NULL,
  revenue_account_id   INT UNSIGNED  DEFAULT NULL,
  is_deleted           TINYINT(1)    DEFAULT 0,
  created_at           DATETIME      DEFAULT NOW(),
  updated_at           DATETIME      ON UPDATE NOW(),
  INDEX idx_tenant (tenant_id),
  INDEX idx_sku    (tenant_id, sku),
  CONSTRAINT fk_prod_tenant    FOREIGN KEY (tenant_id)            REFERENCES tenants(id)  ON DELETE CASCADE,
  CONSTRAINT fk_prod_inv_acct  FOREIGN KEY (inventory_account_id) REFERENCES accounts(id),
  CONSTRAINT fk_prod_cogs_acct FOREIGN KEY (cogs_account_id)      REFERENCES accounts(id),
  CONSTRAINT fk_prod_rev_acct  FOREIGN KEY (revenue_account_id)   REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Inventory Movements ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS inventory_movements (
  id               INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id        INT UNSIGNED  NOT NULL,
  product_id       INT UNSIGNED  NOT NULL,
  date             DATE          NOT NULL,
  quantity         DECIMAL(15,4) NOT NULL,   -- positive = stock in, negative = stock out
  unit_cost        DECIMAL(15,2) DEFAULT 0.00,
  reference_type   ENUM('sale','purchase','adjustment') NOT NULL,
  reference_id     INT UNSIGNED  DEFAULT NULL,
  notes            TEXT          DEFAULT NULL,
  journal_entry_id INT UNSIGNED  DEFAULT NULL,
  created_at       DATETIME      DEFAULT NOW(),
  INDEX idx_tenant  (tenant_id),
  INDEX idx_product (product_id),
  CONSTRAINT fk_im_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
  CONSTRAINT fk_im_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Invoices ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invoices (
  id                 INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id          INT UNSIGNED  NOT NULL,
  customer_id        INT UNSIGNED  NOT NULL,
  invoice_number     VARCHAR(50)   NOT NULL,
  issue_date         DATE          NOT NULL,
  due_date           DATE          NOT NULL,
  status             ENUM('draft','sent','partial','paid','overdue') DEFAULT 'draft',
  subtotal           DECIMAL(15,2) DEFAULT 0.00,
  tax                DECIMAL(15,2) DEFAULT 0.00,
  total              DECIMAL(15,2) DEFAULT 0.00,
  notes              TEXT          DEFAULT NULL,
  revenue_account_id INT UNSIGNED  DEFAULT NULL,
  journal_entry_id   INT UNSIGNED  DEFAULT NULL,
  emailed_at         DATETIME      DEFAULT NULL,
  is_deleted         TINYINT(1)    DEFAULT 0,
  created_at         DATETIME      DEFAULT NOW(),
  updated_at         DATETIME      ON UPDATE NOW(),
  UNIQUE KEY uq_invoice_number (tenant_id, invoice_number),
  INDEX idx_tenant   (tenant_id),
  INDEX idx_customer (customer_id),
  INDEX idx_status   (tenant_id, status),
  CONSTRAINT fk_inv_tenant   FOREIGN KEY (tenant_id)          REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_inv_customer FOREIGN KEY (customer_id)        REFERENCES customers(id),
  CONSTRAINT fk_inv_rev_acct FOREIGN KEY (revenue_account_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Invoice Lines ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invoice_lines (
  id          INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT UNSIGNED  NOT NULL,
  product_id  INT UNSIGNED  DEFAULT NULL,
  description VARCHAR(500)  NOT NULL,
  quantity    DECIMAL(15,4) DEFAULT 1.0000,
  unit_price  DECIMAL(15,2) NOT NULL,
  amount      DECIMAL(15,2) NOT NULL,
  created_at  DATETIME      DEFAULT NOW(),
  INDEX idx_invoice (invoice_id),
  INDEX idx_product (product_id),
  CONSTRAINT fk_il_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id)  ON DELETE CASCADE,
  CONSTRAINT fk_il_product FOREIGN KEY (product_id) REFERENCES products(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Invoice Payments ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invoice_payments (
  id               INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  invoice_id       INT UNSIGNED  NOT NULL,
  tenant_id        INT UNSIGNED  NOT NULL,
  amount           DECIMAL(15,2) NOT NULL,
  payment_date     DATE          NOT NULL,
  method           ENUM('cash','check','bank_transfer','credit_card','other') DEFAULT 'bank_transfer',
  notes            TEXT          DEFAULT NULL,
  journal_entry_id INT UNSIGNED  DEFAULT NULL,
  created_at       DATETIME      DEFAULT NOW(),
  INDEX idx_invoice (invoice_id),
  INDEX idx_tenant  (tenant_id),
  CONSTRAINT fk_ip_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_ip_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Recurring Invoices ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS recurring_invoices (
  id                 INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id          INT UNSIGNED  NOT NULL,
  customer_id        INT UNSIGNED  NOT NULL,
  revenue_account_id INT UNSIGNED  NOT NULL,
  created_by_id      INT UNSIGNED  NOT NULL,
  title              VARCHAR(255)  NOT NULL,
  frequency          ENUM('weekly','monthly','quarterly','annually') NOT NULL,
  next_date          DATE          NOT NULL,
  end_date           DATE          DEFAULT NULL,
  tax                DECIMAL(15,2) DEFAULT 0.00,
  notes              TEXT          DEFAULT NULL,
  `lines`            JSON          NOT NULL,
  is_active          TINYINT(1)    DEFAULT 1,
  last_run_at        DATETIME      DEFAULT NULL,
  created_at         DATETIME      DEFAULT NOW(),
  INDEX idx_tenant    (tenant_id),
  INDEX idx_next_date (tenant_id, next_date),
  CONSTRAINT fk_ri_tenant   FOREIGN KEY (tenant_id)          REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_ri_customer FOREIGN KEY (customer_id)        REFERENCES customers(id),
  CONSTRAINT fk_ri_rev_acct FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  CONSTRAINT fk_ri_user     FOREIGN KEY (created_by_id)      REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Bills ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bills (
  id                 INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id          INT UNSIGNED  NOT NULL,
  vendor_id          INT UNSIGNED  NOT NULL,
  bill_number        VARCHAR(50)   DEFAULT NULL,
  bill_date          DATE          NOT NULL,
  due_date           DATE          NOT NULL,
  status             ENUM('draft','received','partial','paid') DEFAULT 'draft',
  subtotal           DECIMAL(15,2) DEFAULT 0.00,
  total              DECIMAL(15,2) DEFAULT 0.00,
  notes              TEXT          DEFAULT NULL,
  expense_account_id INT UNSIGNED  DEFAULT NULL,
  journal_entry_id   INT UNSIGNED  DEFAULT NULL,
  is_deleted         TINYINT(1)    DEFAULT 0,
  created_at         DATETIME      DEFAULT NOW(),
  updated_at         DATETIME      ON UPDATE NOW(),
  INDEX idx_tenant (tenant_id),
  INDEX idx_vendor (vendor_id),
  INDEX idx_status (tenant_id, status),
  CONSTRAINT fk_bill_tenant   FOREIGN KEY (tenant_id)          REFERENCES tenants(id)  ON DELETE CASCADE,
  CONSTRAINT fk_bill_vendor   FOREIGN KEY (vendor_id)          REFERENCES vendors(id),
  CONSTRAINT fk_bill_exp_acct FOREIGN KEY (expense_account_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Bill Lines ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bill_lines (
  id          INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  bill_id     INT UNSIGNED  NOT NULL,
  description VARCHAR(500)  NOT NULL,
  quantity    DECIMAL(15,4) DEFAULT 1.0000,
  unit_price  DECIMAL(15,2) NOT NULL,
  amount      DECIMAL(15,2) NOT NULL,
  created_at  DATETIME      DEFAULT NOW(),
  INDEX idx_bill (bill_id),
  CONSTRAINT fk_bl_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Bill Payments ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bill_payments (
  id               INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  bill_id          INT UNSIGNED  NOT NULL,
  tenant_id        INT UNSIGNED  NOT NULL,
  amount           DECIMAL(15,2) NOT NULL,
  payment_date     DATE          NOT NULL,
  method           ENUM('cash','check','bank_transfer','credit_card','other') DEFAULT 'bank_transfer',
  notes            TEXT          DEFAULT NULL,
  journal_entry_id INT UNSIGNED  DEFAULT NULL,
  created_at       DATETIME      DEFAULT NOW(),
  INDEX idx_bill   (bill_id),
  INDEX idx_tenant (tenant_id),
  CONSTRAINT fk_bp_bill   FOREIGN KEY (bill_id)   REFERENCES bills(id)   ON DELETE CASCADE,
  CONSTRAINT fk_bp_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 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/checking 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
  matched_entry_id INT UNSIGNED  DEFAULT NULL,
  matched_type     ENUM('journal_entry','invoice_payment','bill_payment') DEFAULT NULL,
  matched_id       INT UNSIGNED  DEFAULT NULL,
  status           ENUM('unmatched','matched','excluded','reconciled') DEFAULT 'unmatched',
  is_cleared       TINYINT(1)    DEFAULT 0,
  import_batch     VARCHAR(36)   DEFAULT NULL,
  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,
  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;

-- ─── Audit Log ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id    INT UNSIGNED    NOT NULL,
  user_id      INT UNSIGNED    DEFAULT NULL,
  user_name    VARCHAR(255)    DEFAULT NULL,
  action       VARCHAR(64)     NOT NULL,
  entity_type  VARCHAR(64)     NOT NULL,
  entity_id    INT UNSIGNED    DEFAULT NULL,
  entity_label VARCHAR(255)    DEFAULT NULL,
  changes      JSON            DEFAULT NULL,
  ip           VARCHAR(45)     DEFAULT NULL,
  created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_tenant (tenant_id, created_at),
  INDEX idx_audit_entity (tenant_id, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Attachments ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS attachments (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  user_id       INT UNSIGNED DEFAULT NULL,
  entity_type   VARCHAR(64)  NOT NULL,
  entity_id     INT UNSIGNED NOT NULL,
  filename      VARCHAR(255) NOT NULL,   -- name on disk (randomised)
  original_name VARCHAR(255) NOT NULL,   -- name shown to user
  mime_type     VARCHAR(128) DEFAULT NULL,
  file_size     INT UNSIGNED DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_attach_entity (tenant_id, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
