-- Slice 8S-3 · PM Execution + Callouts
--
-- Execution surface for Field Supervisor. A pm_service is spawned from a schedule row when
-- Field Supervisor arrives on site; findings + parts used + client acceptance stamped.
--
-- Callouts are intake/dispatch/resolve for reactive work — SLA clock starts on intake,
-- stops on restoration; breach counted for penalty math.

CREATE TABLE IF NOT EXISTS pm_services (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pm_no VARCHAR(30) NOT NULL UNIQUE,
    contract_id INT UNSIGNED NOT NULL,
    contract_asset_id INT UNSIGNED NOT NULL,
    template_id INT UNSIGNED NOT NULL COMMENT 'Snapshot — stays on this version even if template changes',
    template_version SMALLINT UNSIGNED NULL,
    site_id INT UNSIGNED NULL,
    schedule_id INT UNSIGNED NULL COMMENT 'Source row from service_contract_pm_schedule',
    scheduled_for DATE NULL,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    gps_lat DECIMAL(10,7) NULL,
    gps_lng DECIMAL(10,7) NULL,
    ppe_confirmed TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('DRAFT','IN_PROGRESS','DONE','REVIEWED','CLIENT_ACCEPTED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    done_by INT UNSIGNED NULL,
    done_by_name VARCHAR(160) NULL,
    reviewed_by INT UNSIGNED NULL,
    reviewed_by_name VARCHAR(160) NULL,
    reviewed_on DATETIME NULL,
    client_decision ENUM('ACCEPTED','REJECTED','PENDING') NULL,
    client_decided_by_name VARCHAR(191) NULL,
    client_signature_path VARCHAR(500) NULL,
    notes TEXT NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_contract (contract_id),
    KEY idx_asset (contract_asset_id),
    KEY idx_status (status),
    KEY idx_scheduled (scheduled_for)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pm_service_findings (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pm_service_id INT UNSIGNED NOT NULL,
    template_item_id INT UNSIGNED NOT NULL,
    result_check TINYINT(1) NULL COMMENT 'For CHECK type',
    result_number DECIMAL(20,4) NULL COMMENT 'For NUMBER type',
    result_text TEXT NULL COMMENT 'For TEXT type',
    result_photo_path VARCHAR(500) NULL COMMENT 'For PHOTO type',
    result_signature_path VARCHAR(500) NULL COMMENT 'For SIGNATURE type',
    qty_replaced DECIMAL(12,4) NULL COMMENT 'When template item tracks_qty=1',
    remark VARCHAR(500) NULL,
    KEY idx_pm (pm_service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pm_service_parts_used (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pm_service_id INT UNSIGNED NOT NULL,
    part_id INT UNSIGNED NOT NULL,
    qty DECIMAL(12,4) NOT NULL DEFAULT 1,
    unit_cost_at_use DECIMAL(20,2) NULL,
    source ENUM('STOCK','PROCURED','CLIENT_SUPPLIED','ADHOC') NOT NULL DEFAULT 'STOCK',
    notes VARCHAR(300) NULL,
    KEY idx_pm (pm_service_id),
    KEY idx_part (part_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS callouts (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    callout_no VARCHAR(30) NOT NULL UNIQUE,
    contract_id INT UNSIGNED NOT NULL,
    site_id INT UNSIGNED NULL,
    asset_id INT UNSIGNED NULL COMMENT 'Optional specific asset',
    category_id INT UNSIGNED NOT NULL,
    reported_by_name VARCHAR(160) NULL,
    reported_by_phone VARCHAR(40) NULL,
    reported_at DATETIME NOT NULL,
    description TEXT NULL,
    status ENUM('OPEN','DISPATCHED','ON_SITE','RESOLVED','CLIENT_ACCEPTED','CLOSED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    dispatched_at DATETIME NULL,
    dispatched_to INT UNSIGNED NULL,
    dispatched_to_name VARCHAR(160) NULL,
    arrived_at DATETIME NULL,
    resolved_at DATETIME NULL,
    resolution_template_id INT UNSIGNED NULL,
    resolution_description TEXT NULL,
    labour_hrs DECIMAL(8,2) NULL,
    mileage_km DECIMAL(10,2) NULL,
    is_emergency TINYINT(1) NOT NULL DEFAULT 0,
    sla_response_breached_hrs DECIMAL(8,2) NULL COMMENT 'Hours beyond SLA response — computed at resolution',
    sla_restore_breached_hrs DECIMAL(8,2) NULL,
    client_accepted_by_name VARCHAR(160) NULL,
    client_signature_path VARCHAR(500) NULL,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_contract (contract_id),
    KEY idx_status (status),
    KEY idx_reported (reported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS callout_parts_used (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    callout_id INT UNSIGNED NOT NULL,
    part_id INT UNSIGNED NOT NULL,
    qty DECIMAL(12,4) NOT NULL DEFAULT 1,
    unit_cost_at_use DECIMAL(20,2) NULL,
    source ENUM('STOCK','PROCURED','CLIENT_SUPPLIED','ADHOC') NOT NULL DEFAULT 'STOCK',
    notes VARCHAR(300) NULL,
    KEY idx_callout (callout_id),
    KEY idx_part (part_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Perms
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PmServices', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'view' UNION ALL SELECT 'startAj' UNION ALL SELECT 'saveFindingAj' UNION ALL SELECT 'addPartAj' UNION ALL SELECT 'finishAj' UNION ALL SELECT 'reviewAj' UNION ALL SELECT 'clientAcceptAj') actions
WHERE g.user_group_name IN ('Administrator','Field Supervisor','Projects Coordinator','Project Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PmServices' AND p.action_allowed = actions.ac);

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Callouts', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'view' UNION ALL SELECT 'saveIntakeAj' UNION ALL SELECT 'dispatchAj' UNION ALL SELECT 'arriveAj' UNION ALL SELECT 'resolveAj' UNION ALL SELECT 'addPartAj') actions
WHERE g.user_group_name IN ('Administrator','Field Supervisor','Projects Coordinator','Project Manager','CRM')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Callouts' AND p.action_allowed = actions.ac);
