-- Need to excplicitly enable primary keys
PRAGMA foreign_keys = ON;

-- Delete the table if it exists
drop table if exists game;
drop table if exists games;
drop table if exists teams;


-- Create a table to store teams
create table teams(
       id integer primary key autoincrement,
       name text
);

-- Create a table for a game
create table games(
        id integer primary key autoincrement,
        h_team_id char,
        o_team_id char,
        h_points integer,
        o_points integer,
        date text,
        FOREIGN KEY(h_team_id) REFERENCES teams(id),
        FOREIGN KEY(o_team_id) REFERENCES teams(id)
);

-- When inserting into a table pass null if you have an autoincrementing column

-- Seed with some sample data
insert into teams values(null, 'Boston Celtics');
insert into teams values(null, 'Houston Rockets');
insert into teams values(null, 'Indiana Pacers');
insert into teams values(null, 'Memphis Grizzlies');
insert into teams values(null, 'Miami Heat');
insert into teams values(null, 'Milwaukee Bucks');

-- Insert sample game
insert into games values(null, 1, 2, 80, 87, '12-01-2019')
insert into games values(null, 1, 2, 99, 80, '12-01-2019')
insert into games values(null, 1, 2, 101, 92, '12-01-2019')
insert into games values(null, 1, 2, 82, 94, '12-01-2019')