========================
Doctor-patients
========================
CREATE TABLE Doctors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    specialty TEXT
);

CREATE TABLE Patients (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    insurance TEXT,
    birthday DATE
);

CREATE TABLE Visits (
    id SERIAL PRIMARY KEY,
    doctor_id INT REFERENCES Doctors(id),
    patient_id INT REFERENCES Patients(id),
    date TIMESTAMP
);

CREATE TABLE Diseases (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

CREATE TABLE Diagnoses (
    id SERIAL PRIMARY KEY,
    visit_id INT REFERENCES Visits(id),
    disease_id INT REFERENCES Diseases(id),
    notes TEXT
);


========================
CraigsList
========================
CREATE TABLE Regions (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Categories (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    encrypted_password TEXT NOT NULL,
    preferred_region_id INT REFERENCES Regions(id)
);

CREATE TABLE Posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    text TEXT,
    location TEXT,
    user_id INT REFERENCES Users(id),
    region_id INT REFERENCES Regions(id),
    category_id INT REFERENCES Categories(id)
);


======================================
Soccer-leagues
==================================

CREATE TABLE Teams (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL
);

CREATE TABLE Players (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    birthday DATE,
    height INT,
    current_team_id INT REFERENCES Teams(id)
);

CREATE TABLE Referees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Season (
    id SERIAL PRIMARY KEY,
    start_date DATE,
    end_date DATE
);

CREATE TABLE Matches (
    id SERIAL PRIMARY KEY,
    home_team_id INT REFERENCES Teams(id),
    away_team_id INT REFERENCES Teams(id),
    location TEXT NOT NULL,
    date DATE,
    start_time TIME,
    season_id INT REFERENCES Season(id),
    head_referee_id INT REFERENCES Referees(id),
    assistant_referee_1_id INT REFERENCES Referees(id),
    assistant_referee_2_id INT REFERENCES Referees(id)
);

CREATE TABLE Results (
    id SERIAL PRIMARY KEY,
    team_id INT REFERENCES Teams(id),
    match_id INT REFERENCES Matches(id),
    result TEXT CHECK (result IN ('win', 'loss', 'draw'))
);

CREATE TABLE Goals (
    id SERIAL PRIMARY KEY,
    player_id INT REFERENCES Players(id),
    match_id INT REFERENCES Matches(id)
);

CREATE TABLE Lineups (
    id SERIAL PRIMARY KEY,
    player_id INT REFERENCES Players(id),
    match_id INT REFERENCES Matches(id),
    team_id INT REFERENCES Teams(id)
);