-- ============================================================
-- master_items カテゴリ再分類 & weight_pct 追加
-- 各カテゴリごとに合計100%になるよう配点
-- ============================================================

use `C_DB`;

-- 1) weight_pct カラム追加
ALTER TABLE `master_items`
  ADD COLUMN `weight_pct` DECIMAL(4,1) DEFAULT 0.0 COMMENT 'カテゴリ内の進捗パーセンテージ（合計100%）'
  AFTER `estimated_days`;

-- 2) category カラムのCOMMENT更新
ALTER TABLE `master_items`
  MODIFY COLUMN `category` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  NOT NULL COMMENT 'CERT/Python/JavaScript/MySQL/FastAPI/外部API連携/AWS/MCP/PJT企画/PJT設計/PJT開発/テスト';

-- ============================================================
-- CERT (자격증) — 60항목, 합계 100.0%
-- ============================================================
-- 第1章: Total Original Weight = 5.30
UPDATE master_items SET category='CERT_第1章', weight_pct=26.4 WHERE item_id=4;
UPDATE master_items SET category='CERT_第1章', weight_pct=22.6 WHERE item_id=6;
UPDATE master_items SET category='CERT_第1章', weight_pct=30.2 WHERE item_id=8;
UPDATE master_items SET category='CERT_第1章', weight_pct=20.8 WHERE item_id=10;

-- 第2章: Total Original Weight = 5.20
UPDATE master_items SET category='CERT_第2章', weight_pct=26.9 WHERE item_id=12;
UPDATE master_items SET category='CERT_第2章', weight_pct=21.2 WHERE item_id=14;
UPDATE master_items SET category='CERT_第2章', weight_pct=30.8 WHERE item_id=16;
UPDATE master_items SET category='CERT_第2章', weight_pct=21.1 WHERE item_id=18;

-- 第3章: Total Original Weight = 4.70
UPDATE master_items SET category='CERT_第3章', weight_pct=53.2 WHERE item_id=20;
UPDATE master_items SET category='CERT_第3章', weight_pct=46.8 WHERE item_id=22;

-- 第4章: Total Original Weight = 2.70
UPDATE master_items SET category='CERT_第4章', weight_pct=51.9 WHERE item_id=34;
UPDATE master_items SET category='CERT_第4章', weight_pct=48.1 WHERE item_id=36;

-- 第5章: Total Original Weight = 3.80
UPDATE master_items SET category='CERT_第5章', weight_pct=52.6 WHERE item_id=38;
UPDATE master_items SET category='CERT_第5章', weight_pct=47.4 WHERE item_id=40;

-- 第6章: Total Original Weight = 2.50
UPDATE master_items SET category='CERT_第6章', weight_pct=56.0 WHERE item_id=42;
UPDATE master_items SET category='CERT_第6章', weight_pct=44.0 WHERE item_id=44;

-- 第7章: Total Original Weight = 6.20
UPDATE master_items SET category='CERT_第7章', weight_pct=22.6 WHERE item_id=46;
UPDATE master_items SET category='CERT_第7章', weight_pct=19.4 WHERE item_id=48;
UPDATE master_items SET category='CERT_第7章', weight_pct=25.8 WHERE item_id=50;
UPDATE master_items SET category='CERT_第7章', weight_pct=32.2 WHERE item_id=52;

-- 第8章: Total Original Weight = 2.20
UPDATE master_items SET category='CERT_第8章', weight_pct=54.5 WHERE item_id=64;
UPDATE master_items SET category='CERT_第8章', weight_pct=45.5 WHERE item_id=66;

-- 第9章: Total Original Weight = 8.30
UPDATE master_items SET category='CERT_第9章', weight_pct=30.1 WHERE item_id=68;
UPDATE master_items SET category='CERT_第9章', weight_pct=24.1 WHERE item_id=70;
UPDATE master_items SET category='CERT_第9章', weight_pct=24.1 WHERE item_id=72;
UPDATE master_items SET category='CERT_第9章', weight_pct=21.7 WHERE item_id=74;

