-- Migration 0006: create_billing
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 5 — BILLING
-- =====================================================================

CREATE TABLE IF NOT EXISTS invoices (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id    BIGINT UNSIGNED NULL,
    company_id     BIGINT UNSIGNED NULL,
    invoice_number VARCHAR(40)     NOT NULL,
    status         ENUM('draft','sent','paid','overdue','void') NOT NULL DEFAULT 'draft',
    subtotal       DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    tax_total      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    total          DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency       CHAR(3)         NOT NULL DEFAULT 'CAD',
    issue_date     DATE            NOT NULL,
    due_date       DATE            NULL,
    paid_at        DATETIME        NULL,
    created_by     BIGINT UNSIGNED NULL,
    updated_by     BIGINT UNSIGNED NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at     DATETIME        NULL,
    UNIQUE KEY uk_invoices_number (invoice_number),
    KEY idx_invoices_customer (customer_id),
    KEY idx_invoices_company (company_id),
    KEY idx_invoices_status (status),
    CONSTRAINT fk_invoices_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_invoices_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_invoices_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_invoices_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
    -- NOTE: "must have a customer_id OR a company_id" is intentionally NOT a
    -- CHECK constraint here. MySQL forbids a column from being both part of
    -- a CHECK constraint and the child side of a FK with a CASCADE/SET NULL
    -- referential action (error 3823) — and both FKs above need SET NULL
    -- behavior elsewhere in the app. This rule is enforced in
    -- app/models/InvoiceModel.php validation instead.
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id  BIGINT UNSIGNED NOT NULL,
    description VARCHAR(255)    NOT NULL,
    quantity    DECIMAL(10,2)   NOT NULL DEFAULT 1.00,
    unit_price  DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    line_total  DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    KEY idx_invoice_items_invoice (invoice_id),
    CONSTRAINT fk_invoice_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id    BIGINT UNSIGNED NOT NULL,
    amount        DECIMAL(12,2)   NOT NULL,
    currency      CHAR(3)         NOT NULL DEFAULT 'CAD',
    method        ENUM('card','bank_transfer','cash','cheque','other') NOT NULL DEFAULT 'card',
    reference     VARCHAR(120)    NULL COMMENT 'Processor transaction id',
    paid_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    recorded_by   BIGINT UNSIGNED NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_payments_invoice (invoice_id),
    CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_payments_recorded_by FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The Hub's OWN subscription to A&B (Settings > Billing tab) — not to
-- be confused with `invoices`, which bills A&B's customers.
CREATE TABLE IF NOT EXISTS billing_accounts (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_name          VARCHAR(80)     NOT NULL DEFAULT 'Enterprise',
    monthly_amount     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency           CHAR(3)         NOT NULL DEFAULT 'CAD',
    billing_cycle      ENUM('monthly','annually') NOT NULL DEFAULT 'annually',
    card_last_four     CHAR(4)         NULL,
    card_brand         VARCHAR(30)     NULL,
    renews_on          DATE            NULL,
    updated_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
