-- Slice 8b-T · Work Templates foundation
-- One template engine that Services PM plans, Works inspections, and Goods GRN checklists all consume.
-- Mobile-ready from day 1: sha256 dedup, GPS, device_captured_at, time_source enum, version stamp on instance.

CREATE TABLE IF NOT EXISTS work_templates (
    c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code              VARCHAR(60) NOT NULL,
    title             VARCHAR(200) NOT NULL,
    description       TEXT NULL,
    -- Scope categorises the template so consuming modules can filter their pickers.
    scope             ENUM('PM','INSPECTION','HANDOVER','RECEIVING','SAFETY','CHECKLIST') NOT NULL DEFAULT 'CHECKLIST',
    -- Which entity type an instance of this template targets. Consumer modules use this to
    -- constrain the target picker (e.g. Services picks templates whose target = asset).
    target_entity_type VARCHAR(40) NOT NULL DEFAULT 'c_projects',
    -- Cadence is only meaningful for scope=PM (Services). Free text so we can extend later.
    cadence           VARCHAR(20) NOT NULL DEFAULT 'ON_DEMAND',
    version           INT NOT NULL DEFAULT 1,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    expected_minutes  INT NOT NULL DEFAULT 0,
    reviewer_role     VARCHAR(60) NOT NULL DEFAULT '',
    -- Version fork trail: when items change post-deployment, we create a new row (new version)
    -- and point supersedes_id at the previous. In-flight instances keep pointing at the old row.
    supersedes_id     INT NOT NULL DEFAULT 0,
    created_by        INT NOT NULL DEFAULT 0,
    created_by_name   VARCHAR(120) NOT NULL DEFAULT '',
    created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by         INT NOT NULL DEFAULT 0,
    edited_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted           TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_code_ver (code, version),
    KEY ix_scope_target (scope, target_entity_type, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS work_template_items (
    c0d                 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    template_id         INT NOT NULL,
    section             VARCHAR(80) NOT NULL DEFAULT '',
    sort_order          INT NOT NULL DEFAULT 10,
    prompt              VARCHAR(500) NOT NULL,
    -- Input types drive form widgets and validation on both web + future mobile.
    input_type          ENUM('CHECK','NUMBER','TEXT','LONGTEXT','SELECT','DATE','PHOTO','SIGNATURE') NOT NULL DEFAULT 'CHECK',
    is_required         TINYINT(1) NOT NULL DEFAULT 0,
    -- Options for SELECT type (JSON array of {value,label}).
    options_json        TEXT NULL,
    -- Conditional visibility: hide unless item X answered with Y.
    depends_on_item_id  INT NOT NULL DEFAULT 0,
    depends_on_value    VARCHAR(200) NOT NULL DEFAULT '',
    help_text           VARCHAR(500) NOT NULL DEFAULT '',
    numeric_min         DECIMAL(20,4) NULL,
    numeric_max         DECIMAL(20,4) NULL,
    numeric_unit        VARCHAR(20) NOT NULL DEFAULT '',
    created_on          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_on           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted             TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_template (template_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS work_instances (
    c0d                 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    template_id         INT NOT NULL,
    template_version    INT NOT NULL,
    target_entity_type  VARCHAR(40) NOT NULL,
    target_entity_id    INT NOT NULL,
    -- Human-friendly reference stamped at creation (e.g. "PM · Gen 5kVA @ Site 03").
    ref_label           VARCHAR(200) NOT NULL DEFAULT '',
    scheduled_for       DATE NULL,
    -- Assignee is nullable — an unclaimed instance is visible to the whole pool at a site until
    -- one FT taps "claim". Mirrors the sitetracker16 pool pattern.
    assignee_id         INT NOT NULL DEFAULT 0,
    assignee_name       VARCHAR(120) NOT NULL DEFAULT '',
    claimed_at          TIMESTAMP NULL,
    status              ENUM('DRAFT','SUBMITTED','APPROVED','REJECTED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    started_at          TIMESTAMP NULL,
    submitted_at        TIMESTAMP NULL,
    reviewed_at         TIMESTAMP NULL,
    reviewed_by         INT NOT NULL DEFAULT 0,
    reviewed_by_name    VARCHAR(120) NOT NULL DEFAULT '',
    review_notes        VARCHAR(500) NOT NULL DEFAULT '',
    -- GPS captured when the assignee starts (mobile hydrates these from device sensors).
    start_gps_lat       DECIMAL(11,7) NULL,
    start_gps_lng       DECIMAL(11,7) NULL,
    finish_gps_lat      DECIMAL(11,7) NULL,
    finish_gps_lng      DECIMAL(11,7) NULL,
    -- Provenance for offline-first mobile: device clock vs server clock reconciliation.
    time_source         ENUM('SERVER','DEVICE','GPS') NOT NULL DEFAULT 'SERVER',
    time_trust          ENUM('TRUSTED','INFERRED') NOT NULL DEFAULT 'TRUSTED',
    -- Free JSON for scope-specific instance context (asset serial, phase ref, LPO no. etc).
    context_json        LONGTEXT NULL,
    created_by          INT NOT NULL DEFAULT 0,
    created_on          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by           INT NOT NULL DEFAULT 0,
    edited_on           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted             TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_assignee_status (assignee_id, status),
    KEY ix_target (target_entity_type, target_entity_id),
    KEY ix_template (template_id, template_version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS work_instance_results (
    c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    instance_id       INT NOT NULL,
    template_item_id  INT NOT NULL,
    -- Values are stored typed-aware. Consumer picks the right column based on item's input_type.
    value_text        TEXT NULL,
    value_number      DECIMAL(20,4) NULL,
    value_date        DATE NULL,
    note              VARCHAR(500) NOT NULL DEFAULT '',
    -- Mobile provenance per-answer (each save is one upsert; retried POSTs collapse).
    answered_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    answered_by       INT NOT NULL DEFAULT 0,
    time_source       ENUM('SERVER','DEVICE','GPS') NOT NULL DEFAULT 'SERVER',
    UNIQUE KEY uq_instance_item (instance_id, template_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS work_instance_attachments (
    c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    instance_id       INT NOT NULL,
    -- Nullable — attachments can be item-scoped (photo for a specific checklist row) or
    -- instance-scoped (start-of-visit selfie, closeout signature).
    template_item_id  INT NOT NULL DEFAULT 0,
    phase             ENUM('GENERAL','SELFIE_PPE','ASSET_READING','SIGNATURE','CLOSEOUT') NOT NULL DEFAULT 'GENERAL',
    file_path         VARCHAR(500) NOT NULL,
    mime_type         VARCHAR(80) NOT NULL DEFAULT '',
    size_bytes        BIGINT NOT NULL DEFAULT 0,
    -- SHA256 dedups mobile bursts (client-side hash sent alongside upload).
    sha256            CHAR(64) NOT NULL DEFAULT '',
    caption           VARCHAR(200) NOT NULL DEFAULT '',
    gps_lat           DECIMAL(11,7) NULL,
    gps_lng           DECIMAL(11,7) NULL,
    -- When the device captured the photo (may lag uploaded_at by hours if offline).
    device_captured_at TIMESTAMP NULL,
    uploaded_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    time_source        ENUM('SERVER','DEVICE','GPS') NOT NULL DEFAULT 'SERVER',
    time_trust         ENUM('TRUSTED','INFERRED') NOT NULL DEFAULT 'TRUSTED',
    uploaded_by        INT NOT NULL DEFAULT 0,
    deleted            TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_instance_sha (instance_id, sha256),
    KEY ix_instance (instance_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