-- 第10章: Total Original Weight = 8.60
UPDATE master_items SET category='CERT_第10章', weight_pct=26.7 WHERE item_id=76;
UPDATE master_items SET category='CERT_第10章', weight_pct=23.3 WHERE item_id=78;
UPDATE master_items SET category='CERT_第10章', weight_pct=26.7 WHERE item_id=80;
UPDATE master_items SET category='CERT_第10章', weight_pct=23.3 WHERE item_id=82;

-- 第11章: Total Original Weight = 8.50
UPDATE master_items SET category='CERT_第11章', weight_pct=29.4 WHERE item_id=94;
UPDATE master_items SET category='CERT_第11章', weight_pct=23.5 WHERE item_id=96;
UPDATE master_items SET category='CERT_第11章', weight_pct=27.1 WHERE item_id=98;
UPDATE master_items SET category='CERT_第11章', weight_pct=20.0 WHERE item_id=100;

-- 第12章: Total Original Weight = 2.90
UPDATE master_items SET category='CERT_第12章', weight_pct=55.2 WHERE item_id=102;
UPDATE master_items SET category='CERT_第12章', weight_pct=44.8 WHERE item_id=104;

-- 第13章: Total Original Weight = 6.50
UPDATE master_items SET category='CERT_第13章', weight_pct=24.6 WHERE item_id=106;
UPDATE master_items SET category='CERT_第13章', weight_pct=20.0 WHERE item_id=108;
UPDATE master_items SET category='CERT_第13章', weight_pct=24.6 WHERE item_id=110;
UPDATE master_items SET category='CERT_第13章', weight_pct=30.8 WHERE item_id=112;

-- 第14章: Total Original Weight = 3.80
UPDATE master_items SET category='CERT_第14章', weight_pct=52.6 WHERE item_id=124;
UPDATE master_items SET category='CERT_第14章', weight_pct=47.4 WHERE item_id=126;

-- 第15章: Total Original Weight = 2.60
UPDATE master_items SET category='CERT_第15章', weight_pct=53.8 WHERE item_id=128;
UPDATE master_items SET category='CERT_第15章', weight_pct=46.2 WHERE item_id=130;

-- 第16章: Total Original Weight = 2.60
UPDATE master_items SET category='CERT_第16章', weight_pct=53.8 WHERE item_id=132;
UPDATE master_items SET category='CERT_第16章', weight_pct=46.2 WHERE item_id=134;

-- 第17章: Total Original Weight = 2.60
UPDATE master_items SET category='CERT_第17章', weight_pct=53.8 WHERE item_id=136;
UPDATE master_items SET category='CERT_第17章', weight_pct=46.2 WHERE item_id=138;

-- 第18章: Total Original Weight = 3.60
UPDATE master_items SET category='CERT_第18章', weight_pct=44.4 WHERE item_id=140;
UPDATE master_items SET category='CERT_第18章', weight_pct=55.6 WHERE item_id=142;

-- 第19章: Total Original Weight = 2.60
UPDATE master_items SET category='CERT_第19章', weight_pct=53.8 WHERE item_id=154;
UPDATE master_items SET category='CERT_第19章', weight_pct=46.2 WHERE item_id=156;

-- 第20章: Total Original Weight = 2.90
UPDATE master_items SET category='CERT_第20章', weight_pct=55.2 WHERE item_id=158;
UPDATE master_items SET category='CERT_第20章', weight_pct=44.8 WHERE item_id=160;

-- 第21章: Total Original Weight = 2.60
UPDATE master_items SET category='CERT_第21章', weight_pct=53.8 WHERE item_id=162;
UPDATE master_items SET category='CERT_第21章', weight_pct=46.2 WHERE item_id=164;

-- 第22章: Total Original Weight = 9.30
UPDATE master_items SET category='CERT_第22章', weight_pct=26.9 WHERE item_id=166;
UPDATE master_items SET category='CERT_第22章', weight_pct=23.7 WHERE item_id=168;
UPDATE master_items SET category='CERT_第22章', weight_pct=26.9 WHERE item_id=170;
UPDATE master_items SET category='CERT_第22章', weight_pct=22.5 WHERE item_id=172;

