CREATE TABLE players (

    id BIGSERIAL PRIMARY KEY,

    nickname TEXT NOT NULL,

    real_name TEXT NOT NULL,

    role TEXT NOT NULL,

    status TEXT NOT NULL,

    joined_at TIMESTAMP NOT NULL,

    photo_url TEXT NOT NULL,

    bio TEXT NOT NULL

);

CREATE TABLE gear (

    id BIGSERIAL PRIMARY KEY,

    player_id BIGINT NOT NULL,

    manufacturer TEXT NOT NULL,

    model TEXT NOT NULL,

    amazon_link TEXT NOT NULL,

    CONSTRAINT gear_player_fk

        FOREIGN KEY (player_id)

        REFERENCES players(id)

        ON DELETE CASCADE

);

CREATE TABLE matches (

    id BIGSERIAL PRIMARY KEY,

    time TIMESTAMP NOT NULL,

    versus TEXT NOT NULL,

    result_us SMALLINT,

    result_enemy SMALLINT

);

CREATE TABLE skins (

    id BIGSERIAL PRIMARY KEY,

    player_id BIGINT NOT NULL,

    weapon TEXT NOT NULL,

    paint TEXT NOT NULL,

    wear DOUBLE PRECISION NOT NULL DEFAULT 0.0,

    pattern INTEGER NOT NULL DEFAULT 0,

    nametag TEXT,

    csfloat_link TEXT NOT NULL,

    CONSTRAINT skins_player_fk

        FOREIGN KEY (player_id)

        REFERENCES players(id)

        ON DELETE CASCADE

);

CREATE TABLE specs (

    id BIGSERIAL PRIMARY KEY,

    player_id BIGINT NOT NULL,

    cpu_manufacturer TEXT NOT NULL,

    cpu_model TEXT NOT NULL,

    gpu_manufacturer TEXT NOT NULL,

    gpu_model TEXT NOT NULL,

    ram_size_gb INTEGER NOT NULL,

    ram_manufacturer TEXT NOT NULL,

    gpu_amazon_link TEXT NOT NULL,

    cpu_amazon_link TEXT NOT NULL,

    ram_amazon_link TEXT NOT NULL,

    CONSTRAINT specs_player_fk

        FOREIGN KEY (player_id)

        REFERENCES players(id)

        ON DELETE CASCADE

);