-- Solution proposal by @zeornelas on github
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS Compatibility;
DROP TABLE IF EXISTS Model;
DROP TABLE IF EXISTS Part;
DROP TABLE IF EXISTS Service;
DROP TABLE IF EXISTS PartType;
DROP TABLE IF EXISTS Make;
DROP TABLE IF EXISTS Car;
DROP TABLE IF EXISTS Owner;
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Person;

CREATE TABLE Person (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    address TEXT
);

CREATE TABLE Employee (
    id INTEGER PRIMARY KEY REFERENCES Person
);

CREATE TABLE Owner (
    id INTEGER PRIMARY KEY REFERENCES Person,
    phone_number TEXT
);

CREATE TABLE Service (
    id INTEGER PRIMARY KEY,
    car_kms INTEGER NOT NULL CHECK(car_kms > 0),
    hours_spent REAL CHECK(hours_spent IS NULL OR hours_spent > 0),
    date_adm TEXT NOT NULL,
    date_finish TEXT,
    employee INTEGER NOT NULL REFERENCES Employee,
    car INTEGER NOT NULL REFERENCES Car,
    CHECK(date_finish IS NULL OR strftime('%s', date_finish) > strftime('%s', date_adm))
);

CREATE TABLE Car (
    plate TEXT PRIMARY KEY,
    color TEXT,
    owner INTEGER NOT NULL REFERENCES Owner,
    model INTEGER NOT NULL REFERENCES Model
);

CREATE TABLE Model (
    id INTEGER PRIMARY KEY,
    name TEXT,
    make TEXT NOT NULL REFERENCES Make
);

CREATE TABLE Make (
    name TEXT PRIMARY KEY
);

CREATE TABLE Part (
    serial_number INTEGER PRIMARY KEY,
    price INTEGER NOT NULL CHECK(price > 0),
    service INTEGER REFERENCES Service,
    type INTEGER NOT NULL REFERENCES PartType
);

CREATE TABLE PartType (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Compatibility (
    model INTEGER REFERENCES Model,
    part INTEGER REFERENCES Part,
    PRIMARY KEY(model, part)
);
