DROP TABLE IF EXISTS Review;
DROP TABLE IF EXISTS Examination;
DROP TABLE IF EXISTS IsApprovedMedicineRecepit;
DROP TABLE IF EXISTS MedicineRecepit;
DROP TABLE IF EXISTS Report;
DROP TABLE IF EXISTS Comment;
DROP TABLE IF EXISTS Specialist;
DROP TABLE IF EXISTS Doctor;
DROP TABLE IF EXISTS Patient;
DROP TABLE IF EXISTS AppUser;

CREATE TABLE AppUser (
    ssn VARCHAR(13) PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,
    firstName VARCHAR(100) NOT NULL,
    lastName VARCHAR(100) NOT NULL
);
CREATE TABLE Patient (
    userId VARCHAR(13) PRIMARY KEY REFERENCES AppUser(ssn),
    patientId VARCHAR(13)
);
CREATE TABLE Doctor (
    userId VARCHAR(13) PRIMARY KEY REFERENCES AppUser(ssn),
    workingHours VARCHAR(50),
    licenseNumber VARCHAR(50),
    hospitalId VARCHAR(50)
);
CREATE TABLE Specialist (
    userId VARCHAR(13) PRIMARY KEY REFERENCES AppUser(ssn),
    licenseNumber VARCHAR(50),
    hospitalId VARCHAR(50)
);
CREATE TABLE Comment (
    commentId INT PRIMARY KEY,
    createdBy VARCHAR(13) REFERENCES AppUser(ssn),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    comment VARCHAR(300)
);
CREATE TABLE Report (
    reportId INT PRIMARY KEY,
    description VARCHAR(300),
    isFinished BOOLEAN
);
CREATE TABLE MedicineRecepit (
    medicineRecepitId INT PRIMARY KEY,
    reportId INT REFERENCES Report(reportId),
    medicineName VARCHAR(50),
    quantity INT
);
CREATE TABLE IsApprovedMedicineRecepit (
    approvalId INT PRIMARY KEY,
    receiptId INT REFERENCES MedicineRecepit(medicineRecepitId),
    approvedBy VARCHAR(13) REFERENCES AppUser(ssn),
    IsApproved BOOLEAN
);
CREATE TABLE Examination (
    examinationId INT PRIMARY KEY,
    patientId VARCHAR(13) REFERENCES AppUser(ssn),
    doctorId VARCHAR(13) REFERENCES AppUser(ssn),
    specialistId VARCHAR(13) REFERENCES AppUser(ssn),
    dateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Review (
    reviewId INT PRIMARY KEY,
    patientId VARCHAR(13) REFERENCES AppUser(ssn),
    doctorId VARCHAR(13) REFERENCES AppUser(ssn),
    rank INT CHECK (rank >= 1 AND rank <= 5)
);
