-- credits: André Restivo https://web.fe.up.pt/~arestivo
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS stop;
DROP TABLE IF EXISTS trip;
PRAGMA foreign_keys = OFF; -- special case: FK loop between city and country
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS country;
PRAGMA foreign_keys = ON;

CREATE TABLE trip (
    day TEXT PRIMARY KEY,
    size INTEGER NOT NULL -- how many people
);

CREATE TABLE country (
    name TEXT PRIMARY KEY,
    language TEXT,
    capital TEXT REFERENCES city
);

CREATE TABLE city (
    name TEXT PRIMARY KEY,
    score INTEGER,
    country TEXT REFERENCES country
);

CREATE TABLE stop (
    name TEXT REFERENCES city,
    day TEXT REFERENCES trip,
    days INTEGER,         -- number of days spent in this city
    PRIMARY KEY (name, day)
);

INSERT INTO trip (day, size) VALUES ('2006-12-20', 8);
INSERT INTO trip (day, size) VALUES ('2007-05-24', 12);
INSERT INTO trip (day, size) VALUES ('2008-03-02', 4);
INSERT INTO trip (day, size) VALUES ('2007-01-01', 3);

PRAGMA foreign_keys = OFF; -- special case: FK loop between city and country
INSERT INTO country (name, language, capital) VALUES ('Australia', 'English', 'Canberra');
INSERT INTO country (name, language, capital) VALUES ('Brazil', 'Portugues', 'Brazilia');
INSERT INTO country (name, language, capital) VALUES ('Spain', 'Spanish', 'Madrid');
INSERT INTO country (name, language, capital) VALUES ('Turkey', 'Turkish', 'Ankara');
INSERT INTO country (name, language, capital) VALUES ('England', 'English', 'London');
INSERT INTO country (name, language, capital) VALUES ('France', 'French', 'Paris');
INSERT INTO country (name, language, capital) VALUES ('Belgium', 'French', 'Brussels');
INSERT INTO country (name, language, capital) VALUES ('Portugal', 'Portuguese', 'Lisbon');
INSERT INTO country (name, language, capital) VALUES ('United States', 'English', 'Washington');

INSERT INTO city (name, score, country) VALUES ('Istambul', 3, 'Turkey');
INSERT INTO city (name, score, country) VALUES ('Madrid', 4, 'Spain');
INSERT INTO city (name, score, country) VALUES ('Lisbon', 3, 'Portugal');
INSERT INTO city (name, score, country) VALUES ('Ankara', 5, 'Turkey');
INSERT INTO city (name, score, country) VALUES ('Paris', 3, 'France');
INSERT INTO city (name, score, country) VALUES ('Brussels', 2, 'Belgium');
INSERT INTO city (name, score, country) VALUES ('Sidney', 4, 'Australia');
INSERT INTO city (name, score, country) VALUES ('Canberra', 3, 'Australia');
INSERT INTO city (name, score, country) VALUES ('Brazilia', 3, 'Brazil');
INSERT INTO city (name, score, country) VALUES ('Rio de Janeiro', 5, 'Brazil');
INSERT INTO city (name, score, country) VALUES ('Sao Paulo', 4, 'Brazil');
INSERT INTO city (name, score, country) VALUES ('Porto', 5, 'Portugal');
INSERT INTO city (name, score, country) VALUES ('Barcelona', 5, 'Spain');
INSERT INTO city (name, score, country) VALUES ('London', 4, 'England');
INSERT INTO city (name, score, country) VALUES ('Nice', 4, 'France');
INSERT INTO city (name, score, country) VALUES ('Lyon', 4, 'France');
INSERT INTO city (name, score, country) VALUES ('Bruges', 3, 'Belgium');
INSERT INTO city (name, score, country) VALUES ('New York', 5, 'United States');
INSERT INTO city (name, score, country) VALUES ('Washington', 3, 'United States');
PRAGMA foreign_keys = ON;

INSERT INTO stop (name, day, days) VALUES ('Porto', '2007-05-24', 2);
INSERT INTO stop (name, day, days) VALUES ('Barcelona', '2007-05-24', 4);
INSERT INTO stop (name, day, days) VALUES ('London', '2007-05-24', 3);
INSERT INTO stop (name, day, days) VALUES ('London', '2006-12-20', 2);
INSERT INTO stop (name, day, days) VALUES ('New York', '2006-12-20', 12);
INSERT INTO stop (name, day, days) VALUES ('Nice', '2008-03-02', 5);
INSERT INTO stop (name, day, days) VALUES ('Istambul', '2008-03-02', 3);
INSERT INTO stop (name, day, days) VALUES ('Ankara', '2008-03-02', 3);
INSERT INTO stop (name, day, days) VALUES ('Lyon', '2007-05-24', 3);
INSERT INTO stop (name, day, days) VALUES ('Madrid', '2008-03-02', 2);
INSERT INTO stop (name, day, days) VALUES ('Porto', '2008-03-02', 3);
INSERT INTO stop (name, day, days) VALUES ('Brazilia', '2007-01-01', 3);
INSERT INTO stop (name, day, days) VALUES ('Rio de Janeiro', '2007-01-01', 6);
INSERT INTO stop (name, day, days) VALUES ('Washington', '2007-01-01', 2);
