-- ============================================================================
-- facility_error_summaries / customer_error_summaries の形を ★ローカルの dwatch_local にだけ★ 先方の新しい DDL に合わせる
--
-- ★★ステージング・本番には絶対に流さないこと★★ — メインDBのスキーマは TeamWorks の領域（CLAUDE.md §2 / §7）。
--   先方は両表を変更済み。これは「ローカルの MySQL 8.4 に同じ形を作る」ためだけのもの。
-- ★出所: ステージングの information_schema（2026-10-29 時点。先方の変更）★
--   - `error_detail`（TEXT NOT NULL）を削除
--   - `error_list_id BIGINT UNSIGNED NOT NULL` を `error_type` の後ろに追加し、`error_lists(id)` への外部キーを張る
--   - 索引名・制約名: `fk_facility_error_summaries_error_list_id` / `fk_customer_error_summaries_error_list_id`
--   ★ON DELETE / ON UPDATE は指定しない（MySQL の既定 = RESTRICT）★ — 先方の規則までは写していない。
-- ★`prisma db push` / `migrate` の代わりにこれを使う★（db push はローカルにだけある列を DROP する。指示書M 禁止事項3・4）。
--
-- ★前提: `20261027_error_lists.sql` を流して error_lists 表があり、行が入っていること★（無いと外部キーで止まる）。
-- ★既存のローカル行の error_list_id は 131 にする★（ローカルの error_lists にある id。手書きの仮置き）。
--   NULL のまま・error_lists に無い値の行だけを 131 に直す（すでに正しい id が入っている行は触らない）。
--
-- 実行方法（どちらか）:
--   1) 推奨: `npx tsx scripts/local-db/apply-error-summaries.ts`
--      （DATABASE_URL が localhost / 127.0.0.1 の dwatch_local を指していなければ接続前に止まる）
--   2) mysql CLI: `"C:\Program Files\MySQL\MySQL Server 8.4\bin\mysql.exe" -h localhost -u root -p < scripts/local-db/20261029_error_summaries.sql`
--      （パスワードは -p の対話入力か MYSQL_PWD で渡す。コマンドラインに書かない）
--
-- ★何度流しても同じ結果になる★: 列・索引・制約は information_schema を見て無いときだけ足し、error_detail は有るときだけ消す
--   （MySQL 8.4 には ADD COLUMN IF NOT EXISTS / DROP COLUMN IF EXISTS が無いため、準備済み文で分岐する）。
-- ★先頭の USE が歯止め★: dwatch_local の無いサーバーではここでエラーになり、以降の文は流れない。
-- ★MySQL の ALTER TABLE は暗黙にコミットされる★ — 途中で止まったら、もう一度流せば残りだけが進む。
-- ============================================================================

USE dwatch_local;

-- ---------------------------------------------------------------------------
-- facility_error_summaries
-- ---------------------------------------------------------------------------

-- 1) 列を NULL 可で足す（NOT NULL で足すと既存行に 0 が入り、外部キーを張れない）
SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'facility_error_summaries' AND COLUMN_NAME = 'error_list_id') = 0,
  'ALTER TABLE facility_error_summaries ADD COLUMN error_list_id BIGINT UNSIGNED NULL DEFAULT NULL AFTER error_type',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;

-- 2) 既存行に有効な id を入れる（NULL・error_lists に無い値だけ）
UPDATE facility_error_summaries s
LEFT JOIN error_lists l ON l.id = s.error_list_id
SET s.error_list_id = 131
WHERE l.id IS NULL;

-- 3) NOT NULL・既定値なしにする（先方の DDL と同じ。何度流しても同じ形）
ALTER TABLE facility_error_summaries MODIFY COLUMN error_list_id BIGINT UNSIGNED NOT NULL;

-- 4) 索引
SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'facility_error_summaries'
      AND INDEX_NAME = 'fk_facility_error_summaries_error_list_id') = 0,
  'ALTER TABLE facility_error_summaries ADD INDEX fk_facility_error_summaries_error_list_id (error_list_id)',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;

-- 5) 外部キー
SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'facility_error_summaries'
      AND CONSTRAINT_NAME = 'fk_facility_error_summaries_error_list_id' AND CONSTRAINT_TYPE = 'FOREIGN KEY') = 0,
  'ALTER TABLE facility_error_summaries ADD CONSTRAINT fk_facility_error_summaries_error_list_id FOREIGN KEY (error_list_id) REFERENCES error_lists (id)',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;

-- 6) error_detail を消す
SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'facility_error_summaries' AND COLUMN_NAME = 'error_detail') = 1,
  'ALTER TABLE facility_error_summaries DROP COLUMN error_detail',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;

-- ---------------------------------------------------------------------------
-- customer_error_summaries（手順は上と同じ）
-- ---------------------------------------------------------------------------

SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customer_error_summaries' AND COLUMN_NAME = 'error_list_id') = 0,
  'ALTER TABLE customer_error_summaries ADD COLUMN error_list_id BIGINT UNSIGNED NULL DEFAULT NULL AFTER error_type',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;

UPDATE customer_error_summaries s
LEFT JOIN error_lists l ON l.id = s.error_list_id
SET s.error_list_id = 131
WHERE l.id IS NULL;

ALTER TABLE customer_error_summaries MODIFY COLUMN error_list_id BIGINT UNSIGNED NOT NULL;

SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customer_error_summaries'
      AND INDEX_NAME = 'fk_customer_error_summaries_error_list_id') = 0,
  'ALTER TABLE customer_error_summaries ADD INDEX fk_customer_error_summaries_error_list_id (error_list_id)',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;

SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'customer_error_summaries'
      AND CONSTRAINT_NAME = 'fk_customer_error_summaries_error_list_id' AND CONSTRAINT_TYPE = 'FOREIGN KEY') = 0,
  'ALTER TABLE customer_error_summaries ADD CONSTRAINT fk_customer_error_summaries_error_list_id FOREIGN KEY (error_list_id) REFERENCES error_lists (id)',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;

SET @dwatch_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customer_error_summaries' AND COLUMN_NAME = 'error_detail') = 1,
  'ALTER TABLE customer_error_summaries DROP COLUMN error_detail',
  'DO 0'
);
PREPARE dwatch_stmt FROM @dwatch_sql;
EXECUTE dwatch_stmt;
DEALLOCATE PREPARE dwatch_stmt;