DROP TABLE "Patient Medication" IF EXISTS;
DROP TABLE "Medication" IF EXISTS;
DROP TABLE "Payment" IF EXISTS;
DROP TABLE "Schedule" IF EXISTS;
DROP TABLE "Assignment" IF EXISTS;
DROP TABLE "Therapists Number" IF EXISTS;
DROP TABLE "Phone Number" IF EXISTS;
DROP TABLE "Patient" IF EXISTS;
DROP TABLE "Psychiatrist" IF EXISTS;
DROP TABLE "Medical Doctor" IF EXISTS;
DROP TABLE "Therapist" IF EXISTS;

CREATE TABLE "Psychiatrist" (
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"First Name" VARCHAR(25) NOT NULL,
"Surname" VARCHAR(25) NOT NULL,
"Gender" CHAR(6),
"Street and number" VARCHAR(50),
"City" VARCHAR(25),
"Postal code" CHAR(5),
"State" CHAR(2),
"Phone Number" VARCHAR(10)
);

CREATE TABLE "Medical Doctor" (
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"First Name" VARCHAR(25) NOT NULL,
"Surname" VARCHAR(25) NOT NULL,
"Gender" CHAR(6),
"Street and number" VARCHAR(50),
"City" VARCHAR(25),
"Postal code" VARCHAR(5),
"State" CHAR(2),
"Phone Number" VARCHAR(10)
);

CREATE TABLE "Patient" (
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"First Name" VARCHAR(25) NOT NULL,
"Surname" VARCHAR(25) NOT NULL,
"Gender" CHAR(6),
"Date of Birth" DATE,
"Street and number" VARCHAR(50),
"City" VARCHAR(25),
"Postal code" VARCHAR(5),
"State" CHAR(2),
"Diagnosis" VARCHAR(60),
"Medical Doctor ID" INTEGER,
"Psychiatrist ID" INTEGER,
"Time of registry" TIMESTAMP, 
CONSTRAINT "CK_PAT_GNDR" CHECK( "Gender" in ( 'Male', 'Female' ) ),
CONSTRAINT FK_PAT_PSY FOREIGN KEY ("Psychiatrist ID") REFERENCES "Psychiatrist" ("ID Number"),
CONSTRAINT FK_PAT_DOC FOREIGN KEY ("Medical Doctor ID") REFERENCES "Medical Doctor" ("ID Number")
);

CREATE TABLE "Phone Number" (
"Phone ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Patient ID" INTEGER NOT NULL,
"Number" VARCHAR(10),
"Description" VARCHAR(10),
CONSTRAINT FK_PAT_PHN FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number")
);

CREATE TABLE "Therapist" (
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"First Name" VARCHAR(25) NOT NULL,
"Surname" VARCHAR(25) NOT NULL,
"Gender" CHAR(6),
"Street and number" VARCHAR(50),
"City" VARCHAR(25),
"Postal code" VARCHAR(5),
"State" CHAR(2),
"Tax number" VARCHAR(20),
"Academic degree" VARCHAR(25),
"License number" VARCHAR(15),
"Hiring date" DATE NOT NULL,
"Termination date" DATE,
CONSTRAINT "CK_THP_GNDR" CHECK( "Gender" in ( 'Male', 'Female' ) ),
CONSTRAINT "CK_TERM_DT" CHECK( "Termination date" > "Hiring date" )
);

CREATE TABLE "Therapists Number" (
"Phone ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Therapist ID" INTEGER,
"Number" VARCHAR(10),
"Description" VARCHAR(10),
CONSTRAINT FK_THP_PHN FOREIGN KEY ("Therapist ID") REFERENCES "Therapist" ("ID Number")
);

CREATE TABLE "Assignment" (
"Assignment ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Patient ID" INTEGER NOT NULL,
"Therapist ID" INTEGER NOT NULL,
"Date assigned" DATE DEFAULT CURRENT_DATE NOT NULL,
"Date case closed" DATE,
CONSTRAINT FK_PAT_ASMT FOREIGN KEY ("Patient ID") REFERENCES "Patient" 
("ID Number"),
CONSTRAINT FK_THP_ASMT FOREIGN KEY ("Therapist ID") REFERENCES "Therapist" ("ID Number"),
CONSTRAINT "CK_CLOSE_DT" CHECK( "Date case closed" >= "Date assigned" )
);

CREATE TABLE "Schedule" (
"Schedule ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Assignment ID" INTEGER,
"Slot date" DATE NOT NULL,
"Slot hour" TIME NOT NULL,
"Status of the session" VARCHAR(20),
CONSTRAINT FK_SCH_ASMT FOREIGN KEY ("Assignment ID") REFERENCES 
"Assignment" ("Assignment ID")
);

CREATE TABLE "Payment" (
"Payment ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Patient ID" INTEGER NOT NULL,
"Date and time of Payment" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
"Amount" DECIMAL (10, 2) NOT NULL,
"Notes" VARCHAR(100),
"Result" CHAR(6) NOT NULL,
CONSTRAINT FK_PAT_PYMNT FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number"),
CONSTRAINT CK_DBT CHECK("Result" IN ('DEBIT', 'CREDIT'))
);

CREATE TABLE "Medication" (
"Medication ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Name" VARCHAR(30) NOT NULL,
"Description" VARCHAR(256)
);

CREATE TABLE "Patient Medication" (
"Patient ID" INTEGER NOT NULL,
"Medication ID" INTEGER NOT NULL,
"Dosage" VARCHAR(50),
"Start date" DATE DEFAULT CURRENT_DATE,
"End date" DATE,
CONSTRAINT PK_PAT_MED PRIMARY KEY ("Patient ID", "Medication ID" ),
CONSTRAINT FK_MED_PAT FOREIGN KEY ("Medication ID") REFERENCES "Medication" ("Medication ID"),
CONSTRAINT FK_PAT_MED FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number"),
CONSTRAINT CK_END_DT CHECK( "End date" >= "Start date" )
);