-- ========================================
-- ねるとんリストシステム 完全セットアップSQL
-- 実行日: 2025-01-06
-- ========================================

-- ========================================
-- 1. neruton_listsテーブルの作成（新規）
-- ========================================
CREATE TABLE IF NOT EXISTS neruton_lists (
    id INT PRIMARY KEY AUTO_INCREMENT,
    program_id INT NOT NULL COMMENT '番組との紐付け',
    name VARCHAR(255) NOT NULL COMMENT 'ねるとんリスト名',
    box_id VARCHAR(50) NOT NULL COMMENT 'BOXID',
    
    -- 現在（旧ポイント）
    genzai_start INT DEFAULT NULL COMMENT '現在開始値',
    genzai_end INT DEFAULT NULL COMMENT '現在終了値',
    
    -- 定型・号
    teikei INT DEFAULT NULL COMMENT '定型',
    gou INT DEFAULT NULL COMMENT '号',
    
    -- 連続
    renzoku_start INT DEFAULT NULL COMMENT '連続開始',
    renzoku_end INT DEFAULT NULL COMMENT '連続終了',
    
    -- 色選択（JSON配列: [0,2,4]のようにインデックスを保存）
    selected_colors TEXT DEFAULT NULL COMMENT '選択された色のインデックス配列',
    
    -- セレクトボックスの値
    kaiin VARCHAR(10) DEFAULT 'a' COMMENT '会員',
    alm VARCHAR(10) DEFAULT 'a' COMMENT 'ALM',
    yamabiko VARCHAR(10) DEFAULT 'a' COMMENT '山彦',
    yoyaku VARCHAR(10) DEFAULT 'a' COMMENT '予約',
    waho VARCHAR(10) DEFAULT 'a' COMMENT '話放',
    app VARCHAR(10) DEFAULT 'a' COMMENT 'APP',
    
    -- IDリスト（JSONフォーマット）
    -- 例: {"character_ids": ["10432835", "10432836", "10432837"]}
    id_list TEXT DEFAULT NULL COMMENT 'IDリスト（JSON形式）',
    
    -- APIキー管理
    api_key_id INT DEFAULT NULL COMMENT '割り当てられたAPIキーID',
    
    -- タイムスタンプ
    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,
    FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ========================================
-- 2. api_keysテーブルへのカラム追加（既存テーブル更新）
-- ========================================
-- assigned_neruton_list_idカラムを追加（ねるとんリスト専用割り当て）
ALTER TABLE api_keys 
ADD COLUMN IF NOT EXISTS assigned_neruton_list_id INT DEFAULT NULL 
    COMMENT 'ねるとんリスト専用割り当て（NULLの場合は通常システム用）'
    AFTER assigned_box_id;

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

-- ========================================
-- 3. 確認用ビュー（オプション）
-- ========================================
CREATE OR REPLACE VIEW v_neruton_list_status AS
SELECT 
    nl.id,
    nl.name,
    nl.box_id,
    p.name as program_name,
    ak.key_value as api_key,
    ak.is_active as api_key_active,
    COUNT(DISTINCT pi.character_id) as registered_id_count,
    nl.created_at,
    nl.updated_at
FROM neruton_lists nl
LEFT JOIN programs p ON nl.program_id = p.id
LEFT JOIN api_keys ak ON nl.api_key_id = ak.id
LEFT JOIN program_ids pi ON nl.box_id = pi.box_id AND nl.program_id = pi.program_id
GROUP BY nl.id
ORDER BY nl.created_at DESC;

-- ========================================
-- 4. テーブル存在確認
-- ========================================
-- 以下のコマンドで確認できます：
-- SHOW TABLES LIKE 'neruton%';
-- DESCRIBE neruton_lists;
-- SELECT * FROM v_neruton_list_status;

-- ========================================
-- 5. サンプルデータ（テスト用 - 必要に応じて）
-- ========================================
-- INSERT INTO neruton_lists (program_id, name, box_id, genzai_start, genzai_end, selected_colors, id_list)
-- VALUES (
--     1, 
--     'テストねるとんリスト', 
--     'TEST001',
--     100,
--     200,
--     '[0, 2, 4]',
--     '{"character_ids": ["10432835", "10432836"]}'
-- );

-- ========================================
-- 6. 削除用SQL（必要な場合のみ）
-- ========================================
-- DROP VIEW IF EXISTS v_neruton_list_status;
-- DROP TABLE IF EXISTS neruton_lists;
-- ALTER TABLE api_keys DROP COLUMN IF EXISTS assigned_neruton_list_id;