ALTER TABLE games
    ADD COLUMN IF NOT EXISTS slug VARCHAR(255) NULL AFTER id,
    ADD COLUMN IF NOT EXISTS package_version VARCHAR(32) NOT NULL DEFAULT '1' AFTER directory_name,
    ADD COLUMN IF NOT EXISTS entry_point VARCHAR(255) NOT NULL DEFAULT 'index.html' AFTER package_version,
    ADD COLUMN IF NOT EXISTS package_url VARCHAR(1024) NOT NULL DEFAULT '' AFTER entry_point,
    ADD COLUMN IF NOT EXISTS cover_url VARCHAR(1024) NOT NULL DEFAULT '' AFTER package_url,
    ADD COLUMN IF NOT EXISTS preview_url VARCHAR(1024) NOT NULL DEFAULT '' AFTER cover_url,
    ADD COLUMN IF NOT EXISTS intro_url VARCHAR(1024) NOT NULL DEFAULT '' AFTER preview_url,
    ADD COLUMN IF NOT EXISTS size_bytes BIGINT NOT NULL DEFAULT 0 AFTER intro_url,
    ADD COLUMN IF NOT EXISTS sha256 CHAR(64) NOT NULL DEFAULT '' AFTER size_bytes,
    ADD COLUMN IF NOT EXISTS orientation ENUM('auto','portrait','landscape') NOT NULL DEFAULT 'auto' AFTER sha256,
    ADD COLUMN IF NOT EXISTS publish_status ENUM('local','draft','qa','published','disabled') NOT NULL DEFAULT 'draft' AFTER orientation,
    ADD COLUMN IF NOT EXISTS min_app_version INT NOT NULL DEFAULT 1 AFTER publish_status,
    ADD COLUMN IF NOT EXISTS updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER min_app_version;
UPDATE games SET slug=directory_name WHERE slug IS NULL OR slug='';
ALTER TABLE games MODIFY slug VARCHAR(255) NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_games_slug ON games(slug);
CREATE INDEX IF NOT EXISTS idx_games_publish ON games(publish_status,checked,mobile_support,error,id);

CREATE TABLE IF NOT EXISTS game_translations (
    game_id INT NOT NULL, language_tag VARCHAR(16) NOT NULL, name VARCHAR(255) NOT NULL, description TEXT NOT NULL,
    PRIMARY KEY(game_id,language_tag), FOREIGN KEY(game_id) REFERENCES games(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, guest_installation_hash CHAR(64) NULL, google_sub VARCHAR(255) NULL,
    email VARCHAR(255) NOT NULL DEFAULT '', nickname VARCHAR(80) NOT NULL DEFAULT 'Guest Player',
    avatar_url VARCHAR(1024) NOT NULL DEFAULT '', language_tag VARCHAR(16) NOT NULL DEFAULT 'en',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(id), UNIQUE KEY uq_users_guest(guest_installation_hash), UNIQUE KEY uq_users_google(google_sub)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS auth_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, access_hash CHAR(64) NOT NULL,
    refresh_hash CHAR(64) NOT NULL, access_expires_at DATETIME NOT NULL, refresh_expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id), UNIQUE KEY uq_session_access(access_hash),
    UNIQUE KEY uq_session_refresh(refresh_hash), KEY idx_session_user(user_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_profiles (
    user_id BIGINT UNSIGNED NOT NULL, total_points BIGINT NOT NULL DEFAULT 0, level INT NOT NULL DEFAULT 1,
    streak_days INT NOT NULL DEFAULT 1, distinct_login_days INT NOT NULL DEFAULT 1, last_login_date DATE NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_events (
    event_id VARCHAR(64) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, type VARCHAR(40) NOT NULL,
    game_slug VARCHAR(255) NOT NULL DEFAULT '', duration_seconds INT UNSIGNED NOT NULL DEFAULT 0,
    submitted_points INT NOT NULL DEFAULT 0, accepted_points INT NOT NULL DEFAULT 0, payload JSON NULL,
    created_at_ms BIGINT NOT NULL, received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(event_id), KEY idx_events_user_time(user_id,received_at), KEY idx_events_type(user_id,type),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mission_templates (
    id VARCHAR(64) NOT NULL, title VARCHAR(255) NOT NULL, description VARCHAR(500) NOT NULL, kind VARCHAR(40) NOT NULL,
    target_value BIGINT NOT NULL, cadence ENUM('daily','weekly','lifetime') NOT NULL DEFAULT 'lifetime',
    reward_type VARCHAR(40) NOT NULL DEFAULT 'badge', reward_id VARCHAR(64) NOT NULL DEFAULT '', active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_missions (
    user_id BIGINT UNSIGNED NOT NULL, mission_id VARCHAR(64) NOT NULL, period_key VARCHAR(32) NOT NULL DEFAULT 'lifetime',
    progress BIGINT NOT NULL DEFAULT 0, claimed TINYINT(1) NOT NULL DEFAULT 0, claimed_at DATETIME NULL,
    PRIMARY KEY(user_id,mission_id,period_key), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY(mission_id) REFERENCES mission_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS badges (
    id VARCHAR(64) NOT NULL, name VARCHAR(255) NOT NULL, description VARCHAR(500) NOT NULL,
    icon_url VARCHAR(1024) NOT NULL DEFAULT '', required_points BIGINT NOT NULL DEFAULT 0, PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS player_badges (
    user_id BIGINT UNSIGNED NOT NULL, badge_id VARCHAR(64) NOT NULL, unlocked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(user_id,badge_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY(badge_id) REFERENCES badges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS wallpapers (
    id VARCHAR(64) NOT NULL, name VARCHAR(255) NOT NULL, scene INT NOT NULL, preview_url VARCHAR(1024) NOT NULL DEFAULT '',
    asset_url VARCHAR(1024) NOT NULL DEFAULT '', asset_sha256 CHAR(64) NOT NULL DEFAULT '',
    required_points BIGINT NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS player_wallpapers (
    user_id BIGINT UNSIGNED NOT NULL, wallpaper_id VARCHAR(64) NOT NULL, unlocked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(user_id,wallpaper_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY(wallpaper_id) REFERENCES wallpapers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO mission_templates(id,title,description,kind,target_value,cadence,reward_type,reward_id) VALUES
('daily_points','Power player','Earn 100 points','points',100,'daily','wallpaper','neon_grid'),
('explorer','Arcade explorer','Play 5 different games','games',5,'lifetime','wallpaper','aurora'),
('social','Bring a friend','Share 3 games','shares',3,'lifetime','wallpaper','cosmic'),
('streak','Stay in the game','Open Offline Arcade 7 days','streak',7,'lifetime','wallpaper','pixel_rain');
INSERT IGNORE INTO wallpapers(id,name,scene,required_points) VALUES
('neon_grid','Neon Grid',0,0),('aurora','Aurora',1,250),('cosmic','Cosmic',2,750),
('pixel_rain','Pixel Rain',3,1500),('deep_ocean','Deep Ocean',4,3000);
INSERT IGNORE INTO badges(id,name,description,required_points) VALUES
('first_game','First launch','Play your first game',1),('arcade_rookie','Arcade Rookie','Earn 500 points',500),
('arcade_master','Arcade Master','Earn 5000 points',5000);