-- Migration 014: Work Orders Module
-- Tables: work_orders, work_order_checklists, work_order_photos
-- Alters: invoice_number_sequences (WO prefix/sequence)

-- ── Work Orders ──
CREATE TABLE work_orders (
    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,
    invoice_id UUID UNIQUE REFERENCES invoices(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_number VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'check_in'
        CHECK (status IN ('check_in', 'in_progress', 'qc', 'closed')),

    bay_number VARCHAR(20),
    technician_id UUID REFERENCES users(id) ON DELETE SET NULL,

    mileage_in INT,
    mileage_out INT,

    notes TEXT,
    internal_notes TEXT,

    checked_in_by UUID REFERENCES users(id) ON DELETE SET NULL,
    checked_in_at TIMESTAMPTZ,
    qc_by UUID REFERENCES users(id) ON DELETE SET NULL,
    qc_at TIMESTAMPTZ,
    closed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    closed_at TIMESTAMPTZ,

    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, work_order_number)
);
CREATE INDEX idx_work_orders_tenant ON work_orders(tenant_id);
CREATE INDEX idx_work_orders_invoice ON work_orders(invoice_id);
CREATE INDEX idx_work_orders_customer ON work_orders(customer_id);
CREATE INDEX idx_work_orders_status ON work_orders(tenant_id, status);
CREATE INDEX idx_work_orders_technician ON work_orders(technician_id);
CREATE INDEX idx_work_orders_date ON work_orders(created_at DESC);
CREATE TRIGGER trg_work_orders_updated_at BEFORE UPDATE ON work_orders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── Work Order Checklists ──
CREATE TABLE work_order_checklists (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    work_order_id UUID NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    stage VARCHAR(20) NOT NULL CHECK (stage IN ('check_in', 'check_out')),
    item_name VARCHAR(200) NOT NULL,
    status VARCHAR(10) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'pass', 'fail', 'na')),
    notes TEXT,
    sort_order INT DEFAULT 0,
    checked_by UUID REFERENCES users(id) ON DELETE SET NULL,
    checked_at TIMESTAMPTZ
);
CREATE INDEX idx_wo_checklists_wo ON work_order_checklists(work_order_id);

-- ── Work Order Photos ──
CREATE TABLE work_order_photos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    work_order_id UUID NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    stage VARCHAR(20) NOT NULL CHECK (stage IN ('check_in', 'check_out')),
    file_path VARCHAR(500) NOT NULL,
    caption VARCHAR(255),
    uploaded_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_wo_photos_wo ON work_order_photos(work_order_id);

-- ── Default Checklist Templates (stored as a reference table) ──
CREATE TABLE work_order_checklist_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    stage VARCHAR(20) NOT NULL CHECK (stage IN ('check_in', 'check_out')),
    item_name VARCHAR(200) NOT NULL,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
);
CREATE INDEX idx_wo_checklist_tpl_tenant ON work_order_checklist_templates(tenant_id);

-- Checklist template seed data handled by provisioning service

-- ── Add WO sequence columns to invoice_number_sequences ──
ALTER TABLE invoice_number_sequences ADD COLUMN IF NOT EXISTS wo_prefix VARCHAR(20) DEFAULT 'WO';
ALTER TABLE invoice_number_sequences ADD COLUMN IF NOT EXISTS wo_next_number INT DEFAULT 1;

-- ── Add FK constraint on invoices.work_order_id ──
-- (column already exists from migration 012, just needs the FK now that the table exists)
-- Note: Can't add FK directly since work_order_id may have stale data. Only add if clean.
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'invoices_work_order_id_fkey') THEN
        ALTER TABLE invoices ADD CONSTRAINT invoices_work_order_id_fkey
            FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE SET NULL;
    END IF;
END $$;

-- Migration record handled by migration runner
