-- APIキー個別管理機能拡張用SQL
-- 実行日: 2025-08-09
-- 説明: program_idsごとに個別のAPIキーを管理するための拡張

-- ============================================
-- api_keysテーブルの拡張
-- ============================================

-- 使用済みフラグを追加
ALTER TABLE api_keys 
ADD COLUMN IF NOT EXISTS is_used BOOLEAN DEFAULT FALSE 
COMMENT '使用済みフラグ（true=割り当て済み）';

-- 割り当て先の記録
ALTER TABLE api_keys 
ADD COLUMN IF NOT EXISTS assigned_to INT DEFAULT NULL 
COMMENT 'program_ids.idへの参照（どこに割り当てたか）';

-- 割り当て日時
ALTER TABLE api_keys 
ADD COLUMN IF NOT EXISTS assigned_at DATETIME DEFAULT NULL 
COMMENT 'APIキーを割り当てた日時';

-- インデックス追加
CREATE INDEX IF NOT EXISTS idx_api_keys_is_used ON api_keys(is_used);
CREATE INDEX IF NOT EXISTS idx_api_keys_assigned_to ON api_keys(assigned_to);

-- ============================================
-- program_idsテーブルの拡張
-- ============================================

-- APIキーID（api_keysテーブルへの参照）
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS api_key_id INT DEFAULT NULL 
COMMENT 'api_keysテーブルのID';

-- APIキー名（冗長性のため）
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS api_name VARCHAR(100) DEFAULT NULL 
COMMENT 'APIキー名';

-- APIキー値（冗長性のため）
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS api_key TEXT DEFAULT NULL 
COMMENT 'APIキー値';

-- 疎通チェック状態
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS api_check_status VARCHAR(50) DEFAULT 'untested' 
COMMENT '疎通チェック状態（untested/success/failed）';

-- 疎通チェックメッセージ
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS api_check_message TEXT 
COMMENT '疎通チェック結果メッセージ';

-- 最終疎通チェック日時
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS last_api_check DATETIME DEFAULT NULL 
COMMENT '最終疎通チェック日時';

-- 最終API利用日時
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS last_api_used DATETIME DEFAULT NULL 
COMMENT '最終API利用日時';

-- APIエラーカウント
ALTER TABLE program_ids 
ADD COLUMN IF NOT EXISTS api_error_count INT DEFAULT 0 
COMMENT 'APIエラー回数';

-- インデックス追加
CREATE INDEX IF NOT EXISTS idx_program_ids_api_key_id ON program_ids(api_key_id);
CREATE INDEX IF NOT EXISTS idx_program_ids_api_check_status ON program_ids(api_check_status);

-- ============================================
-- 外部キー制約（オプション）
-- 必要に応じてコメントアウトを解除
-- ============================================

-- ALTER TABLE program_ids 
-- ADD CONSTRAINT fk_program_ids_api_key 
-- FOREIGN KEY (api_key_id) REFERENCES api_keys(id) 
-- ON DELETE SET NULL;

-- ALTER TABLE api_keys 
-- ADD CONSTRAINT fk_api_keys_assigned_to 
-- FOREIGN KEY (assigned_to) REFERENCES program_ids(id) 
-- ON DELETE SET NULL;