--CREATE DATABASE Cardiceo
--GO

USE Cardiceo
GO

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

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

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

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

CREATE TABLE Offer(
    Offer_ID INTEGER PRIMARY KEY,
    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 Patient(
    PESEL VARCHAR(11) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(50) NOT NULL,
    phone_nr VARCHAR(10) NOT NULL
)
GO

CREATE TABLE Visit(
    ID INTEGER PRIMARY KEY,
    treatment INTEGER FOREIGN KEY REFERENCES Offer(Offer_ID),
    Patient_ID VARCHAR(11) 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

--drop table Visit
--drop table Patient
--drop table Service
--drop table Offer
--drop table Specialist
--drop table Personnel
--drop table Department