lms-be/prisma/migrations/20260609162000_add_week6_autoware_course/migration.sql

INSERT INTO "course_weeks" (
  "course_id",
  "week",
  "title",
  "tags",
  "progress",
  "state",
  "duration",
  "github",
  "overview",
  "deliverable",
  "updated_at"
)
SELECT
  course."id",
  6,
  'Autoware 기반 자율주행',
  ARRAY['Autoware','진행예정']::TEXT[],
  0,
  'locked',
  '10-12시간',
  '미제출',
  'Autoware 모듈을 실행하고 시각화 화면에서 초기 위치와 목표 포즈를 설정해 차량이 경로를 따라 주행하는 흐름을 검증합니다.',
  '초기 위치/목표 포즈 설정값, 주행 로그, 경로 계획 및 제어 결과를 정리해 제출',
  CURRENT_TIMESTAMP
FROM "courses" AS course
ON CONFLICT ("course_id", "week") DO UPDATE
SET
  "title" = EXCLUDED."title",
  "tags" = EXCLUDED."tags",
  "progress" = EXCLUDED."progress",
  "state" = EXCLUDED."state",
  "duration" = EXCLUDED."duration",
  "github" = EXCLUDED."github",
  "overview" = EXCLUDED."overview",
  "deliverable" = EXCLUDED."deliverable",
  "updated_at" = CURRENT_TIMESTAMP;

WITH target_weeks AS (
  SELECT w."id" AS week_id
  FROM "course_weeks" w
  WHERE w."week" = 6
)
INSERT INTO "course_week_topics" ("week_id", "sort_order", "title", "detail")
SELECT target_weeks.week_id, topic.sort_order, topic.title, topic.detail
FROM target_weeks
CROSS JOIN (
  VALUES
    (1, 'Autoware 실행 구조', '차량, 센서, 지도, 위치추정, 인지, 계획, 제어 모듈의 실행 순서와 ROS2 토픽 흐름을 확인합니다.'),
    (2, '초기 위치 설정', '시각화 화면에서 초기 위치를 지정하고 Localization 상태와 차량 pose 갱신을 검증합니다.'),
    (3, '목표 포즈와 경로 계획', '목표 포즈를 지정해 mission planning과 behavior planning 결과 경로를 확인합니다.'),
    (4, '제어 명령과 주행 검증', '계획된 경로를 따라 차량이 움직이는지 제어 명령, 상태 토픽, 로그를 기준으로 점검합니다.')
) AS topic(sort_order, title, detail)
ON CONFLICT ("week_id", "sort_order") DO UPDATE
SET
  "title" = EXCLUDED."title",
  "detail" = EXCLUDED."detail";

WITH target_weeks AS (
  SELECT w."id" AS week_id
  FROM "course_weeks" w
  WHERE w."week" = 6
)
INSERT INTO "course_week_practices" ("week_id", "sort_order", "title", "description")
SELECT target_weeks.week_id, practice.sort_order, practice.title, practice.description
FROM target_weeks
CROSS JOIN (
  VALUES
    (1, 'Autoware 모듈 실행', '주차에 등록된 Autoware 모듈을 순서대로 실행하고 컨테이너 상태를 확인합니다.'),
    (2, '초기 위치 지정', '시각화에서 초기 위치를 지정하고 차량 위치가 지도 좌표계에 정상 반영되는지 확인합니다.'),
    (3, '목표 포즈 지정', '목표 지점을 설정하고 생성된 route와 주행 상태를 비교합니다.'),
    (4, '주행 결과 제출', '수정한 소스, 실행 로그, 주행 검증 결과를 제출합니다.')
) AS practice(sort_order, title, description)
ON CONFLICT ("week_id", "sort_order") DO UPDATE
SET
  "title" = EXCLUDED."title",
  "description" = EXCLUDED."description";

WITH week_pairs AS (
  SELECT
    source_week."id" AS source_week_id,
    target_week."id" AS target_week_id
  FROM "course_weeks" source_week
  JOIN "course_weeks" target_week
    ON target_week."course_id" = source_week."course_id"
   AND target_week."week" = 6
  WHERE source_week."week" = 3
)
INSERT INTO "course_week_modules" (
  "week_id",
  "sort_order",
  "category",
  "name",
  "folder_name",
  "template_repository_url",
  "docker_image",
  "updated_at"
)
SELECT
  week_pairs.target_week_id,
  source_module."sort_order",
  source_module."category",
  source_module."name",
  source_module."folder_name",
  source_module."template_repository_url",
  source_module."docker_image",
  CURRENT_TIMESTAMP
FROM week_pairs
JOIN "course_week_modules" source_module ON source_module."week_id" = week_pairs.source_week_id
ON CONFLICT ("week_id", "folder_name") DO UPDATE
SET
  "sort_order" = EXCLUDED."sort_order",
  "category" = EXCLUDED."category",
  "name" = EXCLUDED."name",
  "template_repository_url" = EXCLUDED."template_repository_url",
  "docker_image" = EXCLUDED."docker_image",
  "updated_at" = CURRENT_TIMESTAMP;

INSERT INTO "course_environment_profiles" (
  "course_id",
  "week",
  "platform_key",
  "profile_type",
  "name",
  "config",
  "updated_at"
)
SELECT
  course."id",
  6,
  'futuredrive-local-pc',
  'AUTOWARE',
  'Autoware 로컬 자율주행',
  '{
    "type": "autoware",
    "viewer": {
      "type": "autoware",
      "url": "/autoware-viewer/index.html",
      "healthUrl": "http://localhost:8765/health"
    },
    "runtime": {
      "containers": [],
      "steps": []
    },
    "bridge": {
      "name": "autoware-ros-bridge",
      "image": "autoware-ros-bridge",
      "port": 8765,
      "env": {
        "ROBOT_STACK": "autoware",
        "MAP_POINT_STRIDE": "8",
        "MAP_POINT_LIMIT": "80000",
        "AUTOWARE_ROUTE_INPUT": "rviz_topic",
        "INITIAL_POSE_TOPIC": "/initialpose",
        "GOAL_POSE_TOPIC": "/planning/mission_planning/goal",
        "VEHICLE_POSE_TOPIC": "/localization/kinematic_state",
        "TRAJECTORY_TOPIC": "/planning/scenario_planning/trajectory",
        "ROUTE_TOPIC": "/planning/mission_planning/route"
      }
    }
  }'::jsonb,
  CURRENT_TIMESTAMP
FROM "courses" AS course
ON CONFLICT ("course_id", "week", "platform_key") DO UPDATE
SET
  "profile_type" = EXCLUDED."profile_type",
  "name" = EXCLUDED."name",
  "config" = EXCLUDED."config",
  "enabled" = true,
  "updated_at" = CURRENT_TIMESTAMP;

UPDATE "course_student_progress"
SET
  "total_weeks" = GREATEST("total_weeks", 6),
  "updated_at" = CURRENT_TIMESTAMP;