-- ============================================================
-- Mystery Island Belohnungssystem
-- Erstellt: 2026-04-08
-- ============================================================

-- SQL-Skript 1: Glasperlen-Spalte + Schwellenwert-Tabelle

ALTER TABLE game_players
    ADD COLUMN glasperlen INT NOT NULL DEFAULT 0
    COMMENT 'Inselladen-Währung, verdient durch Minispiele auf der Geheimnisvollen Insel';

CREATE TABLE mystery_island_player_thresholds (
    id                  INT(11)       NOT NULL AUTO_INCREMENT,
    user_id             INT(11)       NOT NULL,
    site_key            VARCHAR(50)   NOT NULL COMMENT 'z.B. regenbogental',
    current_threshold   DECIMAL(5,2)  NOT NULL DEFAULT 100.00 COMMENT 'Aktueller Schwellenwert in %',
    last_updated_date   DATE          NOT NULL COMMENT 'Datum der letzten Berechnung (Lazy Eval Basis)',
    created_at          TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_user_site (user_id, site_key),
    KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Individueller Hauptpreis-Schwellenwert je Spieler und Spiel';

-- ------------------------------------------------------------

-- SQL-Skript 2: Hauptpreis-Konfiguration

CREATE TABLE mystery_island_game_rewards (
    id                INT(11)       NOT NULL AUTO_INCREMENT,
    site_key          VARCHAR(50)   NOT NULL COMMENT 'FK zu mystery_island_construction_sites.site_key',
    reward_type       ENUM(
        'resource', 'inventory_item',
        'coins', 'taler', 'kreativpunkte',
        'challenge_points', 'reputation_points'
    ) NOT NULL,
    resource_id       INT(11)       NULL DEFAULT NULL COMMENT 'Für reward_type = resource',
    resource_amount   INT(11)       NULL DEFAULT NULL,
    inventory_type    VARCHAR(50)   NULL DEFAULT NULL COMMENT 'z.B. Boost, Recipe, Fragment',
    inventory_item_id INT(11)       NULL DEFAULT NULL,
    inventory_amount  INT(11)       NULL DEFAULT NULL,
    currency_amount   INT(11)       NULL DEFAULT NULL COMMENT 'Für coins/taler/kreativpunkte/...',
    description       VARCHAR(255)  NULL DEFAULT NULL COMMENT 'Angezeigter Text für den Spieler',
    is_active         TINYINT(1)    NOT NULL DEFAULT 1,
    created_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_site_key (site_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Hauptpreis-Konfiguration pro Mystery Island Minispiel';

-- ------------------------------------------------------------

-- SQL-Skript 3: Inselladen (Sortiment + Kaufprotokoll)

CREATE TABLE mystery_island_shop_items (
    id                INT(11)       NOT NULL AUTO_INCREMENT,
    name              VARCHAR(100)  NOT NULL,
    description       VARCHAR(255)  NULL DEFAULT NULL,
    price_glasperlen  INT(11)       NOT NULL COMMENT 'Preis in Glasperlen',
    reward_type       ENUM(
        'resource', 'inventory_item',
        'coins', 'taler', 'kreativpunkte',
        'challenge_points', 'reputation_points'
    ) NOT NULL,
    resource_id       INT(11)       NULL DEFAULT NULL,
    resource_amount   INT(11)       NULL DEFAULT NULL,
    inventory_type    VARCHAR(50)   NULL DEFAULT NULL,
    inventory_item_id INT(11)       NULL DEFAULT NULL,
    inventory_amount  INT(11)       NULL DEFAULT NULL,
    currency_amount   INT(11)       NULL DEFAULT NULL,
    stock             INT(11)       NULL DEFAULT NULL COMMENT 'NULL = unbegrenzt',
    sort_order        INT(11)       NOT NULL DEFAULT 0,
    is_active         TINYINT(1)    NOT NULL DEFAULT 1,
    created_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_active_sort (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Inselladen-Sortiment, käuflich mit Glasperlen';

CREATE TABLE mystery_island_shop_purchases (
    id              INT(11)    NOT NULL AUTO_INCREMENT,
    user_id         INT(11)    NOT NULL,
    shop_item_id    INT(11)    NOT NULL,
    price_paid      INT(11)    NOT NULL COMMENT 'Glasperlen zum Kaufzeitpunkt (Snapshot)',
    purchased_at    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_user_id (user_id),
    KEY idx_item_id (shop_item_id),
    CONSTRAINT fk_shop_purch_user FOREIGN KEY (user_id)
        REFERENCES game_players (user_id),
    CONSTRAINT fk_shop_purch_item FOREIGN KEY (shop_item_id)
        REFERENCES mystery_island_shop_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Kaufprotokoll für den Inselladen';

-- ------------------------------------------------------------

-- SQL-Skript 4: ENUM-Typen anpassen (inventory_item → boost/recipe, challenge_points entfernt)

ALTER TABLE mystery_island_game_rewards
MODIFY reward_type ENUM(
    'resource', 'boost', 'recipe',
    'coins', 'taler', 'kreativpunkte', 'reputation_points'
) NOT NULL;

ALTER TABLE mystery_island_shop_items
MODIFY reward_type ENUM(
    'resource', 'boost', 'recipe',
    'coins', 'taler', 'kreativpunkte', 'reputation_points'
) NOT NULL;

-- ============================================================
-- Klangwerkstatt – neue Tabellen (2026-04-11)
-- ============================================================

CREATE TABLE melody_daily_challenges (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    challenge_date     DATE NOT NULL,
    melody_data        JSON NOT NULL COMMENT 'Format: {"piano":[[step,note_idx],...], ...}',
    total_active_cells INT UNSIGNED NOT NULL DEFAULT 0,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_challenge_date (challenge_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE melody_player_attempts (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id          INT UNSIGNED NOT NULL,
    challenge_id     INT UNSIGNED NOT NULL,
    submitted_melody JSON NOT NULL,
    score            TINYINT UNSIGNED NOT NULL DEFAULT 0,
    matching_cells   INT UNSIGNED NOT NULL DEFAULT 0,
    submitted_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_challenge (user_id, challenge_id),
    KEY idx_challenge_score (challenge_id, score DESC),
    KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE melody_listen_counts (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NOT NULL,
    challenge_id INT UNSIGNED NOT NULL,
    listen_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
    last_listen  TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_user_challenge (user_id, challenge_id),
    KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================

## Geänderte Dateien
-- (Jede erstellte/veränderte PHP/JS/CSS-Datei einmalig eintragen)

- game/melody_api.php          (neu erstellt – Klangwerkstatt API)
- game/js/klangwerkstatt.js    (neu erstellt – Klangwerkstatt Sequencer)
- game/klangwerkstatt.php      (neu erstellt – Klangwerkstatt Spielseite)