-- Winded – MySQL schema
-- Run this once in your Hostinger MySQL database panel (phpMyAdmin or CLI).

CREATE TABLE IF NOT EXISTS users (
  id            VARCHAR(36)  PRIMARY KEY,
  email         VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name  VARCHAR(255) DEFAULT '',
  role          ENUM('player','manager','admin') DEFAULT 'player',
  bio           TEXT         DEFAULT '',
  photo_url     VARCHAR(500) DEFAULT NULL,
  position      VARCHAR(50)  DEFAULT NULL,
  team_id       VARCHAR(36)  DEFAULT NULL,
  created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS events (
  id                    VARCHAR(36)  PRIMARY KEY,
  title                 VARCHAR(255) NOT NULL,
  description           TEXT         DEFAULT '',
  category              ENUM('tournament','pickup') DEFAULT 'pickup',
  event_date            DATETIME     NOT NULL,
  location              VARCHAR(500) DEFAULT '',
  registration_deadline DATETIME     DEFAULT NULL,
  max_teams             INT          DEFAULT 0,
  is_cancelled          TINYINT(1)   DEFAULT 0,
  image_url             VARCHAR(500) DEFAULT NULL,
  created_by            VARCHAR(36)  DEFAULT NULL,
  created_at            DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS event_registrations (
  id            VARCHAR(36) PRIMARY KEY,
  event_id      VARCHAR(36) NOT NULL,
  user_id       VARCHAR(36) NOT NULL,
  registered_at DATETIME    DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_reg (event_id, user_id)
);

CREATE TABLE IF NOT EXISTS teams (
  id            VARCHAR(36)  PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,
  description   TEXT         DEFAULT NULL,
  logo_url      VARCHAR(500) DEFAULT NULL,
  primary_color VARCHAR(20)  DEFAULT NULL,
  status        ENUM('pending','approved','rejected') DEFAULT 'pending',
  manager_id    VARCHAR(36)  DEFAULT NULL,
  manager_email VARCHAR(255) DEFAULT '',
  manager_phone VARCHAR(50)  DEFAULT NULL,
  wins          INT          DEFAULT 0,
  draws         INT          DEFAULT 0,
  losses        INT          DEFAULT 0,
  created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS players (
  id           VARCHAR(36)  PRIMARY KEY,
  team_id      VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  DEFAULT NULL,
  name         VARCHAR(255) NOT NULL,
  number       INT          DEFAULT NULL,
  position     VARCHAR(50)  DEFAULT NULL,
  goals_scored INT          DEFAULT 0,
  assists      INT          DEFAULT 0,
  created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS join_requests (
  id           VARCHAR(36)  PRIMARY KEY,
  team_id      VARCHAR(36)  NOT NULL,
  team_name    VARCHAR(255) DEFAULT '',
  player_id    VARCHAR(36)  NOT NULL,
  player_name  VARCHAR(255) DEFAULT '',
  player_email VARCHAR(255) DEFAULT '',
  status       ENUM('pending','approved','rejected') DEFAULT 'pending',
  requested_at DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Brackets store rounds+matches as a JSON blob for MVP simplicity.
CREATE TABLE IF NOT EXISTS brackets (
  id          VARCHAR(36)  PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  event_id    VARCHAR(36)  DEFAULT NULL,
  status      ENUM('draft','active','completed') DEFAULT 'draft',
  rounds_json LONGTEXT     DEFAULT NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS suggestions (
  id           VARCHAR(36) PRIMARY KEY,
  user_id      VARCHAR(36) DEFAULT NULL,
  display_name VARCHAR(255) DEFAULT NULL,
  text         TEXT        NOT NULL,
  is_anonymous TINYINT(1)  DEFAULT 0,
  status       ENUM('pending','reviewed','implemented') DEFAULT 'pending',
  submitted_at DATETIME    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS media_links (
  id           VARCHAR(36)  PRIMARY KEY,
  platform     VARCHAR(100) NOT NULL,
  handle       VARCHAR(255) DEFAULT '',
  url          VARCHAR(500) NOT NULL,
  display_name VARCHAR(255) DEFAULT '',
  sort_order   INT          DEFAULT 0
);

CREATE TABLE IF NOT EXISTS highlights (
  id            VARCHAR(36)  PRIMARY KEY,
  title         VARCHAR(255) NOT NULL,
  description   TEXT         DEFAULT '',
  youtube_url   VARCHAR(500) NOT NULL,
  thumbnail_url VARCHAR(500) DEFAULT NULL,
  published_at  DATE         DEFAULT NULL,
  sort_order    INT          DEFAULT 0
);