-- Migration 012: Invoicing & Payments Module
-- Tables: tax_rates, invoice_number_sequences, invoices, invoice_items, payments
-- Alters: customers (add tax_exempt, lifetime_spend, total_invoices)

-- ── Tax Rates ──
CREATE TABLE tax_rates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    rate DECIMAL(6,4) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_tax_rates_tenant ON tax_rates(tenant_id);
CREATE TRIGGER trg_tax_rates_updated_at BEFORE UPDATE ON tax_rates
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── Invoice Number Sequences ──
CREATE TABLE invoice_number_sequences (
    tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    prefix VARCHAR(20) DEFAULT 'INV',
    next_number INT DEFAULT 1,
    estimate_prefix VARCHAR(20) DEFAULT 'EST',
    estimate_next_number INT DEFAULT 1
);

-- ── Invoices ──
CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    location_id UUID REFERENCES locations(id) ON DELETE SET NULL,
    customer_id UUID REFERENCES customers(id) ON DELETE SET NULL,
    vehicle_id UUID REFERENCES customer_vehicles(id) ON DELETE SET NULL,
    work_order_id UUID,

    invoice_number VARCHAR(50) NOT NULL,
    type VARCHAR(20) NOT NULL DEFAULT 'invoice'
        CHECK (type IN ('invoice', 'estimate', 'work_order')),
    status VARCHAR(20) NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft', 'sent', 'partial', 'paid', 'overdue', 'void', 'refunded')),

    subtotal DECIMAL(12,2) DEFAULT 0.00,
    discount_total DECIMAL(12,2) DEFAULT 0.00,
    tax_total DECIMAL(12,2) DEFAULT 0.00,
    grand_total DECIMAL(12,2) DEFAULT 0.00,
    amount_paid DECIMAL(12,2) DEFAULT 0.00,
    balance_due DECIMAL(12,2) DEFAULT 0.00,

    tax_rate_id UUID REFERENCES tax_rates(id) ON DELETE SET NULL,
    tax_exempt BOOLEAN DEFAULT FALSE,

    expires_at DATE,

    payment_plan_type VARCHAR(20) DEFAULT 'standard'
        CHECK (payment_plan_type IN ('standard', 'layaway', 'financing')),
    financing_provider VARCHAR(255),
    financing_account_number VARCHAR(100),

    notes TEXT,
    internal_notes TEXT,

    created_by UUID NOT NULL REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    sent_at TIMESTAMPTZ,
    paid_at TIMESTAMPTZ,
    voided_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, invoice_number)
);
CREATE INDEX idx_invoices_tenant ON invoices(tenant_id);
CREATE INDEX idx_invoices_customer ON invoices(customer_id);
CREATE INDEX idx_invoices_status ON invoices(tenant_id, status);
CREATE INDEX idx_invoices_type ON invoices(tenant_id, type);
CREATE INDEX idx_invoices_date ON invoices(created_at DESC);
CREATE TRIGGER trg_invoices_updated_at BEFORE UPDATE ON invoices
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── Invoice Items ──
CREATE TABLE invoice_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    sort_order INT DEFAULT 0,

    item_type VARCHAR(20) NOT NULL
        CHECK (item_type IN ('product', 'labor', 'misc', 'discount')),

    product_id UUID REFERENCES products(id) ON DELETE SET NULL,

    description VARCHAR(500) NOT NULL,
    notes TEXT,

    quantity DECIMAL(10,3) DEFAULT 1.000,
    unit_price DECIMAL(12,2) DEFAULT 0.00,
    line_total DECIMAL(12,2) DEFAULT 0.00,

    discount_type VARCHAR(10)
        CHECK (discount_type IN ('fixed', 'percent')),
    discount_value DECIMAL(12,2),

    misc_mode VARCHAR(20)
        CHECK (misc_mode IN ('fixed', 'percent', 'labor_surcharge')),
    misc_value DECIMAL(12,2),
    misc_parent_id UUID REFERENCES invoice_items(id) ON DELETE SET NULL,

    is_taxable BOOLEAN DEFAULT TRUE,
    tax_amount DECIMAL(12,2) DEFAULT 0.00,

    stock_deducted BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_invoice_items_invoice ON invoice_items(invoice_id);
CREATE INDEX idx_invoice_items_product ON invoice_items(product_id);

-- ── Payments ──
CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,

    amount DECIMAL(12,2) NOT NULL,
    method VARCHAR(30) NOT NULL
        CHECK (method IN ('cash', 'credit_card', 'check', 'financing', 'layaway', 'store_credit')),
    reference_number VARCHAR(100),
    notes TEXT,

    received_by UUID NOT NULL REFERENCES users(id),
    payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    voided BOOLEAN DEFAULT FALSE,
    voided_at TIMESTAMPTZ,
    voided_by UUID REFERENCES users(id),

    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_payments_tenant ON payments(tenant_id);
CREATE INDEX idx_payments_invoice ON payments(invoice_id);
CREATE INDEX idx_payments_date ON payments(payment_date DESC);
CREATE INDEX idx_payments_method ON payments(tenant_id, method);

-- ── Customer Alterations ──
ALTER TABLE customers ADD COLUMN IF NOT EXISTS tax_exempt BOOLEAN DEFAULT FALSE;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS lifetime_spend DECIMAL(12,2) DEFAULT 0.00;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS total_invoices INT DEFAULT 0;

-- Seed data handled by provisioning service
