SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if NOT exists(select * from information_schema.tables
				where table_type= 'BASE TABLE' AND table_name= 'AGENDAMENTOS')
BEGIN
	CREATE TABLE [dbo].[AGENDAMENTOS]( ----Criação da tabela------
		[ID] [int] IDENTITY(1,1) NOT NULL,
		[CLIENTE_ID] [int] NOT NULL,
		[AGENDAMENTO] [varchar](150) NOT NULL,
		[PERIODO] [varchar](30) NOT NULL,
		[ATIVO] [bit] NULL,
		[CADASTRO] [DATETIME] NULL,
		[ALTERACAO] [DATETIME] NULL,
		[USUARIO_ID] INT NOT NULL,
	 CONSTRAINT [PK_AGENDAMENTOS] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	CREATE UNIQUE NONCLUSTERED INDEX [UN_IDX_AGENDAMENTOS_POR_CLIENTE] ON [dbo].[AGENDAMENTOS] -----criação do index ---------
	(
    [CLIENTE_ID] ASC,
	[AGENDAMENTO] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END
-------------- criação das constraints ---------------------------
if NOT exists(select * from information_schema.tables
				where table_type= 'BASE TABLE' AND table_name= 'AGENDAMENTOS')
BEGIN
ALTER TABLE [dbo].[AGENDAMENTOS] ADD  CONSTRAINT [DF_AGENDAMENTOS_ATIVO]  DEFAULT ((1)) FOR [ATIVO]
END

if NOT exists(select * from information_schema.tables
				where table_type= 'BASE TABLE' AND table_name= 'AGENDAMENTOS')
BEGIN
ALTER TABLE [dbo].[AGENDAMENTOS] ADD  CONSTRAINT [DF_AGENDAMENTOS_CADASTRO]  DEFAULT (getdate()) FOR [CADASTRO]
END

if NOT exists(select * from information_schema.tables
				where table_type= 'BASE TABLE' AND table_name= 'AGENDAMENTOS')
BEGIN
ALTER TABLE [dbo].[AGENDAMENTOS]  WITH CHECK ADD  CONSTRAINT [FK_AGENDAMENTOS_CLIENTE_ID] FOREIGN KEY([CLIENTE_ID])
REFERENCES [dbo].[CLIENTES] ([ID])
end

if NOT exists(select * from information_schema.tables
				where table_type= 'BASE TABLE' AND table_name= 'AGENDAMENTOS')
begin
ALTER TABLE [dbo].[AGENDAMENTOS] CHECK CONSTRAINT [FK_AGENDAMENTOS_CLIENTE_ID]
end

if NOT exists(select * from information_schema.tables
				where table_type= 'BASE TABLE' AND table_name= 'AGENDAMENTOS')
begin
ALTER TABLE [dbo].[AGENDAMENTOS]  WITH CHECK ADD  CONSTRAINT [FK_AGENDAMENTOS_USUARIO_ID] FOREIGN KEY([USUARIO_ID])
REFERENCES [dbo].[CLIENTES] ([ID])
end

if NOT exists(select * from information_schema.tables
				where table_type= 'BASE TABLE' AND table_name= 'AGENDAMENTOS')
begin
ALTER TABLE [dbo].[AGENDAMENTOS] CHECK CONSTRAINT [FK_AGENDAMENTOS_USUARIO_ID]
END