-- Accounting Phase 2 — AR/AP Schema
-- Run AFTER 001_phase1.sql
-- Compatible: MariaDB 10.6+

SET FOREIGN_KEY_CHECKS = 0;

-- ─── 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;

-- ─── 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,
  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,
  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),
  CONSTRAINT fk_il_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) 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;

-- ─── 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;

-- ─── 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;

SET FOREIGN_KEY_CHECKS = 1;
