CREATE DATABASE Cardiceo
GO

USE Cardiceo
GO

CREATE TABLE Personnel(
    ID INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    surname VARCHAR(20) NOT NULL,
    dept VARCHAR(20) FOREIGN KEY REFERENCES Department,
    employed DATE NOT NULL,
    salary FLOAT
)
GO

CREATE TABLE Department(
    name VARCHAR(20) PRIMARY KEY,
    president INTEGER FOREIGN KEY REFERENCES Personnel
)
GO

CREATE TABLE Specialist(
    PersID INTEGER FOREIGN KEY REFERENCES Personnel,
    rank VARCHAR(10) NOT NULL,  --Doctor/Assistant/Intern
    PRIMARY KEY ("PersID")
)
GO

CREATE TABLE Offer(
    Spec_ID INTEGER FOREIGN KEY REFERENCES Specialist,
    Service_ID INTEGER FOREIGN KEY REFERENCES Service,
    price FLOAT,
    PRIMARY KEY ("Spec_ID", "Service_ID")
)
GO

CREATE TABLE Service(
    ID INTEGER PRIMARY KEY,
    name VARCHAR(20),
    description text
)
GO

CREATE TABLE Visit(
    ID INTEGER PRIMARY KEY,
    treatment INTEGER FOREIGN KEY REFERENCES Offer,
    Patient_ID INTEGER FOREIGN KEY REFERENCES Patient,
    reg_date DATE NOT NULL,
    visit_date DATE NOT NULL,
    deposit_amt FLOAT NOT NULL,
    deposit_form VARCHAR(9) NOT NULL,   --cash/transfer
    STATUS VARCHAR(14) NOT NULL,            --completed/not completed/waiting,
    grade INTEGER
)
GO

CREATE TABLE Patient(
    PESEL VARCHAR(11) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    surname VARCHAR(20) NOT NULL,
    address VARCHAR(50) NOT NULL,
    phone_nr VARCHAR(10) NOT NULL
)
GO