-- ⚠️ 重要: 既存のspaice_auto_ress_systemと共存するための安全な変更
-- 既存テーブルは変更せず、新規追加のみ行う

-- ========================================
-- 1. 新規テーブル作成（既存システムに影響なし）
-- ========================================
CREATE TABLE IF NOT EXISTS neruton_lists (
    id INT PRIMARY KEY AUTO_INCREMENT,
    program_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    box_id VARCHAR(50) NOT NULL,
    genzai_start INT,
    genzai_end INT,
    teikei INT,
    gou INT,
    renzoku_start INT,
    renzoku_end INT,
    selected_colors TEXT,
    kaiin VARCHAR(10) DEFAULT 'a',
    alm VARCHAR(10) DEFAULT 'a',
    yamabiko VARCHAR(10) DEFAULT 'a',
    yoyaku VARCHAR(10) DEFAULT 'a',
    waho VARCHAR(10) DEFAULT 'a',
    app VARCHAR(10) DEFAULT 'a',
    id_list TEXT,
    api_key_id INT DEFAULT NULL,  -- APIキーとの関連（オプション）
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_neruton_lists_program_id ON neruton_lists(program_id);
CREATE INDEX idx_neruton_lists_box_id ON neruton_lists(box_id);

-- ========================================
-- 2. api_keysテーブルへの追加カラム（既存動作に影響なし）
-- NULL許可なので既存レコードに影響しない
-- ========================================
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);

-- ========================================
-- 3. 互換性確認用のビュー
-- ========================================
CREATE OR REPLACE VIEW v_api_key_usage AS
SELECT 
    ak.id,
    ak.key_value,
    ak.is_active,
    ak.is_used,
    CASE 
        WHEN ak.assigned_neruton_list_id IS NOT NULL THEN 'ねるとんリスト用'
        WHEN ak.is_used = 1 THEN '通常システム使用中'
        ELSE '未使用'
    END as usage_type,
    nl.name as neruton_list_name,
    nl.box_id as neruton_box_id
FROM api_keys ak
LEFT JOIN neruton_lists nl ON ak.assigned_neruton_list_id = nl.id;

-- ========================================
-- 4. 安全なAPIキー取得関数（ストアドファンクション）
-- ========================================
DELIMITER //
CREATE FUNCTION get_available_api_key_for_neruton()
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_api_key_id INT;
    
    -- ねるとんリスト専用でない、かつ未使用のAPIキーを探す
    SELECT id INTO v_api_key_id
    FROM api_keys
    WHERE is_active = 1 
      AND is_used = 0
      AND assigned_neruton_list_id IS NULL
    ORDER BY id
    LIMIT 1;
    
    RETURN v_api_key_id;
END//
DELIMITER ;

-- ========================================
-- 5. 既存システムとの共存確認
-- ========================================
-- 既存のprogram_idsテーブルはそのまま使用
-- 新規登録時は従来通りINSERTするだけ
-- APIキーの使い分けは以下の通り：
-- - assigned_neruton_list_id IS NULL: 通常システム用
-- - assigned_neruton_list_id IS NOT NULL: ねるとんリスト専用

-- ========================================
-- 注意事項コメント
-- ========================================
-- ⚠️ 既存のspaice_auto_ress_system.pyへの影響：
-- 1. api_keysテーブルに1カラム追加されるが、NULLなので影響なし
-- 2. 既存のAPIキー取得ロジックは変更不要
-- 3. is_usedフラグは従来通り動作
-- 4. program_idsテーブルは変更なし