-- Sentinel / TackleBox Database Schema

-- ═══════════════════════════════════════════════════════════
-- Licensing (product-agnostic — reusable for CraftBot etc.)
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS licenses (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_key  CHAR(19) NOT NULL,
    owner_name   VARCHAR(64) NOT NULL DEFAULT '',
    owner_email  VARCHAR(255) NOT NULL DEFAULT '',
    hardware_id  VARCHAR(64) NULL,
    status       ENUM('active','suspended','revoked','expired') NOT NULL DEFAULT 'active',
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at   DATETIME NULL,
    last_auth_at DATETIME NULL,
    notes        VARCHAR(500) NOT NULL DEFAULT '',
    UNIQUE KEY uq_key (license_key),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id      VARCHAR(32) NOT NULL,
    display_name    VARCHAR(64) NOT NULL,
    current_version VARCHAR(16) NOT NULL DEFAULT '1.0.0',
    minimum_version VARCHAR(16) NOT NULL DEFAULT '1.0.0',
    download_url    VARCHAR(255) NOT NULL DEFAULT '',
    UNIQUE KEY uq_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS license_products (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_id  INT UNSIGNED NOT NULL,
    product_id  INT UNSIGNED NOT NULL,
    features    TEXT NOT NULL,
    expires_at  DATETIME NULL,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uq_lp (license_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_id     INT UNSIGNED NOT NULL,
    product_id     INT UNSIGNED NOT NULL,
    session_token  CHAR(36) NOT NULL,
    hardware_id    VARCHAR(64) NOT NULL DEFAULT '',
    client_version VARCHAR(16) NOT NULL DEFAULT '',
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at     DATETIME NOT NULL,
    last_heartbeat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address     VARCHAR(45) NOT NULL DEFAULT '',
    is_active      TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uq_token (session_token),
    INDEX idx_license_active (license_id, is_active),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ═══════════════════════════════════════════════════════════
-- Game data (replaces fishdata.json)
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS items (
    id          INT UNSIGNED PRIMARY KEY,
    name        VARCHAR(64) NOT NULL,
    stack_size  INT UNSIGNED NOT NULL DEFAULT 1,
    ah_category INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS zones (
    id   INT UNSIGNED PRIMARY KEY,
    name VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rods (
    id        INT UNSIGNED PRIMARY KEY,
    name      VARCHAR(64) NOT NULL,
    size_type TINYINT NOT NULL DEFAULT 0,
    min_rank  INT NOT NULL DEFAULT 1,
    max_rank  INT NOT NULL DEFAULT 10,
    attack    INT NOT NULL DEFAULT 0,
    recovery  INT NOT NULL DEFAULT 0,
    time      INT NOT NULL DEFAULT 0,
    breakable TINYINT(1) NOT NULL DEFAULT 0,
    legendary TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS baits (
    id      INT UNSIGNED PRIMARY KEY,
    name    VARCHAR(64) NOT NULL,
    type    TINYINT NOT NULL DEFAULT 0,
    maxhook INT NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fish (
    id         INT UNSIGNED PRIMARY KEY,
    name       VARCHAR(64) NOT NULL,
    skill      INT NOT NULL DEFAULT 0,
    difficulty INT NOT NULL DEFAULT 0,
    base_delay INT NOT NULL DEFAULT 0,
    base_move  INT NOT NULL DEFAULT 0,
    ranking    INT NOT NULL DEFAULT 0,
    size_type  TINYINT NOT NULL DEFAULT 0,
    is_item    TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS zone_fish (
    zone_id INT UNSIGNED NOT NULL,
    fish_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (zone_id, fish_id),
    INDEX idx_fish (fish_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bait_fish (
    bait_id INT UNSIGNED NOT NULL,
    fish_id INT UNSIGNED NOT NULL,
    power   TINYINT NOT NULL DEFAULT 1,
    PRIMARY KEY (bait_id, fish_id),
    INDEX idx_fish (fish_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS zone_msg_bases (
    zone_id  INT UNSIGNED PRIMARY KEY,
    msg_base INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ═══════════════════════════════════════════════════════════
-- Fingerprints (crowdsourced fish identification)
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS shared_fingerprints (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fingerprint     VARCHAR(20) NOT NULL,
    zone_id         INT UNSIGNED NOT NULL,
    rod_id          INT UNSIGNED NOT NULL,
    bait_id         INT UNSIGNED NOT NULL,
    hook_type       VARCHAR(16) NOT NULL DEFAULT '',
    item_id         INT UNSIGNED NULL,
    item_name       VARCHAR(64) NOT NULL DEFAULT '',
    total_caught    INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_fp (fingerprint, zone_id, rod_id, bait_id),
    INDEX idx_zone (zone_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ═══════════════════════════════════════════════════════════
-- User preferences (per-license fish catch/release selections)
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS user_fish_selections (
    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,
    selected    TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    UNIQUE KEY uq_sel (license_id, fingerprint, zone_id, rod_id, bait_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ═══════════════════════════════════════════════════════════
-- Telemetry / session logs
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS session_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id  INT UNSIGNED NOT NULL,
    event_type  VARCHAR(32) NOT NULL,
    zone_id     INT UNSIGNED NULL,
    extra_json  JSON NULL,
    logged_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ═══════════════════════════════════════════════════════════
-- Seed data
-- ═══════════════════════════════════════════════════════════

INSERT IGNORE INTO products (product_id, display_name, current_version, minimum_version)
VALUES ('tacklebox', 'TackleBox', '1.0.0', '1.0.0');
