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

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS BreedCondition;
DROP TABLE IF EXISTS ConditionIdentified;
DROP TABLE IF EXISTS Appointment;
DROP TABLE IF EXISTS Animal;
DROP TABLE IF EXISTS Breed;
DROP TABLE IF EXISTS Condition;
DROP TABLE IF EXISTS Owner;
DROP TABLE IF EXISTS Physician;

CREATE TABLE Animal (
  id INTEGER PRIMARY KEY,
  name TEXT,
  breed TEXT NOT NULL REFERENCES Breed,
  owner INTEGER REFERENCES Owner
);

CREATE TABLE Breed (
    name TEXT PRIMARY KEY
);

CREATE TABLE Condition (
    name TEXT PRIMARY KEY,
    s_name TEXT NOT NULL UNIQUE
);

CREATE TABLE Appointment (
    id INTEGER PRIMARY KEY,
    date  TEXT NOT NULL,
    time TEXT NOT NULL,
    animal INTEGER NOT NULL REFERENCES Animal,
    physician INTEGER NOT NULL REFERENCES Physician
);

CREATE TABLE ConditionIdentified (
    condition TEXT REFERENCES Condition,
    appointment INTEGER REFERENCES Appointment,
    PRIMARY KEY(condition, appointment)
);

CREATE TABLE BreedCondition (
    breed TEXT REFERENCES Breed,
    condition TEXT REFERENCES Condition,
    PRIMARY KEY(breed, condition)
);

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

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