USE [dbVARK]
GO
/****** Object:  UserDefinedFunction [dbo].[ZemiOstanatiSedista]    Script Date: 30.12.2015 03:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ZemiOstanatiSedista](@TerID INT, @KinoID INT)
RETURNS INT
AS

BEGIN

	DECLARE @Result INT
	DECLARE @p1 AS INT, @p2 AS INT

	SET @p1 = (SELECT Sedista FROM dbo.KinoSali	WHERE Id_Kino = @KinoID) --momentalno sediste
	SET @p2 = (SELECT SUM(Kolicina) FROM dbo.Rezervacii WHERE Rezervacii.ID_TerminFK = @TerID)
	IF (@p2 IS NULL)
		SET @p2 = 0;
	SET @Result = @p1 - @p2
	RETURN @Result
END
GO
/****** Object:  Table [dbo].[Administratori]    Script Date: 30.12.2015 03:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Administratori](
	[Verifikaciski_Kod] [nvarchar](20) NOT NULL,
	[fName] [varchar](50) NOT NULL,
	[lName] [varchar](max) NOT NULL,
	[email_KI] [varchar](50) NOT NULL,
	[telefon] [varchar](50) NULL,
	[Lozinka] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Administratori] PRIMARY KEY CLUSTERED 
(
	[email_KI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AdminKod]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AdminKod](
	[AKID] [int] IDENTITY(1,1) NOT NULL,
	[Kod] [nvarchar](20) NOT NULL,
	[Aktiviran] [bit] NOT NULL CONSTRAINT [DF_AdminKod_Aktiviran]  DEFAULT ((0)),
 CONSTRAINT [PK_AdminKod] PRIMARY KEY CLUSTERED 
(
	[AKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Filmovi]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Filmovi](
	[Id_Film] [int] IDENTITY(1,1) NOT NULL,
	[Naziv] [varchar](max) NOT NULL,
	[Zanr] [varchar](max) NOT NULL,
	[Reziser] [varchar](max) NOT NULL,
	[Akter1] [varchar](max) NOT NULL,
	[Akter2] [varchar](max) NULL,
	[Akter3] [varchar](max) NULL,
 CONSTRAINT [PK__Filmovi__3214EC07F309A015] PRIMARY KEY CLUSTERED 
(
	[Id_Film] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[KinoSali]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KinoSali](
	[Id_Kino] [int] IDENTITY(1,1) NOT NULL,
	[Naziv] [varchar](max) NOT NULL,
	[Adresa] [varchar](max) NOT NULL,
	[email] [varchar](max) NOT NULL,
	[telefon] [varchar](50) NOT NULL,
	[Sedista] [int] NOT NULL,
 CONSTRAINT [PK_KinoSali_1] PRIMARY KEY CLUSTERED 
(
	[Id_Kino] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Kupci]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Kupci](
	[fName] [varchar](50) NOT NULL,
	[lName] [varchar](60) NOT NULL,
	[email_KI] [varchar](50) NOT NULL,
	[telefon] [varchar](50) NULL,
	[Lozinka] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Kupci] PRIMARY KEY CLUSTERED 
(
	[email_KI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Rezervacii]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Rezervacii](
	[ID_Rezervacija] [bigint] IDENTITY(1,1) NOT NULL,
	[kupecEmailFK] [varchar](50) NOT NULL,
	[ID_TerminFK] [int] NOT NULL,
	[Kolicina] [int] NOT NULL CONSTRAINT [DF_dbo.Rezervacii_Kolicina]  DEFAULT ((0)),
	[Platena] [bit] NOT NULL CONSTRAINT [DF_dbo.Rezervacii_Platena]  DEFAULT ((0)),
	[PlatenaDatum] [datetime] NULL,
	[NositelNaKarticka] [nvarchar](100) NULL,
	[TipNaKarticka] [nvarchar](20) NULL,
	[BrojNaKarticka] [nvarchar](16) NULL,
 CONSTRAINT [PK_dbo.Rezervacii] PRIMARY KEY CLUSTERED 
(
	[ID_Rezervacija] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Termini]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Termini](
	[Id_Termin] [int] IDENTITY(1,1) NOT NULL,
	[Id_Film] [int] NOT NULL,
	[Id_Kino] [int] NOT NULL,
	[Datum] [date] NOT NULL,
	[Cas] [time](0) NOT NULL,
	[Cena] [money] NOT NULL CONSTRAINT [DF_Termini_Cena]  DEFAULT ((200)),
	[OstanatiMesta]  AS ([dbo].[ZemiOstanatiSedista]([Id_Termin],[Id_Kino])),
 CONSTRAINT [PK__Termini__790FDCFD6641073C] PRIMARY KEY CLUSTERED 
(
	[Id_Termin] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Rezervacii]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Rezervacii_Kupci] FOREIGN KEY([kupecEmailFK])
REFERENCES [dbo].[Kupci] ([email_KI])
GO
ALTER TABLE [dbo].[Rezervacii] CHECK CONSTRAINT [FK_dbo.Rezervacii_Kupci]
GO
ALTER TABLE [dbo].[Rezervacii]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Rezervacii_Termini] FOREIGN KEY([ID_TerminFK])
REFERENCES [dbo].[Termini] ([Id_Termin])
GO
ALTER TABLE [dbo].[Rezervacii] CHECK CONSTRAINT [FK_dbo.Rezervacii_Termini]
GO
ALTER TABLE [dbo].[Termini]  WITH CHECK ADD  CONSTRAINT [FK_Termini_To_Filmovi] FOREIGN KEY([Id_Film])
REFERENCES [dbo].[Filmovi] ([Id_Film])
GO
ALTER TABLE [dbo].[Termini] CHECK CONSTRAINT [FK_Termini_To_Filmovi]
GO
ALTER TABLE [dbo].[Termini]  WITH CHECK ADD  CONSTRAINT [FK_Termini_To_KinoSali] FOREIGN KEY([Id_Kino])
REFERENCES [dbo].[KinoSali] ([Id_Kino])
GO
ALTER TABLE [dbo].[Termini] CHECK CONSTRAINT [FK_Termini_To_KinoSali]
GO
/****** Object:  StoredProcedure [dbo].[GetMyCart]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetMyCart] 
    @email VARCHAR(50)  = ''
AS
SELECT TOP 50 ID_Rezervacija AS RID
	  ,t3.Naziv AS FNaziv
	  ,t4.Naziv AS KNaziv
	  ,t2.Cena AS CenaBilet
	  ,Kolicina
	  , (Cena * Kolicina) AS CenaVkupno
  FROM dbo.Rezervacii AS t1
  LEFT JOIN dbo.Termini AS t2 ON t1.ID_TerminFK = t2.Id_Termin
  LEFT JOIN dbo.Filmovi AS t3 ON t2.Id_Film = t3.Id_Film
  LEFT JOIN dbo.KinoSali AS t4 ON t2.Id_Kino = t4.Id_Kino
  WHERE kupecEmailFK = @email AND Platena = 0;
RETURN 0 
GO
/****** Object:  StoredProcedure [dbo].[GetTermin]    Script Date: 30.12.2015 03:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetTermin]

AS
SELECT t1.Id_Termin, t2.Naziv AS FNaziv, t2.Zanr, t3.Naziv AS KNaziv, t1.Datum, t1.Cas, t1.Cena FROM dbo.Termini AS t1
LEFT JOIN dbo.Filmovi AS t2 ON t1.Id_Film = t2.Id_Film
LEFT JOIN dbo.KinoSali AS t3 ON t1.Id_Kino = t1.Id_Kino
WHERE t1.Datum >= {fn NOW()}; 
RETURN 0 
GO
