-- Pacific Logistics · slice 18 · Trip Attachments
--
-- Iter 3 Turn 4 adds photo/document capture on trips. The categories are
-- driven by the UAT feedback:
--
--   * At trip START:  truck plates (horse + trailer), truck front, cargo
--     load-out photo, and the odometer_start picture. Optional but strongly
--     recommended — dispatcher can enforce via mobile app UI.
--   * DURING trip: damage evidence, incident photos, fuel receipts, expense
--     receipts (already handled by their own tables — attached here only if
--     ops wants a per-trip index).
--   * At DELIVERY: proof-of-delivery signature, cargo-received photo,
--     odometer_end picture, dropoff-site photo.
--
-- Each attachment optionally links to a trip_stop_id so multi-stop trips can
-- pin photos to a specific waypoint (POD @ stop 3, damage @ stop 1, etc.).


CREATE TABLE IF NOT EXISTS trip_attachments (
    c0d             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id       INT UNSIGNED NOT NULL DEFAULT 1,
    trip_id         INT UNSIGNED NOT NULL,
    trip_stop_id    INT UNSIGNED NULL,              -- optional link to specific stop
    category        VARCHAR(40)  NOT NULL DEFAULT 'other',
                                                    -- truck_plate_horse | truck_plate_trailer |
                                                    -- truck_front | cargo_loadout | odometer_start |
                                                    -- damage | incident | pod_signature | pod_photo |
                                                    -- cargo_received | odometer_end | dropoff_site |
                                                    -- receipt | other
    caption         VARCHAR(255) NULL,
    file_url        VARCHAR(500) NOT NULL,
    file_name       VARCHAR(160) NULL,
    file_size       INT UNSIGNED NULL,
    mime_type       VARCHAR(60)  NULL,
    -- Device-side geo/time (when the driver actually took the photo)
    taken_at        DATETIME     NULL,
    taken_lat       DECIMAL(10,6) NULL,
    taken_lng       DECIMAL(10,6) NULL,
    -- Provenance
    source          VARCHAR(20)  NOT NULL DEFAULT 'web',   -- web | mobile | import
    uploaded_by     INT UNSIGNED NULL,               -- users.c0d (if web-upload)
    driver_id       INT UNSIGNED NULL,               -- drivers.c0d (if mobile-upload)
    created_on      DATETIME     NOT NULL,
    deleted         TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (c0d),
    KEY idx_ta_trip     (trip_id, category, deleted),
    KEY idx_ta_stop     (trip_stop_id),
    KEY idx_ta_created  (created_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — TripAttachments is Dispatcher / Driver / Fleet / Admin scope.
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(10, 'TripAttachments', 'upload', 1, NOW()), (10, 'TripAttachments', 'delete', 1, NOW()), (10, 'TripAttachments', 'view', 1, NOW()),
(20, 'TripAttachments', 'upload', 1, NOW()), (20, 'TripAttachments', 'delete', 1, NOW()), (20, 'TripAttachments', 'view', 1, NOW()),
(21, 'TripAttachments', 'upload', 1, NOW()),                                                (21, 'TripAttachments', 'view', 1, NOW()),
(22, 'TripAttachments', 'upload', 1, NOW()), (22, 'TripAttachments', 'delete', 1, NOW()), (22, 'TripAttachments', 'view', 1, NOW()),
(1,  'TripAttachments', 'view',   1, NOW());
