-- 既存のprogram_idsレコードのAPIキー情報を修復するSQL
-- これを一度実行すれば、過去のレコードも修復されます

-- 1. api_key_idがあるが、api_nameまたはapi_keyがNULLのレコードを修復
UPDATE program_ids p
INNER JOIN api_keys a ON p.api_key_id = a.id
SET p.api_name = a.key_name,
    p.api_key = a.key_value
WHERE p.api_key_id IS NOT NULL
AND (p.api_name IS NULL OR p.api_key IS NULL);

-- 修復されたレコード数を確認
SELECT '修復されたレコード数:' as message, ROW_COUNT() as count;

-- 2. 修復後の状態を確認
SELECT
    COUNT(*) as total_records,
    SUM(CASE WHEN api_key_id IS NOT NULL THEN 1 ELSE 0 END) as with_api_key_id,
    SUM(CASE WHEN api_key_id IS NOT NULL AND api_name IS NOT NULL AND api_key IS NOT NULL THEN 1 ELSE 0 END) as complete_records,
    SUM(CASE WHEN api_key_id IS NOT NULL AND (api_name IS NULL OR api_key IS NULL) THEN 1 ELSE 0 END) as incomplete_records
FROM program_ids;