if (Exists (Select Table_name from SysTable where Table_name='SpaMedicalOffices')) then
  Drop table SpaMedicalOffices
end if

GO

create table SpaMedicalOffices
(
  N integer not null primary key default autoincrement,
  Name varchar(60)
)

GO

if (Exists (Select Table_name from SysTable where Table_name='SpaWorkers')) then
  Drop table SpaWorkers
end if

GO

create table SpaWorkers
(
  N integer not null primary key default autoincrement,
  ShortName varchar(30),
  FullName  varchar(60),
  Status    integer
)

GO

if (select count(*) from sys.syscolumns where tname='Services' and cname='ServiceType')=0 then

alter table Services
Add ServiceType integer default 0

end if

GO

COMMENT ON
COLUMN Services.ServiceType
is '1-standartna usluga 2-spa usluga'

GO

if (Exists (Select Table_name from SysTable where Table_name='SpaReservations')) then
  Drop table SpaReservations
end if

GO

create table  SpaReservations
(
 N integer not null primary key default autoincrement,
 GuestFK            integer not null,
 BeginDate          date not null,
 BeginTime          time not null,
 TimeSteps          integer not null,
 MedicalOffice      integer not null,
 MedicalService     integer not null,
 SpaWorker          integer  not null,
 ReservetionType    integer  default 0,
 IfPayed            integer  default 0
)

GO

ALTER TABLE "SpaReservations"
	ADD FOREIGN KEY "Fk_MedicalOffice" ("MedicalOffice")
	REFERENCES "SpaMedicalOffices" ("N") on update cascade on delete cascade


GO

ALTER TABLE "SpaReservations"
	ADD FOREIGN KEY "Fk_MedicalService" ("MedicalService")
	REFERENCES "Services" ("ServiceNo") on update cascade on delete cascade
	
GO

ALTER TABLE "SpaReservations"
	ADD FOREIGN KEY "Fk_SpaWorker" ("SpaWorker")
	REFERENCES "SpaWorkers" ("N") on update cascade on delete cascade
	
GO

COMMENT ON
COLUMN SpaReservations.GuestFK
is 'vanshen kluch ot Guests'

GO

COMMENT ON
COLUMN SpaReservations.BeginDate
is 'data na izvarshavane na spa procedurata'

GO

COMMENT ON
COLUMN SpaReservations.BeginTime
is 'nachalen chas moze da kragal ili polovin chas primerno 7:00 ili 7:30'

GO

COMMENT ON
COLUMN SpaReservations.MedicalOffice
is 'vanshen kluch kum tablicata c spa kabinentite'

GO

COMMENT ON
COLUMN SpaReservations.MedicalService
is 'spa uslugi ot Survices'

GO

COMMENT ON
COLUMN SpaReservations.SpaWorker
is 'spa rabotnici izvarshvashti spa uslugata'

GO

COMMENT ON
COLUMN SpaReservations.ReservetionType
is '1-otchakvana 2-obrabotena 3-otmenena'

GO

COMMENT ON
COLUMN SpaReservations.IfPayed
is '0-visiasta 1-platena'