-- Slice 8Y · Project handover deepening — PM attaches Client PO docs, adds timelines,
-- assigns PC with dates, PC gets three item-authoring paths on goods projects.

-- ── 1. c_projects_documents ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS c_projects_documents (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    doc_kind ENUM('CLIENT_PO','BOQ','DRAWING','SPEC','WARRANTY','OTHER') NOT NULL DEFAULT 'CLIENT_PO',
    file_path VARCHAR(500) NOT NULL,
    original_name VARCHAR(300) NOT NULL,
    mime VARCHAR(120) NULL,
    size_bytes INT UNSIGNED NOT NULL DEFAULT 0,
    notes VARCHAR(500) NULL,
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) 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_kind (doc_kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 2. c_project_milestones ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS c_project_milestones (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    sequence INT UNSIGNED NOT NULL DEFAULT 1,
    title VARCHAR(200) NOT NULL,
    description VARCHAR(1000) NULL,
    target_date DATE NULL,
    owner_slot VARCHAR(60) NULL,
    owner_user_id INT UNSIGNED NULL,
    owner_user_name VARCHAR(160) NULL,
    status ENUM('NOT_STARTED','IN_PROGRESS','DONE','BLOCKED','CANCELLED') NOT NULL DEFAULT 'NOT_STARTED',
    completed_on DATETIME NULL,
    completed_by INT UNSIGNED NULL,
    completed_by_name VARCHAR(160) NULL,
    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_seq (project_id, sequence),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. Perms ──────────────────────────────────────────────────────────────
-- Documents: PM + Admin upload/delete; anyone with Projects/view can download.
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 'documentUploadAj' AS ac UNION ALL SELECT 'documentDeleteAj') actions
WHERE g.user_group_name IN ('Project Manager','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
);

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'documentDownload', 0, NOW()
FROM user_groups g
WHERE EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'Projects'
      AND p.action_allowed = 'view'
)
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p2
    WHERE p2.user_group_id = g.c0d
      AND p2.controller_allowed = 'Projects'
      AND p2.action_allowed = 'documentDownload'
);

-- Milestones: PM + Admin manage; PC updates own status via same actions.
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 'milestoneAddAj' AS ac UNION ALL SELECT 'milestoneUpdateAj' UNION ALL SELECT 'milestoneDeleteAj') actions
WHERE g.user_group_name IN ('Project Manager','Administrator','Projects Coordinator')
  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
);

-- Goods items authoring: PC + Admin (bulk-convert / xlsx / template).
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 'goodsConvertScopeAj' AS ac UNION ALL SELECT 'goodsImportItemsAj' UNION ALL SELECT 'goodsItemsTemplate') 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
);

-- Also grant the existing goods_add_item / remove_item / submit to PC (they own item authoring now).
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 'goodsAddItemAj' AS ac UNION ALL SELECT 'goodsRemoveItemAj' UNION ALL SELECT 'goodsSubmitOnboardingAj') actions
WHERE g.user_group_name IN ('Projects Coordinator')
  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
);
