-- Slice 8b-5 · Callout Playbook + SLA
-- A playbook = SLA response/resolution targets by severity + escalation chain by severity.
-- Playbooks are attached to Services projects (one playbook per project at a time).
-- Actual callouts + visits are a later slice; this ships the standards library + attachment.

CREATE TABLE IF NOT EXISTS callout_playbooks (
    c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code             VARCHAR(60) NOT NULL,
    name             VARCHAR(200) NOT NULL,
    description      TEXT NULL,
    is_active        TINYINT(1) NOT NULL DEFAULT 1,
    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 (code),
    KEY ix_active (is_active, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS callout_playbook_slas (
    c0d                       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    playbook_id               INT NOT NULL,
    -- P1 = critical (site down); P4 = cosmetic. Fixed 4-severity ladder to match industry norm.
    severity                  ENUM('P1','P2','P3','P4') NOT NULL,
    -- Response = we arrive on site or acknowledge remotely.
    -- Resolution = fault is fixed / workaround in place.
    response_target_hours     DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    resolution_target_hours   DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    notes                     VARCHAR(500) NOT NULL DEFAULT '',
    created_on                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted                   TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_playbook_severity (playbook_id, severity, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS callout_playbook_escalations (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    playbook_id            INT NOT NULL,
    severity               ENUM('P1','P2','P3','P4') NOT NULL,
    step_no                INT NOT NULL DEFAULT 1,
    -- Role to notify (e.g. "Project Manager", "Field Supervisor"). Free-text so consumers
    -- (later slice) can map to actual users at run-time.
    role                   VARCHAR(80) NOT NULL,
    -- When to notify — hours after callout raised (step 1 might be 0, step 2 might be 2h, step 3 might be 4h).
    notify_after_hours     DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    notes                  VARCHAR(500) NOT NULL DEFAULT '',
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_playbook_sev (playbook_id, severity, step_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Project attachment. One playbook per project at a time; reattaching a different playbook
-- soft-deletes the previous row (audit trail preserved via deleted=1 rows).
CREATE TABLE IF NOT EXISTS project_callout_config (
    c0d                INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id         INT NOT NULL,
    playbook_id        INT NOT NULL,
    attached_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    attached_by        INT NOT NULL DEFAULT 0,
    attached_by_name   VARCHAR(120) NOT NULL DEFAULT '',
    notes              VARCHAR(500) NOT NULL DEFAULT '',
    detached_on        TIMESTAMP NULL,
    detached_by        INT NOT NULL DEFAULT 0,
    deleted            TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_project_active (project_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
