SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EFATURA_LOG]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EFATURA_LOG](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EFATNO] [nvarchar](50) NULL,
	[UUID] [nvarchar](50) NULL,
	[DURUM] [nvarchar](25) NULL,
	[TARIH] [datetime] NOT NULL,
	[ISLEM] [nvarchar](25) NULL,
	[HATAMESAJI] [nvarchar](255) NULL,
	[ACIKLAMA] [nvarchar](250) NULL,
	[XML] [ntext] NULL,
	[ONIZLE] [ntext] NULL,
 CONSTRAINT [PK_EFATURA_LOG] 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],
 CONSTRAINT [IX_EFATURA_LOG] UNIQUE NONCLUSTERED 
(
	[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] TEXTIMAGE_ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'TARIH' AND SYSOBJECTS.name = 'EFATURA_LOG')
BEGIN
ALTER TABLE [dbo].[EFATURA_LOG] ADD  CONSTRAINT [DF_EFATURA_LOG_TARIH]  DEFAULT (getdate()) FOR [TARIH]
END
GO

DECLARE @TABLE NVARCHAR(50)='EFATURA_LOG'
IF (SELECT COUNT(TABLOADI) FROM INDEXSIFIRLAMALOG WHERE TABLOADI=@TABLE)=0
BEGIN
	INSERT INTO INDEXSIFIRLAMALOG (TABLOADI) VALUES(@TABLE)
END
GO

DECLARE @TABLE NVARCHAR(50)='EFATURA_LOG'
DECLARE @SIFIRLANDI BIT=0
SELECT @SIFIRLANDI=ISNULL(SIFIRLANDI,0) FROM INDEXSIFIRLAMALOG WHERE TABLOADI=@TABLE

DECLARE @TableName NVARCHAR(100),@IndexName NVARCHAR(500),@ColumnName NVARCHAR(100),@IsInclude BIT
DECLARE @DROPSQL NVARCHAR(3500)='',@INDEXS NVARCHAR(MAX)='',@INDEXCOLS NVARCHAR(MAX)='',@INDEXINCCOLS NVARCHAR(MAX)='',@IndexNameTut NVARCHAR(500)='' 
DECLARE CUR CURSOR FOR
SELECT t.name as TableName, ind.name as IndexName, col.name as ColumnName, ic.is_included_column as IsInclude
FROM sys.indexes ind 
INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN sys.tables t ON ind.object_id = t.object_id 
WHERE ind.is_primary_key = 0 AND ind.is_unique = 0 AND ind.is_unique_constraint = 0 AND t.is_ms_shipped = 0 AND ind.data_space_id=1 
AND t.name = REPLACE(REPLACE(@TABLE,'[',''),']','') AND (@SIFIRLANDI=0 OR (@SIFIRLANDI=1 AND LEFT(ind.name,3)='IX_' ))
ORDER BY t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal

OPEN CUR
FETCH NEXT FROM CUR INTO @TableName,@IndexName,@ColumnName,@IsInclude
WHILE @@FETCH_STATUS = 0  
BEGIN
	if @IndexName<>@IndexNametut
	BEGIN
		IF @IndexNametut<>''
		BEGIN
			SET @INDEXS = @INDEXS + @IndexNametut+'('+@INDEXCOLS+')INCLUDE('+@INDEXINCCOLS+')   '
			SET @INDEXCOLS=''
			SET @INDEXINCCOLS=''
			SET @DROPSQL = @DROPSQL+'DROP INDEX ['+@IndexNametut+'] ON '+@TABLE+';'			
		END
		SET @IndexNametut = @IndexName
	END

	IF @IsInclude=1 
		IF @INDEXINCCOLS='' SET @INDEXINCCOLS=@INDEXINCCOLS+@ColumnName
		ELSE SET @INDEXINCCOLS=@INDEXINCCOLS+','+@ColumnName
	ELSE
		IF @INDEXCOLS='' SET @INDEXCOLS=@INDEXINCCOLS+@ColumnName
		ELSE SET @INDEXCOLS=@INDEXCOLS+','+@ColumnName
	FETCH NEXT FROM CUR INTO @TableName,@IndexName,@ColumnName,@IsInclude
END
CLOSE CUR;  
DEALLOCATE CUR;

IF @IndexNametut<>'' SET @INDEXS = @INDEXS + @IndexNametut+'('+@INDEXCOLS+')INCLUDE('+@INDEXINCCOLS+')   '
IF @IndexNametut<>'' SET @DROPSQL = @DROPSQL+'DROP INDEX ['+@IndexNametut+'] ON '+@TABLE+';'

IF @SIFIRLANDI=0
	UPDATE INDEXSIFIRLAMALOG SET SIFIRLANDI=1,ESKIINDEXLER=@INDEXS WHERE TABLOADI=@TABLE

IF @DROPSQL<>'' EXEC(@DROPSQL)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EFATURA_LOG]') AND name = N'IDX_EFATURA_LOG_UUID_ISLEM')
	CREATE NONCLUSTERED INDEX [IDX_EFATURA_LOG_UUID_ISLEM] ON [dbo].[EFATURA_LOG]
	(
		[UUID] ASC,
		[ISLEM] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
	GO