-- ============================================================
-- 绩效模块演示种子数据 (v4.0)
-- 可重复执行（幂等），用 ON CONFLICT DO NOTHING 防止重复
-- 前置条件：需要先执行系统主种子（users/orgs/departments）
--           和 performance-basic-seed.ts（360 模板）
-- 执行方式：
--   docker exec ffoa-dev-postgres psql -U ffws_dev -d <db_name> \
--     -f /path/to/performance-demo-seed.sql
-- ============================================================

DO $$
DECLARE
  -- 动态查询的依赖 ID
  v_admin_id UUID;
  v_emp_role_id UUID;
  v_org RECORD;
  v_dept RECORD;
  v_default_tpl_id UUID;

  -- 等级配置
  v_grade_config_id UUID;

  -- 循环变量
  v_cycle_id UUID;
  v_emp RECORD;
  v_user_id UUID;
  v_assign_id UUID;
  v_eval_id UUID;
  v_task_id UUID;
  v_obj_id UUID;
  self_s INT;
  mgr_s INT;
  j INT;
  i INT;
  k INT;
  v_peer RECORD;
  c_status platform_performance."CycleStatus";
  c_type platform_performance."CycleType";
  c_name TEXT;
  a_status platform_performance."KpiAssignmentStatus";
  as_status platform_performance."KpiAssessmentStatus";
  r_confirm platform_performance."ResultConfirmStatus";
  v_emp_count INT := 0;

  -- 密码 hash: Test1234! (bcrypt, salt rounds = 10)
  pwd_hash TEXT := '$2b$10$L4xGfkzDvr5v7sbO6u1x2O7euUyaLG0kA/GTaZA/XAfGPghXiJF2W';

  -- 30 个中文名
  cn_names TEXT[] := ARRAY[
    '王明辉','李思远','张晓峰','刘芳菲','陈建国','杨美琳','赵志强','黄晓燕',
    '周天宇','吴雅婷','徐海涛','孙丽华','马骏驰','朱玉洁','郭文博','何雨彤',
    '林清雅','罗铭哲','梁思涵','宋雅轩','韩子豪','唐婉婷','曹泽宇','邓欣怡',
    '冯浩然','谢雨涵','程子轩','蔡欣妍','潘明达','袁诗琪'
  ];
  dept_ids UUID[];

