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

GO

Create table SpaInvoices
(
	"InvoiceNo"     		integer NOT NULL ,
	"Status"        		integer NOT NULL ,
	"PayHow"        		integer NOT NULL ,
	"InvoiceType"   		integer NOT NULL ,
	"InvoiceDate"   		float NOT NULL ,
	"BankAccount"   		varchar(16) NULL ,
	"Bank"  			varchar(30) NULL ,
	"BankTown"      		varchar(15) NOT NULL ,
	"Upolnomosten"  		varchar(25) NOT NULL ,
	"UpolnomDate"   		varchar(15) NOT NULL ,
	"UpolnomNum"    		varchar(15) NOT NULL ,
	"KodAdmin"      		integer NOT NULL ,
	"Whom"  			varchar(50) NOT NULL ,
	"MOL"   			varchar(25) NOT NULL ,
	"WhomTown"      		varchar(50) NOT NULL ,
	"WhomDanNum"    		varchar(15) NOT NULL ,
	"Bulstat"       		varchar(13) NOT NULL ,
	"KodCurrency"   		integer NOT NULL ,
	"TotalNoVAT"    		numeric(12,4) NOT NULL ,
	"VAT"   			numeric(12,4) NOT NULL ,
	"GrandTotal"    		numeric(12,4) NOT NULL ,
	"CancelInvoiceNo"       	integer NULL ,
	"TrueInvoiceNo" 		integer NULL DEFAULT 0 ,
	"WithoutVAT"    		numeric(12,4) NULL ,
	"InvoiceDateDt" 		date NULL ,
	"UpolnomDateDt" 		date NULL ,
	"TrueInvoiceDate"       	date NULL ,
	"ExchangeRate"  		numeric(12,4) NULL ,
	"VidSmetka"     		integer NULL ,
	"DDSInvNum"     		varchar(30) NULL ,
	"DDSBank"       		varchar(50) NULL ,
	"OtgZaStopOper" 		varchar(50) NULL ,
	"ContractNo"    		integer NULL ,
	"MaskaFaktura"  		integer NULL ,
	"ShortNotes"    		varchar(1000) NULL , 
	 PRIMARY KEY ("InvoiceNo"),
	
)

GO

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

GO

CREATE TRIGGER "SpaTrigInvoicesUpd" after insert,update order 1
on "DBA".SpaInvoices
referencing new as InvoicesNew
for each row
begin
  update SpaInvoices set InvoiceDateDt=FloatToDate(InvoiceDate),UpolnomDateDt=FloatToDate(UpolnomDate)
    where InvoiceNo=InvoicesNew.InvoiceNo
end

GO

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

GO

CREATE TABLE "DBA"."SpaArticles"
(
	"InvoiceNo"     		integer NOT NULL ,
	"RegNo" 			integer NOT NULL ,
	"Price" 			float NULL ,
	"CreditNo"      		integer NULL ,
	"TotalSum"      		float NOT NULL ,
	"TotalSumInvCurrency"   	float NULL ,
	"Qty"   			integer NOT NULL ,
	"KodCurrency"   		integer NOT NULL ,
	"ArticleNo"     		integer NOT NULL ,
	"ArticleName"   		varchar(40) NULL ,
	"TipDDS"        		integer NULL ,
	"Discount"      		integer NULL ,
	"ServiceNo"     		integer NULL ,
	"RegDate"       		date NULL , 
	 PRIMARY KEY ("InvoiceNo", "ArticleNo"),

)

GO

if (select count(*) from SYSFOREIGNKEY  where role='SpaART_Invoices_N')=1 then

 ALTER TABLE "SpaArticles"
      DROP FOREIGN KEY "SpaART_Invoices_N"

end if

GO

ALTER TABLE "DBA"."SpaArticles"
	ADD FOREIGN KEY "SpaART_Invoices_N" ("InvoiceNo")
	REFERENCES "DBA"."SpaInvoices" ("InvoiceNo") on delete cascade on update cascade

GO

if (select count(*) from SYSINDEX where index_name='SpaIND_Articles_N')=1 then

      DROP INDEX "SpaIND_Articles_N"
end if

GO

CREATE UNIQUE INDEX "SpaIND_Articles_N" ON "DBA"."SpaArticles"
(
	"InvoiceNo" ASC,
	"ArticleNo" ASC
)

GO

if (select count(*) from SYSINDEX where index_name='SpaIND_Reg_N')=1 then

      DROP INDEX "SpaIND_Reg_N"

end if

GO

