-- Pending Operations table for admin approval workflow
-- Version: 2
-- Description: Admin onay sistemi tablosu - JSON tabanlı operation storage

USE [BONUS_HELPER]
GO

-- PENDING_OPERATIONS tablosu
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PENDING_OPERATIONS]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[PENDING_OPERATIONS](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [FK_USER_ID] [int] NOT NULL,
        [USER_NAME] [nvarchar](50) NOT NULL,
        [OPERATION_TYPE] [varchar](50) NOT NULL,
        [OPERATION_DATA] [nvarchar](max) NOT NULL,
        [STATUS] [tinyint] NOT NULL DEFAULT 0,
        [REQUEST_DATE] [datetime] NOT NULL DEFAULT GETDATE(),
        [APPROVED_BY] [nvarchar](50) NULL,
        [APPROVED_DATE] [datetime] NULL,
        [REJECT_REASON] [nvarchar](500) NULL,
        [NOTES] [nvarchar](500) NULL,
        [ERROR_MESSAGE] [nvarchar](max) NULL,
     CONSTRAINT [PK_PENDING_OPERATIONS] PRIMARY KEY CLUSTERED ([ID] ASC)
    )
END
GO

-- Foreign Key
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_PENDING_OPERATIONS_KULLANICILAR')
BEGIN
    ALTER TABLE [dbo].[PENDING_OPERATIONS] WITH CHECK 
    ADD CONSTRAINT [FK_PENDING_OPERATIONS_KULLANICILAR] FOREIGN KEY([FK_USER_ID])
    REFERENCES [dbo].[KULLANICILAR] ([KullaniciID])
    ON UPDATE CASCADE
    ON DELETE CASCADE
END
GO

-- İndeksler
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_PENDING_OPERATIONS_STATUS')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_PENDING_OPERATIONS_STATUS] ON [dbo].[PENDING_OPERATIONS]([STATUS] ASC)
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_PENDING_OPERATIONS_FK_USER_ID')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_PENDING_OPERATIONS_FK_USER_ID] ON [dbo].[PENDING_OPERATIONS]([FK_USER_ID] ASC)
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_PENDING_OPERATIONS_REQUEST_DATE')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_PENDING_OPERATIONS_REQUEST_DATE] ON [dbo].[PENDING_OPERATIONS]([REQUEST_DATE] DESC)
END
GO

-- Check Constraints
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE name = 'CK_PENDING_OPERATIONS_STATUS')
BEGIN
    ALTER TABLE [dbo].[PENDING_OPERATIONS] WITH CHECK 
    ADD CONSTRAINT [CK_PENDING_OPERATIONS_STATUS] CHECK ([STATUS] IN (0, 1, 2, 3))
END
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE name = 'CK_PENDING_OPERATIONS_OPERATION_TYPE')
BEGIN
    ALTER TABLE [dbo].[PENDING_OPERATIONS] WITH CHECK 
    ADD CONSTRAINT [CK_PENDING_OPERATIONS_OPERATION_TYPE] CHECK ([OPERATION_TYPE] IN (
        'PUAN_EKLE',
        'PUAN_CIKAR', 
        'PUAN_ESITLE',
        'KART_AC',
        'EXCEL_IMPORT'
    ))
END
GO