BEGIN
  -- ============================================================
  -- 0. 动态查询依赖数据（不硬编码 UUID）
  -- ============================================================
  SELECT id INTO v_admin_id FROM platform_iam.users WHERE username = 'itadmin' LIMIT 1;
  IF v_admin_id IS NULL THEN
    RAISE EXCEPTION '❌ 找不到 itadmin 用户，请先执行系统主种子';
  END IF;

  SELECT id INTO v_emp_role_id FROM platform_iam.roles WHERE name = 'Employee' LIMIT 1;
  IF v_emp_role_id IS NULL THEN
    RAISE EXCEPTION '❌ 找不到 Employee 角色，请先执行系统主种子';
  END IF;

  -- 查第一个活跃组织及其下的部门
  SELECT id, name INTO v_org
    FROM corp_hr.organizations WHERE status = 'ACTIVE' ORDER BY name LIMIT 1;
  IF v_org.id IS NULL THEN
    RAISE EXCEPTION '❌ 找不到活跃组织，请先执行系统主种子';
  END IF;
  RAISE NOTICE '📍 目标组织: % (%)', v_org.name, v_org.id;

  -- 查该组织下最多 4 个部门
  SELECT array_agg(id ORDER BY name) INTO dept_ids
    FROM (SELECT id, name FROM corp_hr.departments
          WHERE organization_id = v_org.id AND deleted_at IS NULL
          ORDER BY name LIMIT 4) sub;
  IF dept_ids IS NULL OR array_length(dept_ids, 1) = 0 THEN
    RAISE EXCEPTION '❌ 组织 % 下没有部门', v_org.name;
  END IF;
  RAISE NOTICE '📍 部门数量: %', array_length(dept_ids, 1);

  -- 查默认 360 模板（由 basic-seed 创建）
  SELECT id INTO v_default_tpl_id
    FROM platform_performance.evaluation_360_template
    WHERE is_default = true AND deleted_at IS NULL LIMIT 1;

  -- ============================================================
  -- 1. 等级配置（GradeConfig）
  -- ============================================================
  SELECT id INTO v_grade_config_id
    FROM platform_performance.grade_config
    WHERE name = '标准五级制' AND deleted_at IS NULL LIMIT 1;

  IF v_grade_config_id IS NULL THEN
    v_grade_config_id := gen_random_uuid();
    INSERT INTO platform_performance.grade_config
      (id, name, description, grades, is_default, is_active, organization_id, created_by, created_at, updated_at)
    VALUES (v_grade_config_id, '标准五级制', '适用于大多数绩效周期的五级评定',
      '[
        {"code":"S","name":"卓越","nameEn":"Outstanding","minScore":90,"maxScore":100,"color":"#52c41a"},
        {"code":"A","name":"优秀","nameEn":"Excellent","minScore":80,"maxScore":89.99,"color":"#1890ff"},
        {"code":"B","name":"良好","nameEn":"Good","minScore":70,"maxScore":79.99,"color":"#faad14"},
        {"code":"C","name":"待改进","nameEn":"Needs Improvement","minScore":60,"maxScore":69.99,"color":"#fa8c16"},
        {"code":"D","name":"不合格","nameEn":"Unsatisfactory","minScore":0,"maxScore":59.99,"color":"#f5222d"}
      ]'::jsonb,
      true, true, v_org.id, v_admin_id, NOW(), NOW());
    RAISE NOTICE '✅ 等级配置已创建: 标准五级制';
  ELSE
    RAISE NOTICE '⏭️  等级配置已存在，跳过';
  END IF;

  -- ============================================================
  -- 2. 创建 30 个演示员工
  -- ============================================================
  FOR i IN 1..30 LOOP
    v_user_id := gen_random_uuid();
    BEGIN
      INSERT INTO platform_iam.users (id, username, display_name, email, password_hash, status, created_at, updated_at)
      VALUES (v_user_id, 'demo.perf' || i, cn_names[i], 'demo.perf' || i || '@ff.com',
        pwd_hash, 'ACTIVE', NOW(), NOW())
      ON CONFLICT (username) DO UPDATE SET display_name = EXCLUDED.display_name RETURNING id INTO v_user_id;

      INSERT INTO platform_iam.user_role_rel (id, user_id, role_id, created_at)
      VALUES (gen_random_uuid(), v_user_id, v_emp_role_id, NOW()) ON CONFLICT DO NOTHING;

      INSERT INTO corp_hr.user_departments (id, user_id, organization_id, department_id, manager_id, is_primary, joined_at, created_at, updated_at)
      VALUES (gen_random_uuid(), v_user_id, v_org.id,
        dept_ids[1 + ((i - 1) % array_length(dept_ids, 1))],
        v_admin_id, true, NOW(), NOW(), NOW())
      ON CONFLICT DO NOTHING;
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
  END LOOP;
  RAISE NOTICE '✅ 30 个演示员工已创建（密码: Test1234!）';

  -- ============================================================
  -- 3. 为组织创建 5 个不同状态的周期 + 关联数据
  --    覆盖完整业务流：GOAL_SETTING → EVALUATING → CALIBRATING → CONFIRMING → COMPLETED
  -- ============================================================

  -- 跳过已有完整周期的组织
  IF (SELECT COUNT(*) FROM platform_performance.performance_cycle WHERE organization_id = v_org.id AND deleted_at IS NULL) >= 5 THEN
    RAISE NOTICE '⏭️  跳过 % (已有周期)', v_org.name;
    RETURN;
  END IF;

  FOR j IN 1..5 LOOP
    c_status := (ARRAY['GOAL_SETTING','EVALUATING','CALIBRATING','CONFIRMING','COMPLETED']::platform_performance."CycleStatus"[])[j];
    c_type := CASE WHEN j <= 4 THEN 'QUARTERLY'::platform_performance."CycleType" ELSE 'ANNUAL'::platform_performance."CycleType" END;
    c_name := v_org.name || ' ' || (ARRAY['Q2目标设定','Q2评估中','Q2校准中','Q2待确认','2025年度'])[j];
    v_cycle_id := gen_random_uuid();

    INSERT INTO platform_performance.performance_cycle
      (id, name, type, status, start_date, end_date, grade_config_id, organization_id, created_by, created_at, updated_at)
    VALUES (v_cycle_id, c_name, c_type, c_status,
      CASE WHEN j <= 4 THEN '2026-04-01'::date ELSE '2025-01-01'::date END,
      CASE WHEN j <= 4 THEN '2026-06-30'::date ELSE '2025-12-31'::date END,
      v_grade_config_id, v_org.id, v_admin_id, NOW(), NOW());

    -- ----------------------------------------------------------
    -- 3a. 战略目标（v4.0）— GOAL_SETTING 和 EVALUATING 周期各 2 个
    -- ----------------------------------------------------------
    IF c_status IN ('GOAL_SETTING', 'EVALUATING') THEN
      FOR i IN 1..2 LOOP
        v_obj_id := gen_random_uuid();
        INSERT INTO platform_performance.strategic_objective
          (id, cycle_id, seq, name, description, department_id, created_by, created_at, updated_at)
        VALUES (v_obj_id, v_cycle_id, i,
          (ARRAY['提升产品交付效率','加强跨部门协作','优化客户满意度','降低运营成本'])[((j-1)*2 + i - 1) % 4 + 1],
          '战略目标描述示例', dept_ids[1 + ((i - 1) % array_length(dept_ids, 1))],
          v_admin_id, NOW(), NOW());

        -- 为前 3 个演示员工分配目标
        FOR k IN 1..3 LOOP
          SELECT id INTO v_user_id FROM platform_iam.users WHERE username = 'demo.perf' || ((i-1)*3 + k) LIMIT 1;
          IF v_user_id IS NOT NULL THEN
            INSERT INTO platform_performance.strategic_objective_assignment
              (id, objective_id, assignee_id, created_at)
            VALUES (gen_random_uuid(), v_obj_id, v_user_id, NOW()) ON CONFLICT DO NOTHING;
          END IF;
        END LOOP;
      END LOOP;
    END IF;

    -- ----------------------------------------------------------
    -- 3b. KPI 分配 + 评估
    -- ----------------------------------------------------------
    v_emp_count := 0;
    FOR v_emp IN
      SELECT DISTINCT ud.user_id FROM corp_hr.user_departments ud
      WHERE ud.organization_id = v_org.id AND ud.left_at IS NULL AND ud.user_id != v_admin_id
    LOOP
      v_emp_count := v_emp_count + 1;

      FOR i IN 1..2 LOOP
        v_assign_id := gen_random_uuid();
        self_s := 65 + abs(hashtext(v_emp.user_id::text || j::text || i::text) % 30);
        mgr_s := 60 + abs(hashtext(v_emp.user_id::text || 'mgr' || j::text || i::text) % 35);

        a_status := CASE
          WHEN c_status = 'GOAL_SETTING' AND v_emp_count <= 10 THEN 'SUBMITTED'  -- 部分已提交
          WHEN c_status = 'GOAL_SETTING' THEN 'DRAFT'
          ELSE 'APPROVED'
        END::platform_performance."KpiAssignmentStatus";

        INSERT INTO platform_performance.kpi_assignment
          (id, cycle_id, employee_id, name, description, weight, unit, assignment_status,
           base_target, stretch_target, seq, created_at, updated_at)
        VALUES (v_assign_id, v_cycle_id, v_emp.user_id,
          (ARRAY['核心业务指标','团队协作指标'])[i],
          (ARRAY['完成核心业务交付目标','促进团队协作和知识共享'])[i],
          CASE WHEN i = 1 THEN 60 ELSE 40 END,
          CASE WHEN i = 1 THEN '%' ELSE '分' END,
          a_status, '完成80%', '完成100%', i, NOW(), NOW());

        -- 非目标设定阶段：创建评估数据
        IF c_status != 'GOAL_SETTING' THEN
          as_status := CASE
            WHEN c_status = 'EVALUATING' AND i = 2 THEN 'SELF_EVALUATED'
            ELSE 'MANAGER_EVALUATED'
          END::platform_performance."KpiAssessmentStatus";

          INSERT INTO platform_performance.kpi_assessment
            (id, assignment_id, self_score, self_comment, completion_note, status,
             self_evaluated_at, manager_score, manager_comment, manager_id, manager_evaluated_at)
          VALUES (gen_random_uuid(), v_assign_id, self_s, '自评完成',
            CASE WHEN i = 1 THEN '已完成主要交付物' ELSE '积极参与团队协作' END,
            as_status, NOW(),
            CASE WHEN as_status = 'SELF_EVALUATED' THEN NULL ELSE mgr_s END,
            CASE WHEN as_status = 'SELF_EVALUATED' THEN NULL ELSE '主管评价完成' END,
            CASE WHEN as_status = 'SELF_EVALUATED' THEN NULL ELSE v_admin_id END,
            CASE WHEN as_status = 'SELF_EVALUATED' THEN NULL ELSE NOW() END);
        END IF;
      END LOOP;

      -- ----------------------------------------------------------
      -- 3c. 跨部门依赖（v4.0）— GOAL_SETTING 周期中部分员工
      -- ----------------------------------------------------------
      IF c_status = 'GOAL_SETTING' AND v_emp_count <= 5 THEN
        -- 找一个不同部门的员工作为依赖目标
        SELECT ud2.user_id INTO v_user_id
          FROM corp_hr.user_departments ud2
          WHERE ud2.organization_id = v_org.id AND ud2.user_id != v_emp.user_id
            AND ud2.department_id != (SELECT department_id FROM corp_hr.user_departments WHERE user_id = v_emp.user_id LIMIT 1)
          LIMIT 1;
        IF v_user_id IS NOT NULL THEN
          INSERT INTO platform_performance.kpi_dependency
            (id, source_assignment_id, target_user_id, description, status, created_at, updated_at)
          VALUES (gen_random_uuid(), v_assign_id, v_user_id,
            '需要该同事提供数据支持',
            (ARRAY['PENDING','CONFIRMED','REJECTED']::platform_performance."KpiDependencyStatus"[])[1 + (v_emp_count % 3)],
            NOW(), NOW())
          ON CONFLICT DO NOTHING;
        END IF;
      END IF;

      -- ----------------------------------------------------------
      -- 3d. 绩效结果（CONFIRMING / COMPLETED）
      -- ----------------------------------------------------------
      IF c_status IN ('CONFIRMING', 'COMPLETED') THEN
        r_confirm := CASE
          WHEN c_status = 'COMPLETED' THEN 'CONFIRMED'
          WHEN v_emp_count <= 5 THEN 'APPEALED'        -- 5 人申诉中
          WHEN v_emp_count <= 8 THEN 'APPEAL_RESOLVED'  -- 3 人申诉已处理
          ELSE 'PENDING'
        END::platform_performance."ResultConfirmStatus";

        INSERT INTO platform_performance.performance_result
          (id, cycle_id, employee_id, organization_id,
           kpi_score, total_score,
           proposed_grade_code, proposed_grade_name, grade_code, grade_name,
           is_published, published_at, confirm_status,
           appeal_reason, appeal_response, appeal_resolved_at,
           self_overall_comment, manager_overall_comment,
           created_at, updated_at)
        VALUES (gen_random_uuid(), v_cycle_id, v_emp.user_id, v_org.id,
          mgr_s, mgr_s,
          CASE WHEN mgr_s >= 90 THEN 'S' WHEN mgr_s >= 80 THEN 'A' WHEN mgr_s >= 70 THEN 'B' WHEN mgr_s >= 60 THEN 'C' ELSE 'D' END,
          CASE WHEN mgr_s >= 90 THEN '卓越' WHEN mgr_s >= 80 THEN '优秀' WHEN mgr_s >= 70 THEN '良好' WHEN mgr_s >= 60 THEN '待改进' ELSE '不合格' END,
          CASE WHEN mgr_s >= 90 THEN 'S' WHEN mgr_s >= 80 THEN 'A' WHEN mgr_s >= 70 THEN 'B' WHEN mgr_s >= 60 THEN 'C' ELSE 'D' END,
          CASE WHEN mgr_s >= 90 THEN '卓越' WHEN mgr_s >= 80 THEN '优秀' WHEN mgr_s >= 70 THEN '良好' WHEN mgr_s >= 60 THEN '待改进' ELSE '不合格' END,
          true, NOW(), r_confirm,
          CASE WHEN r_confirm IN ('APPEALED', 'APPEAL_RESOLVED') THEN '我认为评分未充分考虑项目交付贡献' ELSE NULL END,
          CASE WHEN r_confirm = 'APPEAL_RESOLVED' THEN '经复核，维持原等级评定' ELSE NULL END,
          CASE WHEN r_confirm = 'APPEAL_RESOLVED' THEN NOW() ELSE NULL END,
          '本周期在核心业务和团队协作方面均有良好表现',
          '该员工表现稳定，建议继续保持',
          NOW(), NOW()) ON CONFLICT DO NOTHING;
      END IF;

      -- ----------------------------------------------------------
      -- 3e. 360 评估 + 任务 + 回答（EVALUATING / CALIBRATING）
      -- ----------------------------------------------------------
      IF c_status IN ('EVALUATING', 'CALIBRATING') AND v_emp_count <= 15 THEN
        v_eval_id := gen_random_uuid();
        INSERT INTO platform_performance.evaluation_360
          (id, cycle_id, target_id, template_id, status, deadline, min_evaluators, created_by, created_at, updated_at)
        VALUES (v_eval_id, v_cycle_id, v_emp.user_id, v_default_tpl_id,
          CASE WHEN v_emp_count <= 8 THEN 'COMPLETED' ELSE 'IN_PROGRESS' END::platform_performance."Evaluation360Status",
          '2026-06-30', 3, v_admin_id, NOW(), NOW()) ON CONFLICT DO NOTHING;

        -- 自评任务
        v_task_id := gen_random_uuid();
        INSERT INTO platform_performance.evaluation_task
          (id, evaluation_id, evaluator_id, relation_type, status, is_anonymous, submitted_at, created_at)
        VALUES (v_task_id, v_eval_id, v_emp.user_id, 'SELF',
          'SUBMITTED', false, NOW(), NOW()) ON CONFLICT DO NOTHING;

        -- 自评回答（3 个维度）
        INSERT INTO platform_performance.evaluation_response (id, task_id, dimension, score, comment)
        VALUES
          (gen_random_uuid(), v_task_id, 'communication', 3.5 + (v_emp_count % 2)::numeric * 0.5, '沟通方面有所进步'),
          (gen_random_uuid(), v_task_id, 'teamwork', 4.0, '团队协作表现良好'),
          (gen_random_uuid(), v_task_id, 'professional', 3.0 + (v_emp_count % 3)::numeric * 0.5, '专业能力持续提升');

        -- 同事互评任务（找 2 个同事）
        k := 0;
        FOR v_peer IN
          SELECT ud2.user_id FROM corp_hr.user_departments ud2
          WHERE ud2.organization_id = v_org.id AND ud2.user_id != v_emp.user_id AND ud2.left_at IS NULL
          ORDER BY ud2.user_id LIMIT 2
        LOOP
          k := k + 1;
          v_task_id := gen_random_uuid();
          INSERT INTO platform_performance.evaluation_task
            (id, evaluation_id, evaluator_id, relation_type, status, is_anonymous, submitted_at, created_at)
          VALUES (v_task_id, v_eval_id, v_peer.user_id,
            CASE WHEN k = 1 THEN 'PEER' ELSE 'CROSS_FUNCTIONAL' END::platform_performance."RelationType",
            CASE WHEN v_emp_count <= 8 THEN 'SUBMITTED' ELSE 'PENDING' END::platform_performance."EvaluationTaskStatus",
            true,
            CASE WHEN v_emp_count <= 8 THEN NOW() ELSE NULL END,
            NOW()) ON CONFLICT DO NOTHING;

          -- 已完成的评估才有回答
          IF v_emp_count <= 8 THEN
            INSERT INTO platform_performance.evaluation_response (id, task_id, dimension, score, comment)
            VALUES
              (gen_random_uuid(), v_task_id, 'communication', 3.0 + (k % 3)::numeric * 0.5, NULL),
              (gen_random_uuid(), v_task_id, 'teamwork', 3.5 + (k % 2)::numeric * 0.5, '协作不错'),
              (gen_random_uuid(), v_task_id, 'professional', 3.0 + (v_emp_count % 2)::numeric, NULL);
          END IF;
        END LOOP;
      END IF;

    END LOOP; -- v_emp
    RAISE NOTICE '  ✅ % (% 人)', c_name, v_emp_count;
    v_emp_count := 0;
  END LOOP; -- j (cycles)

  RAISE NOTICE '============================================================';
  RAISE NOTICE '🎉 演示数据创建完成！';
  RAISE NOTICE '  组织: %', v_org.name;
  RAISE NOTICE '  等级配置: 标准五级制 (绑定所有周期)';
  RAISE NOTICE '  周期: 5 个（GOAL_SETTING → COMPLETED）';
  RAISE NOTICE '  覆盖: KPI/评估/结果/360/战略目标/依赖/申诉';
  RAISE NOTICE '  演示账号: demo.perf1 ~ demo.perf30 / Test1234!';
  RAISE NOTICE '============================================================';
END;
$$;
