-- AnarchySpace Games Platform schema (v0.2)
-- Existing `users` table is reused; foreign keys expect `users.id` (INT).

CREATE TABLE IF NOT EXISTS games (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id        INT NOT NULL,
  slug            VARCHAR(120) NOT NULL UNIQUE,
  title           VARCHAR(160) NOT NULL,
  description     TEXT,
  visibility      ENUM('draft','private','unlisted','public') DEFAULT 'draft',
  engine_version  VARCHAR(32) DEFAULT 'core-0.1',
  storage_path    VARCHAR(255) NOT NULL,
  thumbnail_path  VARCHAR(255),
  last_commit_id  INT UNSIGNED,
  last_version_id INT UNSIGNED,
  stars_count     INT UNSIGNED DEFAULT 0,
  plays_count     BIGINT UNSIGNED DEFAULT 0,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_games_owner (owner_id),
  CONSTRAINT fk_games_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS game_assets (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game_id       INT UNSIGNED NOT NULL,
  storage_key   VARCHAR(255) NOT NULL,
  asset_name    VARCHAR(160) NOT NULL,
  asset_type    ENUM('model','texture','audio','script','prefab','shader','ui','other') DEFAULT 'other',
  mime_type     VARCHAR(120),
  size_bytes    BIGINT UNSIGNED DEFAULT 0,
  checksum      CHAR(64),
  uploaded_by   INT NOT NULL,
  uploaded_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_assets_game FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
  CONSTRAINT fk_assets_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_assets_game (game_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS game_files (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_id         INT UNSIGNED NOT NULL,
    file_path       VARCHAR(255) NOT NULL,
    file_type       ENUM('script','scene','config','shader','ui','other') DEFAULT 'other',
    content_hash    CHAR(64),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
    INDEX idx_files_game (game_id),
    UNIQUE KEY uniq_game_path (game_id, file_path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS game_commits (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game_id       INT UNSIGNED NOT NULL,
  user_id       INT NOT NULL,
  hash          CHAR(40) NOT NULL,
  message       VARCHAR(255) NOT NULL,
  auto_commit   TINYINT(1) DEFAULT 1,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_commit_game (game_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS commit_files (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    commit_id   INT UNSIGNED NOT NULL,
    file_path   VARCHAR(255) NOT NULL,
    change_type ENUM('added','modified','deleted') NOT NULL,
    diff_patch  LONGTEXT,
    FOREIGN KEY (commit_id) REFERENCES game_commits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS game_versions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game_id         INT UNSIGNED NOT NULL,
  commit_id       INT UNSIGNED NOT NULL,
  version_tag     VARCHAR(64) NOT NULL,
  changelog       TEXT,
  build_path      VARCHAR(255) NOT NULL,
  published_by    INT NOT NULL,
  published_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status          ENUM('processing','ready','failed') DEFAULT 'processing',
  FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
  FOREIGN KEY (commit_id) REFERENCES game_commits(id) ON DELETE CASCADE,
  FOREIGN KEY (published_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_versions_game (game_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS game_analytics (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game_id       INT UNSIGNED NOT NULL,
  metric_date   DATE NOT NULL,
  player_count  INT UNSIGNED DEFAULT 0,
  session_time  INT UNSIGNED DEFAULT 0,
  errors        INT UNSIGNED DEFAULT 0,
  crashes       INT UNSIGNED DEFAULT 0,
  perf_score    DECIMAL(6,2) DEFAULT 0,
  INDEX idx_metric (game_id, metric_date),
  FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS game_plugins (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game_id      INT UNSIGNED NOT NULL,
  plugin_slug  VARCHAR(120) NOT NULL,
  config_json  JSON,
  enabled      TINYINT(1) DEFAULT 1,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
  INDEX idx_plugin_game (game_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
