--
-- The 10 figurines
--
CREATE TABLE IF NOT EXISTS figurines(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    color TEXT NOT NULL,
    nb_legs INTEGER
);

INSERT INTO figurines(id, name, color, nb_legs) VALUES (0, 'bird', 'red', 2);
INSERT INTO figurines(name, color, nb_legs) VALUES ('bird', 'green', 3);
INSERT INTO figurines(name, color, nb_legs) VALUES ('bird', 'green', 2);
INSERT INTO figurines(name, color, nb_legs) VALUES ('cat', 'black', 4);
INSERT INTO figurines(name, color, nb_legs) VALUES ('cat', 'white', 3);
INSERT INTO figurines(name, color, nb_legs) VALUES ('cat', 'red', 4);
INSERT INTO figurines(name, color, nb_legs) VALUES ('snake', 'green', 0);
INSERT INTO figurines(name, color, nb_legs) VALUES ('fox', 'red', 4);
INSERT INTO figurines(name, color, nb_legs) VALUES ('fox', 'black', 4);
INSERT INTO figurines(name, color, nb_legs) VALUES ('centipede', 'white', 10);


--
-- Bertrand's collection
--
CREATE TABLE IF NOT EXISTS collection(
    figurine_id INTEGER NOT NULL,
    drawer_color TEXT NOT NULL,
    drawer_room TEXT NOT NULL,
    CONSTRAINT pk_collection PRIMARY KEY (figurine_id, drawer_color, drawer_room),
    CONSTRAINT fk_figurine_id FOREIGN KEY (figurine_id) REFERENCES figurines(id)
);

INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (7, 'red', 'living_room');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (0, 'red', 'living_room');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (5, 'red', 'living_room');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (3, 'red', 'living_room');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (3, 'black', 'kitchen');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (0, 'black', 'kitchen');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (9, 'black', 'kitchen');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (1, 'black', 'kitchen');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (4, 'red', 'bedroom');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (5, 'red', 'bedroom');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (1, 'red', 'bedroom');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (2, 'red', 'bedroom');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (4, 'black', 'bedroom');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (5, 'black', 'bedroom');
INSERT INTO collection(figurine_id, drawer_color, drawer_room) VALUES (9, 'black', 'bedroom');