-- ============================================================
-- Python — 40항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='Python', weight_pct=1.3 WHERE item_id=1;
UPDATE master_items SET category='Python', weight_pct=1.0 WHERE item_id=2;
UPDATE master_items SET category='Python', weight_pct=1.5 WHERE item_id=3;
UPDATE master_items SET category='Python', weight_pct=1.2 WHERE item_id=5;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=7;
UPDATE master_items SET category='Python', weight_pct=1.6 WHERE item_id=9;
UPDATE master_items SET category='Python', weight_pct=1.5 WHERE item_id=11;
UPDATE master_items SET category='Python', weight_pct=1.2 WHERE item_id=13;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=15;
UPDATE master_items SET category='Python', weight_pct=1.6 WHERE item_id=17;
UPDATE master_items SET category='Python', weight_pct=2.6 WHERE item_id=19;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=21;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=23;
UPDATE master_items SET category='Python', weight_pct=1.6 WHERE item_id=24;
UPDATE master_items SET category='Python', weight_pct=3.2 WHERE item_id=25;
UPDATE master_items SET category='Python', weight_pct=2.3 WHERE item_id=26;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=27;
UPDATE master_items SET category='Python', weight_pct=1.6 WHERE item_id=28;
UPDATE master_items SET category='Python', weight_pct=3.2 WHERE item_id=29;
UPDATE master_items SET category='Python', weight_pct=2.3 WHERE item_id=30;
UPDATE master_items SET category='Python', weight_pct=3.2 WHERE item_id=31;
UPDATE master_items SET category='Python', weight_pct=2.3 WHERE item_id=32;
UPDATE master_items SET category='Python', weight_pct=3.8 WHERE item_id=33;
UPDATE master_items SET category='Python', weight_pct=2.7 WHERE item_id=35;
UPDATE master_items SET category='Python', weight_pct=3.8 WHERE item_id=37;
UPDATE master_items SET category='Python', weight_pct=2.7 WHERE item_id=39;
UPDATE master_items SET category='Python', weight_pct=2.5 WHERE item_id=41;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=43;
UPDATE master_items SET category='Python', weight_pct=2.7 WHERE item_id=45;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=47;
UPDATE master_items SET category='Python', weight_pct=3.8 WHERE item_id=49;
UPDATE master_items SET category='Python', weight_pct=3.0 WHERE item_id=51;
UPDATE master_items SET category='Python', weight_pct=4.5 WHERE item_id=53;
UPDATE master_items SET category='Python', weight_pct=3.5 WHERE item_id=54;
UPDATE master_items SET category='Python', weight_pct=5.0 WHERE item_id=55;
UPDATE master_items SET category='Python', weight_pct=3.8 WHERE item_id=56;
UPDATE master_items SET category='Python', weight_pct=3.8 WHERE item_id=57;
UPDATE master_items SET category='Python', weight_pct=2.7 WHERE item_id=58;
UPDATE master_items SET category='Python', weight_pct=2.5 WHERE item_id=59;
UPDATE master_items SET category='Python', weight_pct=2.0 WHERE item_id=60;

-- ============================================================
-- MySQL Database — 12항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='MySQL', weight_pct=5.5 WHERE item_id=61;
UPDATE master_items SET category='MySQL', weight_pct=5.0 WHERE item_id=62;
UPDATE master_items SET category='MySQL', weight_pct=7.0 WHERE item_id=63;
UPDATE master_items SET category='MySQL', weight_pct=6.0 WHERE item_id=65;
UPDATE master_items SET category='MySQL', weight_pct=8.0 WHERE item_id=67;
UPDATE master_items SET category='MySQL', weight_pct=7.0 WHERE item_id=69;
UPDATE master_items SET category='MySQL', weight_pct=10.0 WHERE item_id=71;
UPDATE master_items SET category='MySQL', weight_pct=9.0 WHERE item_id=73;
UPDATE master_items SET category='MySQL', weight_pct=10.0 WHERE item_id=75;
UPDATE master_items SET category='MySQL', weight_pct=8.5 WHERE item_id=77;
UPDATE master_items SET category='MySQL', weight_pct=13.0 WHERE item_id=79;
UPDATE master_items SET category='MySQL', weight_pct=11.0 WHERE item_id=81;

