-- Slice 8S-7 · Photo evidence for PMs + Callouts
--
-- Two general-purpose photo tables (site photos, PPE, resolution evidence, client signature).
-- Per-checklist-item photos already live on pm_service_findings.result_photo_path — Slice S7
-- just makes that path a real uploaded file instead of user-typed text.
--
-- Storage: attachments/pm-photos/{pm_service_id}/ and attachments/callout-photos/{callout_id}/

CREATE TABLE IF NOT EXISTS pm_service_photos (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pm_service_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    original_name VARCHAR(300) NOT NULL,
    mime VARCHAR(120) NOT NULL,
    size_bytes INT UNSIGNED NOT NULL,
    kind ENUM('SITE','PPE','FINDING','RESOLUTION','CLIENT_SIGNATURE','OTHER') NOT NULL DEFAULT 'OTHER',
    caption VARCHAR(300) NULL,
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) NULL,
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_pm (pm_service_id),
    KEY idx_kind (kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS callout_photos (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    callout_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    original_name VARCHAR(300) NOT NULL,
    mime VARCHAR(120) NOT NULL,
    size_bytes INT UNSIGNED NOT NULL,
    kind ENUM('SITE','ARRIVAL','FAULT','RESOLUTION','CLIENT_SIGNATURE','OTHER') NOT NULL DEFAULT 'OTHER',
    caption VARCHAR(300) NULL,
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) NULL,
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_callout (callout_id),
    KEY idx_kind (kind)
) 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, ctrl, ac, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'PmServices' AS ctrl, 'addPhotoAj' AS ac
    UNION ALL SELECT 'PmServices', 'removePhotoAj'
    UNION ALL SELECT 'PmServices', 'photoDownload'
    UNION ALL SELECT 'PmServices', 'saveFindingPhotoAj'
    UNION ALL SELECT 'Callouts',   'addPhotoAj'
    UNION ALL SELECT 'Callouts',   'removePhotoAj'
    UNION ALL SELECT 'Callouts',   'photoDownload'
) 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 = actions.ctrl AND p.action_allowed = actions.ac);
