-- 診断ログテーブル
-- 実行: mysql -u root -p zuwmnpqv_wp924 < 001_create_diagnosis_log.sql

CREATE TABLE IF NOT EXISTS shindan_diagnosis_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnosed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    exit_id TINYINT NOT NULL,
    stone_id VARCHAR(20) NOT NULL,

    age_range VARCHAR(10),
    gender VARCHAR(10),
    married VARCHAR(10),
    prefecture VARCHAR(10),

    answers JSON NOT NULL,

    user_agent VARCHAR(500),
    ip_hash VARCHAR(64),

    INDEX idx_diagnosed_at (diagnosed_at),
    INDEX idx_stone_id (stone_id),
    INDEX idx_prefecture (prefecture),
    INDEX idx_exit_id (exit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;