-- Run once: create database if needed (executed against master)
-- The Go migration runner handles this separately.

-- ── Work Orders ───────────────────────────────────────────────────────────────
CREATE TABLE work_orders (
    id              INT IDENTITY PRIMARY KEY,
    wo_number       AS ('WO-' + RIGHT('000000' + CAST(id AS VARCHAR), 6)) PERSISTED,
    title           NVARCHAR(200)  NOT NULL,
    description     NVARCHAR(MAX),
    instructions    NVARCHAR(MAX),
    status          NVARCHAR(30)   NOT NULL DEFAULT 'draft',
    priority        NVARCHAR(10)   NOT NULL DEFAULT 'normal',
    scheduled_start DATETIME2,
    scheduled_end   DATETIME2,
    actual_start    DATETIME2,
    actual_end      DATETIME2,
    site_name       NVARCHAR(200),
    address         NVARCHAR(400),
    lat             DECIMAL(10,7),
    lng             DECIMAL(10,7),
    access_notes    NVARCHAR(MAX),
    parent_type     NVARCHAR(50),
    parent_id       INT,
    created_by      NVARCHAR(200)  NOT NULL DEFAULT 'system',
    created_at      DATETIME2      NOT NULL DEFAULT GETUTCDATE(),
    updated_at      DATETIME2      NOT NULL DEFAULT GETUTCDATE(),
    closed_at       DATETIME2,
    closed_by       NVARCHAR(200),
    CONSTRAINT chk_wo_status   CHECK (status   IN ('draft','assigned','scheduled','in_progress','pending_review','closed')),
    CONSTRAINT chk_wo_priority CHECK (priority IN ('low','normal','high','urgent'))
);

-- ── Resource Registry ─────────────────────────────────────────────────────────
CREATE TABLE resource_people (
    id    INT IDENTITY PRIMARY KEY,
    name  NVARCHAR(100) NOT NULL,
    role  NVARCHAR(100),
    email NVARCHAR(200),
    phone NVARCHAR(30),
    active BIT NOT NULL DEFAULT 1
);

CREATE TABLE resource_vehicles (
    id           INT IDENTITY PRIMARY KEY,
    unit_number  NVARCHAR(50)  NOT NULL,
    description  NVARCHAR(200),
    vehicle_type NVARCHAR(100),
    active       BIT NOT NULL DEFAULT 1
);

CREATE TABLE resource_equipment (
    id        INT IDENTITY PRIMARY KEY,
    name      NVARCHAR(200) NOT NULL,
    asset_tag NVARCHAR(100),
    category  NVARCHAR(100),
    active    BIT NOT NULL DEFAULT 1
);

CREATE TABLE resource_materials (
    id          INT IDENTITY PRIMARY KEY,
    name        NVARCHAR(200) NOT NULL,
    unit        NVARCHAR(30),
    part_number NVARCHAR(100),
    active      BIT NOT NULL DEFAULT 1
);

-- ── Work Order Resources ───────────────────────────────────────────────────────
CREATE TABLE wo_resources (
    id            INT IDENTITY PRIMARY KEY,
    wo_id         INT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    resource_type NVARCHAR(20) NOT NULL,
    resource_id   INT NOT NULL,
    quantity      DECIMAL(10,2),
    notes         NVARCHAR(500),
    assigned_at   DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    CONSTRAINT chk_resource_type CHECK (resource_type IN ('person','vehicle','equipment','material'))
);

-- ── Checklist Steps ───────────────────────────────────────────────────────────
CREATE TABLE wo_steps (
    id           INT IDENTITY PRIMARY KEY,
    wo_id        INT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    step_order   INT NOT NULL,
    title        NVARCHAR(200) NOT NULL,
    description  NVARCHAR(MAX),
    required     BIT NOT NULL DEFAULT 1,
    completed    BIT NOT NULL DEFAULT 0,
    completed_by NVARCHAR(200),
    completed_at DATETIME2,
    notes        NVARCHAR(MAX)
);

-- ── Attachments ───────────────────────────────────────────────────────────────
CREATE TABLE wo_attachments (
    id          INT IDENTITY PRIMARY KEY,
    wo_id       INT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    step_id     INT REFERENCES wo_steps(id),
    file_name   NVARCHAR(500) NOT NULL,
    file_path   NVARCHAR(1000) NOT NULL,
    file_type   NVARCHAR(100),
    file_size   BIGINT,
    caption     NVARCHAR(500),
    phase       NVARCHAR(20),
    lat         DECIMAL(10,7),
    lng         DECIMAL(10,7),
    uploaded_by NVARCHAR(200) NOT NULL,
    uploaded_at DATETIME2 NOT NULL DEFAULT GETUTCDATE()
);

-- ── Accounting Codes ──────────────────────────────────────────────────────────
CREATE TABLE wo_accounting (
    id          INT IDENTITY PRIMARY KEY,
    wo_id       INT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    code_type   NVARCHAR(50)  NOT NULL,
    code_value  NVARCHAR(200) NOT NULL,
    description NVARCHAR(500),
    CONSTRAINT uq_wo_accounting UNIQUE (wo_id, code_type)
);

-- ── Audit Log ─────────────────────────────────────────────────────────────────
CREATE TABLE wo_audit_log (
    id           INT IDENTITY PRIMARY KEY,
    wo_id        INT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    action       NVARCHAR(100) NOT NULL,
    old_value    NVARCHAR(MAX),
    new_value    NVARCHAR(MAX),
    performed_by NVARCHAR(200) NOT NULL,
    performed_at DATETIME2 NOT NULL DEFAULT GETUTCDATE()
);

-- ── Indexes ───────────────────────────────────────────────────────────────────
CREATE INDEX ix_wo_status    ON work_orders (status);
CREATE INDEX ix_wo_parent    ON work_orders (parent_type, parent_id);
CREATE INDEX ix_wo_scheduled ON work_orders (scheduled_start);
CREATE INDEX ix_resources    ON wo_resources (wo_id, resource_type);
CREATE INDEX ix_attachments  ON wo_attachments (wo_id);

-- ── Seed registry data ────────────────────────────────────────────────────────
INSERT INTO resource_people (name, role, email, phone) VALUES
    ('Alice Johnson', 'Lead Technician', 'alice@example.com', '555-0101'),
    ('Bob Smith',     'Field Tech',      'bob@example.com',   '555-0102'),
    ('Carol White',   'Supervisor',      'carol@example.com', '555-0103');

INSERT INTO resource_vehicles (unit_number, description, vehicle_type) VALUES
    ('TRK-001', 'Ford F-250 Service Truck', 'Pickup'),
    ('VAN-002', 'Transit Connect Work Van',  'Van'),
    ('SUV-003', 'Chevy Tahoe Supervisor',    'SUV');

INSERT INTO resource_equipment (name, asset_tag, category) VALUES
    ('Bucket Truck',       'EQ-1001', 'Heavy Equipment'),
    ('Portable Generator', 'EQ-1002', 'Power'),
    ('Cable Puller',       'EQ-1003', 'Tools');

INSERT INTO resource_materials (name, unit, part_number) VALUES
    ('Wire 12 AWG',  'ft',   'WR-12AWG'),
    ('Conduit 1"',   'ft',   'CD-1IN'),
    ('Junction Box', 'each', 'JB-100');