USE [AIS_S3]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductVendingLimits]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductVendingLimits](
	[ProductVendingLimitNo] [int] IDENTITY(1,1) NOT NULL,
	[ProductNo] [int] NOT NULL,
	[MachineNo] [int] NOT NULL,
	[IsGroup] [bit] NOT NULL,
	[GroupNo] [int] NOT NULL DEFAULT ((0)),
	[UserNo] [int] NOT NULL DEFAULT ((0)),
	[PeriodType] [char](5) NOT NULL,
	[LimitPeriod] [int] NOT NULL,
	[LimitAmount] [int] NOT NULL,
	[Active] [bit] NOT NULL DEFAULT ((1)),
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductVendingLimits] PRIMARY KEY CLUSTERED 
(
	[ProductVendingLimitNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


if NOT Exists(select * from sys.columns where Name = N'Supervisor'   
                and Object_ID = Object_ID(N'dbo.Users')) 
 
begin 
 
ALTER TABLE [dbo].Users
ADD Supervisor bit DEFAULT 0 NOT NULL
end 

GO
ALTER TABLE [dbo].[ProductVendingLimits]  WITH CHECK ADD  CONSTRAINT [FK_ProductVendingLimits_Products] FOREIGN KEY([ProductNo])
REFERENCES [dbo].[Products] ([ProductNo])
GO
ALTER TABLE [dbo].[ProductVendingLimits] CHECK CONSTRAINT [FK_ProductVendingLimits_Products]