System Overview

-- USERS & AUTH
CREATE TABLE users (
id              SERIAL PRIMARY KEY,
email           CITEXT UNIQUE NOT NULL,
username        CITEXT UNIQUE NOT NULL,
password_hash   TEXT NOT NULL,
locale          VARCHAR(8) DEFAULT 'en',
is_banned       BOOLEAN DEFAULT FALSE,
created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE roles (
id      SERIAL PRIMARY KEY,
name    VARCHAR(32) UNIQUE NOT NULL -- USER, MOD, ADMIN
);

CREATE TABLE user_roles (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, role_id)
);

CREATE TABLE sessions (
id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
refresh_token TEXT NOT NULL,
created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
expires_at   TIMESTAMPTZ NOT NULL
);

-- TEAMS & PLAYERS
CREATE TABLE teams (
id                SERIAL PRIMARY KEY,
user_id           INTEGER NOT NULL REFERENCES users(id),
name              VARCHAR(64) NOT NULL,
short_name        VARCHAR(8),
country           VARCHAR(2),
logo_url          TEXT,
stadium_name      VARCHAR(64),
balance           BIGINT NOT NULL DEFAULT 0,
wage_budget       BIGINT NOT NULL DEFAULT 0,
stadium_capacity  INTEGER NOT NULL DEFAULT 1000,
created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TYPE position AS ENUM (
'GK','CB','FB','DM','CM','AM','W','ST'
);

CREATE TABLE players (
id              SERIAL PRIMARY KEY,
team_id         INTEGER REFERENCES teams(id) ON DELETE SET NULL,
name            VARCHAR(64) NOT NULL,
age             INTEGER NOT NULL,
nationality     VARCHAR(2),
preferred_pos   position NOT NULL,
pace            SMALLINT NOT NULL CHECK (pace BETWEEN 0 AND 100),
shooting        SMALLINT NOT NULL CHECK (shooting BETWEEN 0 AND 100),
passing         SMALLINT NOT NULL CHECK (passing BETWEEN 0 AND 100),
dribbling       SMALLINT NOT NULL CHECK (dribbling BETWEEN 0 AND 100),
defending       SMALLINT NOT NULL CHECK (defending BETWEEN 0 AND 100),
physical        SMALLINT NOT NULL CHECK (physical BETWEEN 0 AND 100),
goalkeeping     SMALLINT NOT NULL CHECK (goalkeeping BETWEEN 0 AND 100),
vision          SMALLINT NOT NULL CHECK (vision BETWEEN 0 AND 100),
work_rate       SMALLINT NOT NULL CHECK (work_rate BETWEEN 0 AND 100),
discipline      SMALLINT NOT NULL CHECK (discipline BETWEEN 0 AND 100),
energy          SMALLINT NOT NULL DEFAULT 100 CHECK (energy BETWEEN 0 AND 100),
morale          SMALLINT NOT NULL DEFAULT 50 CHECK (morale BETWEEN 0 AND 100),
value           BIGINT NOT NULL DEFAULT 0,
wage            BIGINT NOT NULL DEFAULT 0,
contract_until  DATE,
created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- FORMATIONS & TACTICS
CREATE TABLE formations (
id          SERIAL PRIMARY KEY,
team_id     INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
name        VARCHAR(64) NOT NULL, -- e.g. "Default 4-4-2"
base_system VARCHAR(16) NOT NULL CHECK (base_system IN ('4-4-2','4-3-3','3-5-2')),
is_default  BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE formation_slots (
id           SERIAL PRIMARY KEY,
formation_id INTEGER NOT NULL REFERENCES formations(id) ON DELETE CASCADE,
slot_index   SMALLINT NOT NULL,            -- 1..11
position     position NOT NULL,
player_id    INTEGER REFERENCES players(id) ON DELETE SET NULL
);

CREATE TABLE tactic_profiles (
id              SERIAL PRIMARY KEY,
team_id         INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
name            VARCHAR(64) NOT NULL,
style           VARCHAR(32) NOT NULL,      -- possession, counter, etc.
defensive_line  SMALLINT NOT NULL CHECK (defensive_line BETWEEN 0 AND 100),
width           SMALLINT NOT NULL CHECK (width BETWEEN 0 AND 100),
tempo           SMALLINT NOT NULL CHECK (tempo BETWEEN 0 AND 100),
pressing        SMALLINT NOT NULL CHECK (pressing BETWEEN 0 AND 100),
aggression      SMALLINT NOT NULL CHECK (aggression BETWEEN 0 AND 100),
is_default      BOOLEAN NOT NULL DEFAULT FALSE
);

-- COMPETITIONS
CREATE TABLE competitions (
id          SERIAL PRIMARY KEY,
name        VARCHAR(64) NOT NULL,
type        VARCHAR(16) NOT NULL CHECK (type IN ('LEAGUE','CUP')),
country     VARCHAR(2),
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE seasons (
id               SERIAL PRIMARY KEY,
competition_id   INTEGER NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
season_number    INTEGER NOT NULL,
start_date       DATE NOT NULL,
end_date         DATE NOT NULL
);

CREATE TABLE matches (
id                 SERIAL PRIMARY KEY,
season_id          INTEGER NOT NULL REFERENCES seasons(id) ON DELETE CASCADE,
competition_stage  VARCHAR(32), -- league round, cup round
home_team_id       INTEGER NOT NULL REFERENCES teams(id),
away_team_id       INTEGER NOT NULL REFERENCES teams(id),
kickoff_at         TIMESTAMPTZ NOT NULL,
status             VARCHAR(16) NOT NULL CHECK (status IN ('SCHEDULED','LIVE','FINISHED','CANCELED')),
goals_home         SMALLINT,
goals_away         SMALLINT,
events             JSONB, -- serialized timeline
home_formation_id  INTEGER REFERENCES formations(id),
away_formation_id  INTEGER REFERENCES formations(id),
home_tactic_id     INTEGER REFERENCES tactic_profiles(id),
away_tactic_id     INTEGER REFERENCES tactic_profiles(id)
);

-- MARKET
CREATE TABLE transfer_listings (
id            SERIAL PRIMARY KEY,
player_id     INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
seller_team_id INTEGER NOT NULL REFERENCES teams(id),
type          VARCHAR(16) NOT NULL CHECK (type IN ('AUCTION','FIXED')),
min_price     BIGINT NOT NULL,
buy_now_price BIGINT,
ends_at       TIMESTAMPTZ NOT NULL,
status        VARCHAR(16) NOT NULL CHECK (status IN ('OPEN','SOLD','EXPIRED','CANCELED'))
);

CREATE TABLE transfer_bids (
id             SERIAL PRIMARY KEY,
listing_id     INTEGER NOT NULL REFERENCES transfer_listings(id) ON DELETE CASCADE,
bidder_team_id INTEGER NOT NULL REFERENCES teams(id),
amount         BIGINT NOT NULL,
created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- SPONSORS & BUILDINGS
CREATE TABLE sponsors (
id           SERIAL PRIMARY KEY,
name         VARCHAR(64) NOT NULL,
base_fee     BIGINT NOT NULL,
win_bonus    BIGINT NOT NULL DEFAULT 0,
objective    VARCHAR(128)
);

CREATE TABLE team_sponsors (
id           SERIAL PRIMARY KEY,
team_id      INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
sponsor_id   INTEGER NOT NULL REFERENCES sponsors(id),
start_date   DATE NOT NULL,
end_date     DATE NOT NULL,
active       BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE buildings (
id           SERIAL PRIMARY KEY,
team_id      INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
type         VARCHAR(32) NOT NULL, -- stadium, training, academy, etc.
level        SMALLINT NOT NULL DEFAULT 1,
upgrading    BOOLEAN NOT NULL DEFAULT FALSE,
upgrade_ends_at TIMESTAMPTZ
);

-- GUILDS & MESSAGING
CREATE TABLE guilds (
id          SERIAL PRIMARY KEY,
name        VARCHAR(64) UNIQUE NOT NULL,
tag         VARCHAR(8) UNIQUE NOT NULL,
created_by  INTEGER NOT NULL REFERENCES users(id),
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE guild_members (
guild_id INTEGER NOT NULL REFERENCES guilds(id) ON DELETE CASCADE,
user_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role     VARCHAR(16) NOT NULL CHECK (role IN ('LEADER','OFFICER','MEMBER')),
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (guild_id, user_id)
);

CREATE TABLE messages (
id             SERIAL PRIMARY KEY,
sender_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
recipient_id   INTEGER REFERENCES users(id) ON DELETE CASCADE,
guild_id       INTEGER REFERENCES guilds(id) ON DELETE CASCADE,
content        TEXT NOT NULL,
created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted        BOOLEAN NOT NULL DEFAULT FALSE,
CHECK (
(recipient_id IS NOT NULL AND guild_id IS NULL)
OR (recipient_id IS NULL AND guild_id IS NOT NULL)
)
);

function simulateMatch(matchId: MatchId, repo: MatchSimulationRepo): void {
const ctx = repo.loadMatchContext(matchId);
const state = initState(ctx);

for (let minute = 1; minute <= 90; minute++) {
stepMinute(state, minute);
}

applyPostMatch(state, repo);
repo.saveResult(state);
}

// Express-style pseudo-code (server-side)
router.post('/api/auth/login', async (req, res) => {
const dto = loginRequestSchema.parse(req.body);
const result = await loginUseCase.execute(dto);

if (result.isErr()) {
return res.status(401).json({ error: result.error.message });
}

const { accessToken, refreshToken, user } = result.value;
return res.json({ accessToken, refreshToken, user });
});

router.post('/api/teams', authMiddleware('USER'), async (req, res) => {
const dto = createTeamSchema.parse(req.body);
const userId = req.auth.userId;
const team = await createTeamUseCase.execute({ ...dto, ownerId: userId });
return res.status(201).json(team);
});

router.post('/api/matches/:id/simulate', adminOnly, async (req, res) => {
const matchId = Number(req.params.id);
await simulateMatchUseCase.execute({ matchId });
return res.status(202).json({ status: 'QUEUED' });
});