CREATE INDEX "SpaIND_Reg_N" ON "DBA"."SpaArticles"
(
	"RegNo" ASC,
	"CreditNo" ASC
)

GO

if (select count(*) from SYSINDEX where index_name='SpaIND_Credit_N')=1 then

      DROP INDEX "SpaIND_Credit_N"

end if


GO

CREATE INDEX "SpaIND_Credit_N" ON "DBA"."SpaArticles"
(
	"CreditNo" ASC
)

GO

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

GO

CREATE TRIGGER "SpaTrigArticUpd" after insert,update order 1
on "DBA".SpaArticles
referencing new as ATN
for each row
begin
  declare @D date;
  select InvoiceDateDt into @D
    from SpaInvoices where InvoiceNo=ATN.InvoiceNo;
  call SaveDateRate(ATN.KodCurrency,@D)
end

GO

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

GO

CREATE TABLE "DBA"."SpaResMoney"
(
	"ReserveNo"     		integer NOT NULL ,
	"DateMoney"     		float NOT NULL ,
	"KodAdmin"      		integer NOT NULL ,
	"Sum"   			numeric(12,2) NOT NULL ,
	"Currency"      		integer NOT NULL ,
	"Payment"       		integer NULL ,
	"Note"  			varchar(50) NULL ,
	"GuestNo"       		integer NOT NULL ,
	"DepositNo"     		integer NOT NULL ,
	"Status"        		integer NOT NULL ,
	"GroupNo"       		integer NULL ,
	"ContractNo"    		integer NULL ,
	"InvoiceNo"     		integer NULL ,
	"RoomNo"        		integer NULL ,
	"AccountNumber" 		varchar(16) NULL ,
	"DateMoneyDt"   		date NULL ,
	"PTVid" 			integer NULL ,
	"VaucherInvoiceNo"      	integer NULL ,
	 PRIMARY KEY ("DepositNo"),

)

GO

if (select count(*) from SYSINDEX where index_name='SpaInd_ResM_N')=1 then

      DROP INDEX "SpaInd_ResM_N"
end if

GO

CREATE UNIQUE INDEX "SpaInd_ResM_N" ON "DBA"."SpaResMoney"
(
	"DepositNo" ASC
)

GO

if (select count(*) from SYSINDEX where index_name='SpaIND_ResM_Guest')=1 then

      DROP INDEX "SpaIND_ResM_Guest"
end if

GO

CREATE INDEX "SpaIND_ResM_Guest" ON "DBA"."SpaResMoney"
(
	"GuestNo" ASC
)

GO

if (select count(*) from SYSINDEX where index_name='SpaInd_ResM_N')=1 then

      DROP INDEX "SpaInd_ResM_N"

end if

GO

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

GO

CREATE TRIGGER "SpaTrigInsDeposit" after update order 2
on "DBA".SpaResMoney
referencing old as RMOLD new as RMNEW
for each row
begin
  if RMOLD.GuestNo<>0 then call CalcBalance(RMOLD.GuestNo)
  end if
  ;
  if RMNEW.GuestNo<>0 then call CalcBalance(RMNEW.GuestNo)
  end if
  ;
  if RMOLD.ContractNo<>0 then
    call CalcBalanceContract(RMOLD.ContractNo)
  end if
  ;
  if RMNEW.ContractNo<>0 then
    call CalcBalanceContract(RMNEW.ContractNo)
  end if
end

GO

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

GO

CREATE TRIGGER "SpaTrigResMonDel" after delete order 4
on "DBA".SpaResMoney
referencing old as RMOLD
for each row
begin
  if RMOLD.GuestNo<>0 then
    call CalcBalance(RMOLD.GuestNo)
  end if
end

GO

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

GO

CREATE TRIGGER "SpaTrigResMoneyUpd" after insert,update order 1
on "DBA".SpaResMoney
referencing new as ResMoneyNew
for each row
begin
  declare @Date date;
  set @Date=FloatToDate(ResMoneyNew.DateMoney);
  update SpaResMoney set DateMoneyDt=@Date
    where DepositNo=ResMoneyNew.DepositNo;
  call SaveDateRate(ResMoneyNew.Currency,@Date)
end

GO

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

GO

CREATE TRIGGER "SpaTrigResMonIns" after insert order 3
on "DBA".SpaResMoney
referencing new as RMNEW
for each row
begin
  if RMNEW.GuestNo<>0 then call CalcBalance(RMNEW.GuestNo)
  end if ;
  if RMNEW.ContractNo<>0 then call CalcBalanceContract(RMNEW.ContractNo)
  end if
end