-- ============================================================
-- FastAPI — 8항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='FastAPI', weight_pct=8.0 WHERE item_id=83;
UPDATE master_items SET category='FastAPI', weight_pct=7.0 WHERE item_id=84;
UPDATE master_items SET category='FastAPI', weight_pct=12.0 WHERE item_id=85;
UPDATE master_items SET category='FastAPI', weight_pct=10.0 WHERE item_id=86;
UPDATE master_items SET category='FastAPI', weight_pct=18.0 WHERE item_id=87;
UPDATE master_items SET category='FastAPI', weight_pct=15.0 WHERE item_id=88;
UPDATE master_items SET category='FastAPI', weight_pct=18.0 WHERE item_id=89;
UPDATE master_items SET category='FastAPI', weight_pct=12.0 WHERE item_id=90;

-- ============================================================
-- JavaScript — 16항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='JavaScript', weight_pct=4.0 WHERE item_id=91;
UPDATE master_items SET category='JavaScript', weight_pct=3.5 WHERE item_id=92;
UPDATE master_items SET category='JavaScript', weight_pct=4.5 WHERE item_id=93;
UPDATE master_items SET category='JavaScript', weight_pct=4.0 WHERE item_id=95;
UPDATE master_items SET category='JavaScript', weight_pct=7.0 WHERE item_id=97;
UPDATE master_items SET category='JavaScript', weight_pct=6.0 WHERE item_id=99;
UPDATE master_items SET category='JavaScript', weight_pct=7.0 WHERE item_id=101;
UPDATE master_items SET category='JavaScript', weight_pct=6.0 WHERE item_id=103;
UPDATE master_items SET category='JavaScript', weight_pct=8.0 WHERE item_id=105;
UPDATE master_items SET category='JavaScript', weight_pct=7.0 WHERE item_id=107;
UPDATE master_items SET category='JavaScript', weight_pct=9.0 WHERE item_id=109;
UPDATE master_items SET category='JavaScript', weight_pct=7.5 WHERE item_id=111;
UPDATE master_items SET category='JavaScript', weight_pct=9.0 WHERE item_id=113;
UPDATE master_items SET category='JavaScript', weight_pct=8.0 WHERE item_id=114;
UPDATE master_items SET category='JavaScript', weight_pct=5.5 WHERE item_id=119;
UPDATE master_items SET category='JavaScript', weight_pct=4.0 WHERE item_id=120;

-- ============================================================
-- 外部API連携 — 4항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='外部API連携', weight_pct=22.0 WHERE item_id=115;
UPDATE master_items SET category='外部API連携', weight_pct=20.0 WHERE item_id=116;
UPDATE master_items SET category='外部API連携', weight_pct=32.0 WHERE item_id=117;
UPDATE master_items SET category='外部API連携', weight_pct=26.0 WHERE item_id=118;

-- ============================================================
-- AWS — 8항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='AWS', weight_pct=8.0 WHERE item_id=121;
UPDATE master_items SET category='AWS', weight_pct=10.0 WHERE item_id=122;
UPDATE master_items SET category='AWS', weight_pct=8.0 WHERE item_id=123;
UPDATE master_items SET category='AWS', weight_pct=7.0 WHERE item_id=125;
UPDATE master_items SET category='AWS', weight_pct=18.0 WHERE item_id=127;
UPDATE master_items SET category='AWS', weight_pct=16.0 WHERE item_id=129;
UPDATE master_items SET category='AWS', weight_pct=16.0 WHERE item_id=131;
UPDATE master_items SET category='AWS', weight_pct=17.0 WHERE item_id=133;

-- ============================================================
-- MCP — 4항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='MCP', weight_pct=20.0 WHERE item_id=135;
UPDATE master_items SET category='MCP', weight_pct=25.0 WHERE item_id=137;
UPDATE master_items SET category='MCP', weight_pct=22.0 WHERE item_id=139;
UPDATE master_items SET category='MCP', weight_pct=33.0 WHERE item_id=141;

