-- ========================================
-- ねるとんリストシステム 既存テーブル更新SQL（修正版）
-- 実行日: 2025-01-06
-- 既存のneruton_listsテーブルに不足カラムを追加
-- ========================================

-- ========================================
-- 1. 現在のテーブル構造を確認（実行して確認）
-- ========================================
-- DESCRIBE neruton_lists;
-- DESCRIBE api_keys;

-- ========================================
-- 2. neruton_listsテーブルに不足カラムを追加
-- ========================================

-- api_key_idカラムを追加（存在しない場合のみ）
ALTER TABLE neruton_lists 
ADD COLUMN IF NOT EXISTS api_key_id INT DEFAULT NULL 
    COMMENT '割り当てられたAPIキーID' 
    AFTER id_list;

-- インデックスを追加
CREATE INDEX IF NOT EXISTS idx_neruton_lists_api_key ON neruton_lists(api_key_id);

-- ========================================
-- 3. api_keysテーブルに不足カラムを追加
-- ========================================

-- まず、api_keysテーブルの現在の構造を確認
-- SHOW COLUMNS FROM api_keys;

-- assigned_box_idカラムを追加（存在しない場合のみ）
ALTER TABLE api_keys 
ADD COLUMN IF NOT EXISTS assigned_box_id VARCHAR(50) DEFAULT NULL 
    COMMENT 'ねるとん用BOXID';

-- assigned_neruton_list_idカラムを追加（存在しない場合のみ）
ALTER TABLE api_keys 
ADD COLUMN IF NOT EXISTS assigned_neruton_list_id INT DEFAULT NULL 
    COMMENT 'ねるとんリスト専用割り当て（NULLの場合は通常システム用）';

-- インデックスを追加
CREATE INDEX IF NOT EXISTS idx_api_keys_neruton_list ON api_keys(assigned_neruton_list_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_box_id ON api_keys(assigned_box_id);

-- ========================================
-- 4. テーブル構造の最終確認
-- ========================================
-- 以下のコマンドを実行して、必要なカラムがすべて存在することを確認：
DESCRIBE neruton_lists;
DESCRIBE api_keys;

-- ========================================
-- 5. 必要なカラムが存在するか確認するクエリ
-- ========================================
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME IN ('neruton_lists', 'api_keys')
    AND COLUMN_NAME IN ('api_key_id', 'assigned_box_id', 'assigned_neruton_list_id')
ORDER BY 
    TABLE_NAME, ORDINAL_POSITION;

-- ========================================
-- 6. 既存データの確認
-- ========================================
-- api_key_idがNULLのレコードを確認
SELECT id, name, box_id, api_key_id 
FROM neruton_lists 
WHERE api_key_id IS NULL;

-- 利用可能なAPIキーを確認
SELECT id, key_value, is_used, assigned_to, assigned_box_id, assigned_neruton_list_id
FROM api_keys
WHERE is_active = 1 AND (is_used = 0 OR is_used IS NULL);