-- ============================================================
-- 從 steps_sprint 補充資料到 vpn
-- ⚠️  steps_sprint 唯讀，只 SELECT，不寫入
-- 執行順序：clients → projects → sprints → time_records
-- ============================================================

SET foreign_key_checks = 0;
SET @man_day_hours = 8;

-- ─────────────────────────────────────────────────────────────
-- STEP 1：補充 clients（steps_sprint 有但 vpn 沒有的）
-- ─────────────────────────────────────────────────────────────
INSERT INTO vpn.clients (name, created_at, updated_at)
SELECT sc.name, sc.created_at, sc.updated_at
FROM steps_sprint.task_clients sc
LEFT JOIN vpn.clients vc ON sc.name = vc.name
WHERE vc.id IS NULL;

SELECT ROW_COUNT() AS step1_clients_added;

-- ─────────────────────────────────────────────────────────────
-- STEP 2：補充 projects（steps_sprint 有但 vpn 沒有的）
-- type 對應：專案→依 status_id 判斷，公司產品開發→商品，其他→維護
-- status 對應：1→開發, 2→保固, 3→維護, 5→結束
-- ─────────────────────────────────────────────────────────────
INSERT INTO vpn.projects
    (client_id, name, project_type, start_date, end_date, created_at, updated_at)
SELECT
    vc.id,
    sp.name,
    CASE sp.type
        WHEN '公司產品開發' THEN '商品'
        WHEN '其他'         THEN '維護'
        ELSE
            CASE sp.status_id
                WHEN 2 THEN '保固'
                WHEN 3 THEN '維護'
                WHEN 5 THEN '結束'
                ELSE '開發'
            END
    END,
    sp.start_date,
    sp.deadline,
    sp.created_at,
    sp.updated_at
FROM steps_sprint.task_projects sp
JOIN steps_sprint.task_clients sc ON sp.task_client_id = sc.id
JOIN vpn.clients vc ON sc.name = vc.name
LEFT JOIN vpn.projects vp ON sp.name = vp.name
WHERE vp.id IS NULL;

SELECT ROW_COUNT() AS step2_projects_added;

-- ─────────────────────────────────────────────────────────────
-- STEP 3：建立 user 對應暫存表
-- 以 email 比對 steps_sprint user_id → vpn user_id
-- ─────────────────────────────────────────────────────────────
CREATE TABLE vpn._import_user_map (
    ss_user_id  BIGINT UNSIGNED NOT NULL,
    vpn_user_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (ss_user_id)
) ENGINE=InnoDB;

INSERT INTO vpn._import_user_map (ss_user_id, vpn_user_id)
SELECT su.id, vu.id
FROM steps_sprint.users su
JOIN vpn.users vu ON su.email = vu.email;

SELECT COUNT(*) AS step3_user_pairs FROM vpn._import_user_map;

-- ─────────────────────────────────────────────────────────────
-- STEP 4：建立 project 對應表（現在 vpn 已有所有 projects）
-- ─────────────────────────────────────────────────────────────
CREATE TABLE vpn._import_project_map (
    ss_project_id  BIGINT UNSIGNED NOT NULL,
    vpn_project_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (ss_project_id)
) ENGINE=InnoDB;

-- 每個 ss_project_id 只對應一個 vpn project（取最小 vp.id 避免重名衝突）
INSERT IGNORE INTO vpn._import_project_map (ss_project_id, vpn_project_id)
SELECT sp.id, MIN(vp.id)
FROM steps_sprint.task_projects sp
JOIN vpn.projects vp ON sp.name = vp.name
GROUP BY sp.id;

SELECT COUNT(*) AS step4_project_pairs FROM vpn._import_project_map;

-- ─────────────────────────────────────────────────────────────
-- STEP 5：匯入 sprints（task_sprints → vpn.sprints）
-- project_id 已設為 nullable，sprint 為全域週期不綁定專案
-- status_id: 1=進行中→active, 2=已完成→completed
-- ─────────────────────────────────────────────────────────────
INSERT INTO vpn.sprints
    (id, project_id, name, year, week, start_date, end_date,
     goal, required_users, status, created_at, updated_at)
SELECT
    ss.id,
    NULL,
    ss.name,
    ss.year,
    ss.week,
    ss.start_date,
    ss.end_date,
    ss.description,
    ss.required_users,
    CASE ss.status_id
        WHEN 1 THEN 'active'
        ELSE 'completed'
    END,
    ss.created_at,
    ss.updated_at
FROM steps_sprint.task_sprints ss
ON DUPLICATE KEY UPDATE
    name           = VALUES(name),
    year           = VALUES(year),
    week           = VALUES(week),
    start_date     = VALUES(start_date),
    end_date       = VALUES(end_date),
    goal           = VALUES(goal),
    required_users = VALUES(required_users),
    status         = VALUES(status),
    updated_at     = VALUES(updated_at);

SELECT ROW_COUNT() AS step5_sprints_upserted;

-- ─────────────────────────────────────────────────────────────
-- STEP 6：匯入 time_records
-- 條件：user 有對應 email 且 project 有對應，hours > 0
-- ─────────────────────────────────────────────────────────────
INSERT INTO vpn.time_records
    (user_id, project_id, sprint_id, record_date,
     hours, man_days, overtime_hours, is_billable,
     activity, comments, created_at, updated_at)
SELECT
    um.vpn_user_id,
    pm.vpn_project_id,
    tr.task_sprint_id,
    tr.record_date,
    tr.hours,
    CASE WHEN tr.man_days > 0 THEN tr.man_days ELSE ROUND(tr.hours / @man_day_hours, 4) END,
    tr.overtime_hours,
    tr.is_billable,
    'steps-sprint歷史',
    tr.remark,
    tr.created_at,
    tr.updated_at
FROM steps_sprint.task_time_records tr
JOIN vpn._import_user_map    um ON tr.user_id         = um.ss_user_id
JOIN vpn._import_project_map pm ON tr.task_project_id = pm.ss_project_id
WHERE tr.hours > 0
  AND NOT EXISTS (
      SELECT 1 FROM vpn.time_records vtr
      WHERE vtr.user_id     = um.vpn_user_id
        AND vtr.project_id  = pm.vpn_project_id
        AND vtr.record_date = tr.record_date
        AND vtr.activity    = 'steps-sprint歷史'
  );

SELECT ROW_COUNT() AS step6_time_records_added;

-- 清除工作表

SET foreign_key_checks = 1;

-- ─────────────────────────────────────────────────────────────
-- 驗證結果
-- ─────────────────────────────────────────────────────────────
SELECT '=== 匯入完成 ===' AS info;
SELECT 'vpn.clients'      AS tbl, COUNT(*) AS cnt FROM vpn.clients      UNION ALL
SELECT 'vpn.projects'     AS tbl, COUNT(*) AS cnt FROM vpn.projects     UNION ALL
SELECT 'vpn.sprints'      AS tbl, COUNT(*) AS cnt FROM vpn.sprints      UNION ALL
SELECT 'vpn.time_records' AS tbl, COUNT(*) AS cnt FROM vpn.time_records;

SELECT 'steps-sprint歷史 工時記錄' AS label,
       COUNT(*)                     AS records,
       ROUND(SUM(hours), 1)         AS total_hours,
       COUNT(DISTINCT user_id)      AS users,
       COUNT(DISTINCT project_id)   AS projects
FROM vpn.time_records
WHERE activity = 'steps-sprint歷史';