-- ============================================================
-- PJT企画 — 4항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='PJT企画', weight_pct=22.0 WHERE item_id=143;
UPDATE master_items SET category='PJT企画', weight_pct=28.0 WHERE item_id=144;
UPDATE master_items SET category='PJT企画', weight_pct=20.0 WHERE item_id=179;
UPDATE master_items SET category='PJT企画', weight_pct=30.0 WHERE item_id=180;

-- ============================================================
-- PJT設計 — 6항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='PJT設計', weight_pct=16.0 WHERE item_id=145;
UPDATE master_items SET category='PJT設計', weight_pct=14.0 WHERE item_id=146;
UPDATE master_items SET category='PJT設計', weight_pct=20.0 WHERE item_id=147;
UPDATE master_items SET category='PJT設計', weight_pct=18.0 WHERE item_id=148;
UPDATE master_items SET category='PJT設計', weight_pct=18.0 WHERE item_id=149;
UPDATE master_items SET category='PJT設計', weight_pct=14.0 WHERE item_id=176;

-- ============================================================
-- PJT開発 — 12항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='PJT開発', weight_pct=9.0 WHERE item_id=150;
UPDATE master_items SET category='PJT開発', weight_pct=8.0 WHERE item_id=151;
UPDATE master_items SET category='PJT開発', weight_pct=10.0 WHERE item_id=152;
UPDATE master_items SET category='PJT開発', weight_pct=11.0 WHERE item_id=153;
UPDATE master_items SET category='PJT開発', weight_pct=9.0 WHERE item_id=155;
UPDATE master_items SET category='PJT開発', weight_pct=7.0 WHERE item_id=157;
UPDATE master_items SET category='PJT開発', weight_pct=10.0 WHERE item_id=159;
UPDATE master_items SET category='PJT開発', weight_pct=8.0 WHERE item_id=161;
UPDATE master_items SET category='PJT開発', weight_pct=13.0 WHERE item_id=163;
UPDATE master_items SET category='PJT開発', weight_pct=6.0 WHERE item_id=174;
UPDATE master_items SET category='PJT開発', weight_pct=5.0 WHERE item_id=175;
UPDATE master_items SET category='PJT開発', weight_pct=4.0 WHERE item_id=177;

-- ============================================================
-- テスト — 6항목, 합계 100.0%
-- ============================================================
UPDATE master_items SET category='テスト', weight_pct=14.0 WHERE item_id=165;
UPDATE master_items SET category='テスト', weight_pct=14.0 WHERE item_id=167;
UPDATE master_items SET category='テスト', weight_pct=18.0 WHERE item_id=169;
UPDATE master_items SET category='テスト', weight_pct=18.0 WHERE item_id=171;
UPDATE master_items SET category='テスト', weight_pct=12.0 WHERE item_id=173;
UPDATE master_items SET category='テスト', weight_pct=24.0 WHERE item_id=178;

-- ============================================================
-- 検証クエリ: 各カテゴリの合計が100%になることを確認
-- ============================================================
SELECT category, COUNT(*) AS item_count, SUM(weight_pct) AS total_pct
FROM master_items
GROUP BY category
ORDER BY FIELD(category,'CERT','Python','JavaScript','MySQL','FastAPI','外部API連携','AWS','MCP','PJT企画','PJT設計','PJT開発','テスト');


ALTER TABLE `user_curriculum`
  ADD COLUMN `block_id` INT DEFAULT NULL AFTER `item_id`,
  ADD COLUMN `category` VARCHAR(20) DEFAULT NULL AFTER `block_id`,
  ADD COLUMN `weight_pct` DECIMAL(4,1) DEFAULT 0.0 AFTER `category`;
  


-- ============================================================
-- ユーザー別カテゴリ進捗テーブル (Goalskill_DB)
-- デバッグ日誌完了時にカテゴリ別 total_pct を記録
-- ============================================================
USE `Goalskill_DB`;

CREATE TABLE IF NOT EXISTS `user_category_progress` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `session_id`  VARCHAR(100) NOT NULL,
  `category`    VARCHAR(20)  NOT NULL,
  `total_pct`   DECIMAL(5,1) NOT NULL DEFAULT 0.0,
  `updated_at`  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_session_category` (`session_id`, `category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
