philip
b239ae3e5f
Replaces Firebase with a self-hosted PHP/MySQL API served from winded.prymsolutions.com. Includes full backend (schema, auth, events, teams, brackets, suggestions, stats, media, file upload) and updated Flutter repositories and domain models. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
122 lines
4.7 KiB
SQL
122 lines
4.7 KiB
SQL
-- 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
|
||
);
|