/*suzdava tablicata koqto pazi max. broi ot vsqka kategoriq staq koito mogat da se rezervirat online*/
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('OnlineRooms'))) THEN
Drop Table OnlineRooms
END IF
GO

create table OnlineRooms
(
id integer not null,
maxbroi integer default 0 not null,
)

GO

insert into onlinerooms(id)
select c.class
from Classes c
where c.class <> 0 and
      c.class not in (select id from onlinerooms)

GO

/*Sledvashtite 3 trigger-a prosto otrazqvat promenite v Classes v OnlineRooms*/
if exists (select * from SYSTRIGGERS where LCase(trigname)=lcase('TrigUpdateOnlineRooms'))
  then
  drop trigger TrigUpdateOnlineRooms
end if;

CREATE TRIGGER "TrigUpdateOnlineRooms" AFTER UPDATE
ORDER 1 ON "DBA"."Classes"
 REFERENCING OLD AS old_name NEW AS new_name
FOR EACH ROW
BEGIN
    update OnlineRooms
    set id=new_name.class
	where id=old_name.class
END

GO

if exists (select * from SYSTRIGGERS where LCase(trigname)=lcase('TrigDeleteFromOnlineRooms'))
  then
  drop trigger TrigDeleteFromOnlineRooms
end if;

CREATE TRIGGER "TrigDeleteFromOnlineRooms" AFTER DELETE
ORDER 2 ON "DBA"."Classes"
 REFERENCING OLD AS old_name
FOR EACH ROW
BEGIN
    delete from OnlineRooms
    where id=old_name.class
END

GO

if exists (select * from SYSTRIGGERS where LCase(trigname)=lcase('TrigInsertIntoOnlineRooms'))
  then
  drop trigger TrigInsertIntoOnlineRooms
end if;

CREATE TRIGGER "TrigInsertIntoOnlineRooms" AFTER INSERT
ORDER 1 ON "DBA"."Classes"
 REFERENCING NEW AS new_name 
FOR EACH ROW
BEGIN
	insert into OnlineRooms values( new_name.Class, 0 )
END

GO

/*V tazi tablica se pazqt periodite v koito online rezervaciite NE sa dostupni*/
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('BlackPeriods'))) THEN
Drop Table BlackPeriods
END IF

GO

create table BlackPeriods
(
N integer not null default autoincrement primary key,
fromdate date not null,
todate date not null,
notes varchar(50),
)

GO

/*Sazdava registur za avtomatichno potvurjdavane na online rezervaciite*/
if (select count(*) from Registers where RegisterName='AutoConfirmReserve')=0
then
    insert into Registers(RegisterName, IntStojnost, Komentar ) values('AutoConfirmReserve',1, 'AutoConfirmReserve')
end if;


GO

/*Tova view pulni grida v OnlineReservationForm v Hotela*/
if (Exists (Select Table_name from SysTable where Table_name='v_OnlineRooms')) then
  Drop View v_OnlineRooms
end if

GO

create view
DBA.v_OnlineRooms as
select c.Description,o.maxbroi from
DBA.Classes as c, DBA.OnlineRooms as o
where c.class <> 0 and c.Class = o.id

GO

/*Tova view proverqva dali ima nalichni stai za online rezervacii kato proverqva dali ima nastaneni po online*/
/*rezervacii, dali ima otmeneni ili dali ima takiva s iztekla data. Ideqta tuk e che ako tozi broi stai stanat*/
/*ravni na maxbroi v table OnlineRooms znachi sme nadvishili limita.*/
if (Exists (Select Table_name from SysTable where Table_name='v_AvailableRoomsForOnline')) then
  Drop View v_AvailableRoomsForOnline
end if

GO

create view DBA.v_AvailableRoomsForOnline as
(
select RoomClass, count(*) as broi  from ReservePlan where ReserveNo in
(select ReserveNo from Reserve where KodAdmin = -1 and Status = 1  and DateReserveDt >= now())
and Status = 1
group by RoomClass order by RoomClass
)

GO

/*Prava*/
if (select count(*) from PermissionTypes where n=5000)=0
then
    insert into PermissionTypes values(5000, 'Online Резервации', 0)
end if;

GO

if (select count(*) from PermissionTags where TagNo=5000)=0
then
    insert into PermissionTags values(5000, 5000, 'Дава права за Online Резервации')
end if;

GO

FOR f AS curs scroll CURSOR FOR
SELECT n as nomer
FROM admingroups
DO
 if (select count(*) from Permissions where GroupNo=nomer and PermissionNo=5000)=0 then
      if nomer = 1 then
           insert into permissions values ( nomer, 5000, 0 )
      else
           insert into permissions values ( nomer, 5000, 1 )
      end if;
 end if;
END FOR;