-- APIキーテーブルの状態を確認するSQL

-- 1. api_keysテーブルの全体状況を確認
SELECT
    id,
    key_name,
    CASE
        WHEN key_value IS NULL THEN 'NULL'
        WHEN key_value = '' THEN '空文字'
        ELSE '値あり'
    END as key_value_status,
    is_used,
    assigned_neruton_list_id,
    is_active
FROM api_keys
ORDER BY id;

-- 2. 未使用のAPIキーを確認
SELECT
    id,
    key_name,
    CASE
        WHEN key_value IS NULL THEN 'NULL'
        WHEN key_value = '' THEN '空文字'
        ELSE LEFT(key_value, 10)
    END as key_value_preview,
    is_used
FROM api_keys
WHERE is_used = 0
ORDER BY id;

-- 3. ねるとんリストに割り当て済みのAPIキーを確認
SELECT
    ak.id,
    ak.key_name,
    CASE
        WHEN ak.key_value IS NULL THEN 'NULL'
        WHEN ak.key_value = '' THEN '空文字'
        ELSE '値あり'
    END as key_value_status,
    nl.id as neruton_list_id,
    nl.name as neruton_list_name
FROM api_keys ak
LEFT JOIN neruton_lists nl ON nl.api_key_id = ak.id
WHERE ak.assigned_neruton_list_id IS NOT NULL
   OR nl.id IS NOT NULL;

-- 4. program_idsテーブルのAPIキー情報を確認
SELECT
    id,
    program_id,
    box_id,
    character_id,
    api_key_id,
    api_name,
    CASE
        WHEN api_key IS NULL THEN 'NULL'
        WHEN api_key = '' THEN '空文字'
        ELSE '値あり'
    END as api_key_status
FROM program_ids
WHERE api_key_id IS NOT NULL
ORDER BY id DESC
LIMIT 10;