-- Per-player catch/fail tracking with timestamps
-- Each row = one catch or fail event

CREATE TABLE IF NOT EXISTS user_catch_log (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_id    INT UNSIGNED NOT NULL,
    fingerprint   VARCHAR(20) NOT NULL,
    zone_id       INT UNSIGNED NOT NULL,
    rod_id        INT UNSIGNED NOT NULL,
    bait_id       INT UNSIGNED NOT NULL,
    item_id       INT UNSIGNED NULL,
    item_name     VARCHAR(64) NOT NULL DEFAULT '',
    hook_type     VARCHAR(16) NOT NULL DEFAULT '',
    result        ENUM('catch','fail') NOT NULL,
    caught_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    INDEX idx_license_fp (license_id, fingerprint, zone_id, rod_id, bait_id),
    INDEX idx_license_time (license_id, caught_at),
    INDEX idx_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Aggregated per-player stats (materialized for fast reads)
-- Updated on each catch/fail via the API
CREATE TABLE IF NOT EXISTS user_catch_stats (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_id    INT UNSIGNED NOT NULL,
    fingerprint   VARCHAR(20) NOT NULL,
    zone_id       INT UNSIGNED NOT NULL,
    rod_id        INT UNSIGNED NOT NULL,
    bait_id       INT UNSIGNED NOT NULL,
    times_hooked  INT UNSIGNED NOT NULL DEFAULT 0,
    times_caught  INT UNSIGNED NOT NULL DEFAULT 0,
    times_failed  INT UNSIGNED NOT NULL DEFAULT 0,
    first_catch   DATETIME NULL,
    last_catch    DATETIME NULL,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_fp (license_id, fingerprint, zone_id, rod_id, bait_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
