
-- crear la tabla rol 

if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='pro_rol')
EXEC _ini_creartabla 'pro_rol', 1
go

-- crear tabla intermedia rol y equ_contacto

if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='pro_rol_contacto')
EXEC _ini_creartabla 'pro_rol_contacto', 1
go

-- crear campos


if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='pro_rol_contacto' and column_name = 'id_rol' )
begin
alter table [pro_rol_contacto] add [id_rol] int not NULL DEFAULT 0
end 

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='pro_rol_contacto' and column_name = 'id_contacto' )
begin
alter table [pro_rol_contacto] add [id_contacto] int not NULL DEFAULT 0
end 




IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_rol_contacto'
      AND [columna]='id_rol' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='int' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_rol_contacto' ,'id_rol',1 ,5 ,1 ,'int' ,0 ,100)
END




IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_rol_contacto'
      AND [columna]='id_contacto' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='int' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_rol_contacto' ,'id_contacto',1 ,5 ,1 ,'int' ,0 ,100)
END

-- se realizan  las  relaciones


IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[pro_rol_contacto]') AND object_id = OBJECT_ID(N'[dbo].[FK_pro_rol_contacto_id_rol]')  )
BEGIN
ALTER TABLE [pro_rol_contacto] ADD CONSTRAINT
	[FK_pro_rol_contacto_id_rol] FOREIGN KEY
	([id_rol]) REFERENCES [pro_rol](id) 
	
ALTER TABLE [dbo].[pro_rol_contacto] CHECK CONSTRAINT [FK_pro_rol_contacto_id_rol]
END


--IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].pro_rol_contacto]') AND object_id = OBJECT_ID(N'[dbo].[FK_pro_rol_contacto_id_contacto]')  )
--BEGIN
--ALTER TABLE [pro_rol_contacto] ADD CONSTRAINT
--	[FK_pro_rol_contacto_id_contacto] FOREIGN KEY
--	([id_contacto]) REFERENCES [ter_contacto](id) 
	
--ALTER TABLE [dbo].[pro_rol_contacto] CHECK CONSTRAINT [FK_pro_rol_contacto_id_contacto]
--END

-- creacion de tablas auxiliares
if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='pro_rol_aux')
BEGIN
create table [pro_rol_aux] (
uid varchar (500)
,[rol] varchar (300)
,[rol_codigo] varchar (100)

)
END