-- Slice 8Z-a · Handover packet — delivery block + overheads carry-over.

-- ── 1. Delivery block on goods_client_pos ─────────────────────────────────
SET @c := (SELECT COUNT(*) FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'goods_client_pos'
              AND column_name = 'delivery_mode');
SET @sql := IF(@c = 0,
    "ALTER TABLE goods_client_pos
        ADD COLUMN delivery_mode ENUM('SINGLE_LOCATION','MULTI_LOCATION') NOT NULL DEFAULT 'SINGLE_LOCATION' AFTER handover_to_user_id,
        ADD COLUMN primary_delivery_location VARCHAR(300) NULL AFTER delivery_mode,
        ADD COLUMN delivery_contact_name VARCHAR(160) NULL AFTER primary_delivery_location,
        ADD COLUMN delivery_contact_phone VARCHAR(60) NULL AFTER delivery_contact_name,
        ADD COLUMN incoterm ENUM('EXW','FCA','CPT','CIP','DAP','DPU','DDP') NULL AFTER delivery_contact_phone,
        ADD COLUMN delivery_deadline DATE NULL AFTER incoterm,
        ADD COLUMN delivery_special_instructions TEXT NULL AFTER delivery_deadline",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. c_project_overheads ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS c_project_overheads (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    po_id INT UNSIGNED NULL,
    category ENUM('TRANSPORT','HSE','INSURANCE','PACKAGING','WAREHOUSING','INSTALLATION','OTHER') NOT NULL DEFAULT 'OTHER',
    label VARCHAR(200) NOT NULL,
    amount DECIMAL(20,2) NOT NULL DEFAULT 0,
    notes VARCHAR(500) NULL,
    source ENUM('TENDER_CARRY_OVER','PC_ADDED') NOT NULL DEFAULT 'PC_ADDED',
    source_overhead_id INT UNSIGNED NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    edited_by INT UNSIGNED NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_project (project_id),
    KEY idx_po (po_id),
    KEY idx_source (project_id, source_overhead_id)
    -- Uniqueness is enforced in model (seedFromTender pre-checks source ids) rather than the schema —
    -- an index over `deleted` breaks bulk soft-deletes (multiple deleted=1 rows collide on the same source id).
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. Perms ──────────────────────────────────────────────────────────────
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'savePoDeliveryAj'      AS ac
             UNION ALL SELECT 'overheadAddAj'
             UNION ALL SELECT 'overheadUpdateAj'
             UNION ALL SELECT 'overheadDeleteAj'
             UNION ALL SELECT 'overheadsSeedFromTenderAj') actions
WHERE g.user_group_name IN ('Projects Coordinator','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'Projects'
      AND p.action_allowed = actions.ac
);
