funman300
1ffe0ffa9f
Offline Ultimate Team backend — game-independent REST API. - 19 API endpoints: auth, profiles, clubs, cards, packs, squads, objectives, SBCs, match rewards, NPC market, statistics - Axum + SQLite + SQLx with full migrations - Weighted pack generator, SBC validation engine - JSON-driven mod data (cards, packs, objectives, SBCs) - 5 integration tests passing Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
127 lines
4.0 KiB
SQL
127 lines
4.0 KiB
SQL
-- OpenFUT Core initial schema
|
|
|
|
CREATE TABLE IF NOT EXISTS profiles (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
username TEXT NOT NULL UNIQUE,
|
|
level INTEGER NOT NULL DEFAULT 1,
|
|
xp INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS clubs (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
profile_id TEXT NOT NULL REFERENCES profiles(id),
|
|
name TEXT NOT NULL,
|
|
coins INTEGER NOT NULL DEFAULT 0,
|
|
level INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS owned_cards (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
club_id TEXT NOT NULL REFERENCES clubs(id),
|
|
card_id TEXT NOT NULL,
|
|
is_loan INTEGER NOT NULL DEFAULT 0,
|
|
loan_matches_remaining INTEGER,
|
|
acquired_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS packs (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
club_id TEXT NOT NULL REFERENCES clubs(id),
|
|
definition_id TEXT NOT NULL,
|
|
opened INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS squads (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
club_id TEXT NOT NULL REFERENCES clubs(id),
|
|
name TEXT NOT NULL DEFAULT 'My Squad',
|
|
formation TEXT NOT NULL DEFAULT '4-4-2',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS squad_players (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
squad_id TEXT NOT NULL REFERENCES squads(id),
|
|
owned_card_id TEXT NOT NULL REFERENCES owned_cards(id),
|
|
position_index INTEGER NOT NULL,
|
|
is_captain INTEGER NOT NULL DEFAULT 0,
|
|
is_on_bench INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS objective_progress (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
profile_id TEXT NOT NULL REFERENCES profiles(id),
|
|
objective_id TEXT NOT NULL,
|
|
current INTEGER NOT NULL DEFAULT 0,
|
|
completed INTEGER NOT NULL DEFAULT 0,
|
|
claimed INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE(profile_id, objective_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS matches (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
profile_id TEXT NOT NULL REFERENCES profiles(id),
|
|
squad_id TEXT NOT NULL,
|
|
opponent_name TEXT NOT NULL,
|
|
goals_for INTEGER NOT NULL DEFAULT 0,
|
|
goals_against INTEGER NOT NULL DEFAULT 0,
|
|
outcome TEXT NOT NULL,
|
|
coins_awarded INTEGER NOT NULL DEFAULT 0,
|
|
xp_awarded INTEGER NOT NULL DEFAULT 0,
|
|
mode TEXT NOT NULL DEFAULT 'squad_battles',
|
|
played_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS statistics (
|
|
profile_id TEXT PRIMARY KEY NOT NULL REFERENCES profiles(id),
|
|
matches_played INTEGER NOT NULL DEFAULT 0,
|
|
matches_won INTEGER NOT NULL DEFAULT 0,
|
|
matches_drawn INTEGER NOT NULL DEFAULT 0,
|
|
matches_lost INTEGER NOT NULL DEFAULT 0,
|
|
goals_scored INTEGER NOT NULL DEFAULT 0,
|
|
goals_conceded INTEGER NOT NULL DEFAULT 0,
|
|
packs_opened INTEGER NOT NULL DEFAULT 0,
|
|
sbcs_completed INTEGER NOT NULL DEFAULT 0,
|
|
total_coins_earned INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sbc_submissions (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
profile_id TEXT NOT NULL REFERENCES profiles(id),
|
|
sbc_id TEXT NOT NULL,
|
|
submitted_card_ids TEXT NOT NULL,
|
|
passed INTEGER NOT NULL DEFAULT 0,
|
|
submitted_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS market_listings (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
card_id TEXT NOT NULL,
|
|
seller_name TEXT NOT NULL,
|
|
price INTEGER NOT NULL DEFAULT 0,
|
|
listed_at TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL,
|
|
sold INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY NOT NULL,
|
|
value TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_owned_cards_club ON owned_cards(club_id);
|
|
CREATE INDEX IF NOT EXISTS idx_packs_club ON packs(club_id);
|
|
CREATE INDEX IF NOT EXISTS idx_squad_players_squad ON squad_players(squad_id);
|
|
CREATE INDEX IF NOT EXISTS idx_obj_progress_profile ON objective_progress(profile_id);
|
|
CREATE INDEX IF NOT EXISTS idx_matches_profile ON matches(profile_id);
|
|
CREATE INDEX IF NOT EXISTS idx_market_active ON market_listings(sold, expires_at);
|