-- SPAICE自動返信システム データベーステーブル作成SQL
-- データベース: hr02a_spaice_auto_ress_system

-- 番組マスタテーブル
CREATE TABLE IF NOT EXISTS programs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    url VARCHAR(500) NOT NULL,
    program_id VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 番組ID紐づけテーブル（boxid + キャラIDの組み合わせ）
CREATE TABLE IF NOT EXISTS program_ids (
    id INT PRIMARY KEY AUTO_INCREMENT,
    program_id INT NOT NULL,
    box_id VARCHAR(50) NOT NULL,
    character_id VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE,
    UNIQUE KEY unique_box_character_id (box_id, character_id),
    INDEX idx_program_id (program_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- やり取り回数別プロンプトテーブル
CREATE TABLE IF NOT EXISTS program_prompts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    program_id_link INT NOT NULL,
    turn_number INT NOT NULL,
    prompt_text TEXT NOT NULL,
    photonum INT DEFAULT NULL COMMENT '写真送信番号',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (program_id_link) REFERENCES program_ids(id) ON DELETE CASCADE,
    UNIQUE KEY unique_program_turn (program_id_link, turn_number),
    INDEX idx_turn_number (turn_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 全体共有プロンプトテンプレートテーブル
CREATE TABLE IF NOT EXISTS global_prompt_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    template_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- APIキー設定テーブル
CREATE TABLE IF NOT EXISTS api_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    key_name VARCHAR(100) NOT NULL UNIQUE,
    key_value TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- resslogテーブル（プロンプトとAI応答のログ）
CREATE TABLE IF NOT EXISTS resslog (
    id INT PRIMARY KEY AUTO_INCREMENT,
    prompt TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 初期データ挿入（APIキー用）
INSERT INTO api_settings (key_name, key_value) VALUES 
('grok_api_key', '')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;