-- Slice 5 · Assignments-during-approval + role-based project provisioning
-- Idempotent: uses INFORMATION_SCHEMA guards so re-running is safe.

/* 1. Two new seats on c_projects (project_manager_id + pc_id already exist) */
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE c_projects ADD COLUMN accountant_id INT NOT NULL DEFAULT 0 AFTER pc_name, ADD COLUMN accountant_name VARCHAR(120) NOT NULL DEFAULT "" AFTER accountant_id',
    'SELECT "c_projects.accountant_id already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'c_projects' AND column_name = 'accountant_id');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE c_projects ADD COLUMN procurement_id INT NOT NULL DEFAULT 0 AFTER accountant_name, ADD COLUMN procurement_name VARCHAR(120) NOT NULL DEFAULT "" AFTER procurement_id',
    'SELECT "c_projects.procurement_id already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'c_projects' AND column_name = 'procurement_id');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 2. Per-step assignment requirements (comma-separated slot names like "ACCOUNTANT,PROCUREMENT") */
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE fin_chain_steps ADD COLUMN required_assignments VARCHAR(200) NOT NULL DEFAULT "" AFTER description',
    'SELECT "fin_chain_steps.required_assignments already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'fin_chain_steps' AND column_name = 'required_assignments');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 3. Accumulated payload on the approval instance (JSON of slot -> {user_id, user_name, assigned_by, at}) */
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE fin_approvals ADD COLUMN step_payload_json LONGTEXT NULL AFTER summary_json',
    'SELECT "fin_approvals.step_payload_json already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'fin_approvals' AND column_name = 'step_payload_json');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 4. Seat-reassignment audit log (used later when PM re-nominates PC, etc.) */
CREATE TABLE IF NOT EXISTS c_projects_role_changes (
    c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id       INT NOT NULL,
    role_slot        VARCHAR(40) NOT NULL,
    old_user_id      INT NOT NULL DEFAULT 0,
    old_user_name    VARCHAR(120) NOT NULL DEFAULT '',
    new_user_id      INT NOT NULL DEFAULT 0,
    new_user_name    VARCHAR(120) NOT NULL DEFAULT '',
    changed_by       INT NOT NULL DEFAULT 0,
    changed_by_name  VARCHAR(120) NOT NULL DEFAULT '',
    reason           VARCHAR(400) NOT NULL DEFAULT '',
    changed_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY ix_project (project_id),
    KEY ix_slot    (role_slot)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* 5. Seed the assignment requirements on the two TENDER_CONTRACT_APPROVAL steps.
      FM_VERIFY captures Accountant + Procurement in one step.
      EXEC_APPROVE captures Project Manager and terminates the chain. */
UPDATE fin_chain_steps s
   JOIN fin_chain_defs d ON d.c0d = s.chain_id
    SET s.required_assignments = 'ACCOUNTANT,PROCUREMENT'
  WHERE d.chain_key = 'TENDER_CONTRACT_APPROVAL' AND s.step_key = 'FM_VERIFY';

UPDATE fin_chain_steps s
   JOIN fin_chain_defs d ON d.c0d = s.chain_id
    SET s.required_assignments = 'PROJECT_MANAGER'
  WHERE d.chain_key = 'TENDER_CONTRACT_APPROVAL' AND s.step_key = 'EXEC_APPROVE';
