-- ═══════════════════════════════════════════════════════════
--  Archipel Game API — Database Migration
-- ═══════════════════════════════════════════════════════════

SET NAMES utf8mb4;

-- ─────────────────────────────────────────────────────────
--  PLAYER IDENTITY
-- ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS players (
    player_id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    username       VARCHAR(128) UNIQUE NOT NULL,
    password_hash  VARCHAR(255) NOT NULL,
    display_name   VARCHAR(64),
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Top-level save metadata
CREATE TABLE IF NOT EXISTS player_saves (
    player_id              BIGINT PRIMARY KEY,
    schema_version         INT NOT NULL DEFAULT 2,
    app_version            VARCHAR(32),
    last_updated_unix      BIGINT,
    pointer_expedition_id  INT NOT NULL DEFAULT 0,
    pointer_set_number     INT NOT NULL DEFAULT 0,
    pointer_question_index INT NOT NULL DEFAULT 0,
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────
--  EXPEDITION PROGRESS
-- ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS player_expedition_progress (
    player_id       BIGINT NOT NULL,
    expedition_id   INT NOT NULL,
    completed_sets  INT NOT NULL DEFAULT 0,
    PRIMARY KEY (player_id, expedition_id),
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS player_set_progress (
    player_id           BIGINT NOT NULL,
    expedition_id       INT NOT NULL,
    set_number          INT NOT NULL,
    last_question_index INT NOT NULL DEFAULT 0,
    set_completed       BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (player_id, expedition_id, set_number),
    FOREIGN KEY (player_id, expedition_id)
        REFERENCES player_expedition_progress(player_id, expedition_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS player_question_attempts (
    attempt_id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    player_id           BIGINT NOT NULL,
    expedition_id       INT NOT NULL,
    set_number          INT NOT NULL,
    question_id         INT NOT NULL,
    selected_answer_ids JSON NOT NULL,
    was_correct         BOOLEAN NOT NULL,
    attempted_at_unix   BIGINT NOT NULL,
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE,
    INDEX idx_player_expedition (player_id, expedition_id),
    INDEX idx_player_set (player_id, expedition_id, set_number),
    INDEX idx_question (question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────
--  KRAKEN PROGRESS
-- ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS kraken_progress (
    player_id             BIGINT PRIMARY KEY,
    current_belt_index    INT NOT NULL DEFAULT 0,
    current_notion_index  INT NOT NULL DEFAULT 0,
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS kraken_sessions (
    session_id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    player_id          BIGINT NOT NULL,
    notion_id          INT NOT NULL,
    started_at_unix    BIGINT NOT NULL,
    succeeded          BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE,
    INDEX idx_player_notion (player_id, notion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS kraken_answers (
    answer_id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    session_id         BIGINT NOT NULL,
    question_text      VARCHAR(128) NOT NULL,
    correct_answer     DOUBLE NOT NULL,
    given_answer       DOUBLE NOT NULL,
    was_correct        BOOLEAN NOT NULL,
    answered_at_unix   BIGINT NOT NULL,
    FOREIGN KEY (session_id) REFERENCES kraken_sessions(session_id) ON DELETE CASCADE,
    INDEX idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────
--  INVENTORY
-- ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS player_inventory (
    player_id  BIGINT NOT NULL,
    item_id    VARCHAR(64) NOT NULL,
    count      INT NOT NULL DEFAULT 0,
    PRIMARY KEY (player_id, item_id),
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────
--  ISLANDS
-- ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS player_islands (
    player_id  BIGINT NOT NULL,
    island_id  INT NOT NULL,
    chunks     JSON,
    rocks      JSON,
    roads      JSON,
    items      JSON,
    PRIMARY KEY (player_id, island_id),
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
