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

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS Tracking;
DROP TABLE IF EXISTS Package;
DROP TABLE IF EXISTS Courier;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS DeliveryCenter;

CREATE TABLE Person (
    vat INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    phone_number TEXT
);

CREATE TABLE Customer (
    vat INTEGER PRIMARY KEY REFERENCES Person,
    address TEXT NOT NULL,
    closest_delivery_center INTEGER NOT NULL REFERENCES DeliveryCenter
);

CREATE TABLE Courier (
    vat INTEGER PRIMARY KEY REFERENCES Person,
    delivery_center INTEGER NOT NULL REFERENCES DeliveryCenter
);

CREATE TABLE Package (
    identifier INTEGER PRIMARY KEY,
    weight INTEGER NOT NULL CHECK (weight > 0),
    priority TEXT NOT NULL CHECK (priority = 'normal' OR priority = 'urgent'),
    sender INTEGER NOT NULL REFERENCES Customer,
    recipient INTEGER NOT NULL REFERENCES Customer,
    drop_off INTEGER NOT NULL REFERENCES DeliveryCenter,
    courier INTEGER NOT NULL REFERENCES Courier
);

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

CREATE TABLE Tracking (
    package INTEGER REFERENCES Package,
    delivery_center INTEGER REFERENCES DeliveryCenter,
    date_arrival TEXT,
    date_departure TEXT NOT NULL,
    PRIMARY KEY(package, delivery_center),
    CHECK(date_departure IS NULL OR strftime('%s', date_departure) > strftime('%s', date_arrival))
);
