IF OBJECT_ID('dbo.T_ORAREND') IS NULL BEGIN
  CREATE TABLE T_ORAREND (
     C_ORARENDIORAID int NOT NULL
    ,C_DATUM datetime NOT NULL
    ,C_TANEVID int NOT NULL
    ,C_INTEZMENYID int NOT NULL
    ,CONSTRAINT PK_Orarend PRIMARY KEY (C_ORARENDIORAID, C_DATUM)
  );

  CREATE INDEX NCI_Orarend_TanevId ON T_ORAREND ( C_TANEVID)
  CREATE INDEX NCI_Orarend_IntezmenyId ON T_ORAREND ( C_INTEZMENYID)
  CREATE INDEX NCI_Orarend_Datum_TanevId_IntezmenyId ON T_ORAREND (C_DATUM, C_TANEVID, C_INTEZMENYID)

  ALTER TABLE [DBO].T_ORAREND 
    ADD CONSTRAINT FK_Orarend_OrarendiOraId_REF_OrarendiOraId 
    FOREIGN KEY (C_ORARENDIORAID) 
    REFERENCES [DBO].T_ORARENDIORA (ID)
  
  ALTER TABLE [DBO].T_ORAREND 
    ADD CONSTRAINT FK_Orarend_TanevId_REF_Tanev 
    FOREIGN KEY (C_TANEVID) 
    REFERENCES [DBO].T_TANEV (ID)

  ALTER TABLE [DBO].T_ORAREND 
    ADD CONSTRAINT FK_Orarend_IntezmenyId_REF_Intezmeny 
    FOREIGN KEY (C_INTEZMENYID) 
    REFERENCES [DBO].T_INTEZMENY (ID)  

END
GO


EXEC dev.uspCreateSchemaViews 'T_ORAREND'
GO

DROP INDEX IF EXISTS NCI_TanevRendje_Datum_IntezmenyId_TanevId ON T_TANEVRENDJE
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCI_TanevRendje_Datum_IntezmenyId_TanevId') BEGIN
  CREATE NONCLUSTERED INDEX NCI_TanevRendje_Datum_IntezmenyId_TanevId
    ON T_TANEVRENDJE (
       C_DATUM
      ,C_TANEVID
      ,C_INTEZMENYID
    )
    INCLUDE (C_OSSZESCSOPORTRAVONATKOZIK, C_CSENGETESIRENDID, C_EGYEDINAP)
  WHERE TOROLT = 'F'
  WITH (FILLFACTOR = 80)
END
GO


IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCI_OrarendiOra_TanevId_IntezmenyId_HetNapja_OraervenyessegKezdete_OrarevenyessegVege') BEGIN
  CREATE INDEX NCI_OrarendiOra_TanevId_IntezmenyId_HetNapja_OraervenyessegKezdete_OrarevenyessegVege 
    ON T_ORARENDIORA (C_TANEVID, C_INTEZMENYID, C_HETNAPJA, C_ORAERVENYESSEGKEZDETE, C_ORAERVENYESSEGVEGE) 
  INCLUDE (C_EGYEDINAP, C_OSZTALYCSOPORTID, C_HETIREND, TOROLT)
  WHERE TOROLT = 'F' 
  WITH (FILLFACTOR = 80)
END
GO