-- ねるとんリストテーブルにAPIキー関連カラムを追加
ALTER TABLE neruton_lists 
ADD COLUMN api_key_id INT DEFAULT NULL AFTER id_list,
ADD FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL;

-- APIキー使用状況をより詳細に管理するため、api_keysテーブルに追加カラム
ALTER TABLE api_keys 
ADD COLUMN IF NOT EXISTS assigned_box_id VARCHAR(50) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS assigned_neruton_list_id INT DEFAULT NULL,
ADD INDEX idx_api_keys_box_id (assigned_box_id),
ADD INDEX idx_api_keys_neruton_list_id (assigned_neruton_list_id);

-- APIキー割り当て管理用のビュー
CREATE OR REPLACE VIEW v_neruton_api_assignments AS
SELECT 
    nl.id as neruton_list_id,
    nl.name as neruton_list_name,
    nl.box_id,
    nl.program_id,
    ak.id as api_key_id,
    ak.key_value,
    ak.is_active,
    ak.is_used,
    COUNT(DISTINCT pi.character_id) as character_count
FROM neruton_lists nl
LEFT JOIN api_keys ak ON nl.api_key_id = ak.id
LEFT JOIN program_ids pi ON nl.program_id = pi.program_id AND nl.box_id = pi.box_id
GROUP BY nl.id, ak.id;

-- APIキー自動割り当て用のストアドプロシージャ（MySQL用）
DELIMITER //
CREATE PROCEDURE assign_api_key_to_neruton_list(
    IN p_neruton_list_id INT
)
BEGIN
    DECLARE v_api_key_id INT;
    DECLARE v_box_id VARCHAR(50);
    
    -- ねるとんリストのBOX IDを取得
    SELECT box_id INTO v_box_id 
    FROM neruton_lists 
    WHERE id = p_neruton_list_id;
    
    -- 既に割り当てられているAPIキーがあるか確認
    SELECT api_key_id INTO v_api_key_id
    FROM neruton_lists
    WHERE id = p_neruton_list_id;
    
    IF v_api_key_id IS NULL THEN
        -- 未使用で有効なAPIキーを取得
        SELECT id INTO v_api_key_id
        FROM api_keys
        WHERE is_active = 1 
          AND is_used = 0
          AND assigned_box_id IS NULL
        ORDER BY id
        LIMIT 1;
        
        IF v_api_key_id IS NOT NULL THEN
            -- APIキーを割り当て
            UPDATE neruton_lists 
            SET api_key_id = v_api_key_id
            WHERE id = p_neruton_list_id;
            
            -- APIキー側も更新
            UPDATE api_keys
            SET is_used = 1,
                assigned_box_id = v_box_id,
                assigned_neruton_list_id = p_neruton_list_id
            WHERE id = v_api_key_id;
            
            SELECT 'SUCCESS' as status, v_api_key_id as assigned_key_id;
        ELSE
            SELECT 'NO_AVAILABLE_KEY' as status, NULL as assigned_key_id;
        END IF;
    ELSE
        SELECT 'ALREADY_ASSIGNED' as status, v_api_key_id as assigned_key_id;
    END IF;
END//
DELIMITER ;

-- APIキー解放用のストアドプロシージャ
DELIMITER //
CREATE PROCEDURE release_api_key_from_neruton_list(
    IN p_neruton_list_id INT
)
BEGIN
    DECLARE v_api_key_id INT;
    
    -- 現在割り当てられているAPIキーを取得
    SELECT api_key_id INTO v_api_key_id
    FROM neruton_lists
    WHERE id = p_neruton_list_id;
    
    IF v_api_key_id IS NOT NULL THEN
        -- ねるとんリストからAPIキーを解除
        UPDATE neruton_lists 
        SET api_key_id = NULL
        WHERE id = p_neruton_list_id;
        
        -- APIキー側も解放
        UPDATE api_keys
        SET is_used = 0,
            assigned_box_id = NULL,
            assigned_neruton_list_id = NULL
        WHERE id = v_api_key_id;
        
        SELECT 'SUCCESS' as status;
    ELSE
        SELECT 'NO_KEY_ASSIGNED' as status;
    END IF;
END//
DELIMITER ;