-- ============================================
-- Migration 006: Kampanya Yonetim Sistemi
-- Mevcut MS_B2B_CAMPAIGNS tablosuna yeni ozellikler ekler
-- Ve iki yeni tablo olusturur: CAMPAIGN_PRODUCTS, CAMPAIGN_DEALERS
-- ============================================

-- CAMPAIGN_TYPE sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'CAMPAIGN_TYPE')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD CAMPAIGN_TYPE TINYINT DEFAULT 0;
    PRINT 'CAMPAIGN_TYPE sutunu eklendi.';
END
GO

-- DISCOUNT_AMOUNT sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'DISCOUNT_AMOUNT')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD DISCOUNT_AMOUNT DECIMAL(18,2) DEFAULT 0;
    PRINT 'DISCOUNT_AMOUNT sutunu eklendi.';
END
GO

-- MIN_QUANTITY sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'MIN_QUANTITY')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD MIN_QUANTITY INT DEFAULT 1;
    PRINT 'MIN_QUANTITY sutunu eklendi.';
END
GO

-- MIN_AMOUNT sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'MIN_AMOUNT')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD MIN_AMOUNT DECIMAL(18,2) DEFAULT 0;
    PRINT 'MIN_AMOUNT sutunu eklendi.';
END
GO

-- START_DATE sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'START_DATE')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD START_DATE DATETIME DEFAULT GETDATE();
    PRINT 'START_DATE sutunu eklendi.';
END
GO

-- START_DATE verilerini doldur
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'START_DATE')
BEGIN
    UPDATE MS_B2B_CAMPAIGNS SET START_DATE = ADDDATE WHERE START_DATE IS NULL AND ADDDATE IS NOT NULL;
    UPDATE MS_B2B_CAMPAIGNS SET START_DATE = GETDATE() WHERE START_DATE IS NULL;
    PRINT 'Mevcut kampanyalarin START_DATE degerleri guncellendi.';
END
GO

-- END_DATE sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'END_DATE')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD END_DATE DATETIME NULL;
    PRINT 'END_DATE sutunu eklendi.';
END
GO

-- PRIORITY sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'PRIORITY')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD PRIORITY TINYINT DEFAULT 0;
    PRINT 'PRIORITY sutunu eklendi.';
END
GO

-- APPLY_TO_ALL_DEALERS sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'APPLY_TO_ALL_DEALERS')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD APPLY_TO_ALL_DEALERS BIT DEFAULT 0;
    PRINT 'APPLY_TO_ALL_DEALERS sutunu eklendi.';
END
GO

-- APPLY_TO_ALL_PRODUCTS sutunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_CAMPAIGNS') AND name = 'APPLY_TO_ALL_PRODUCTS')
BEGIN
    ALTER TABLE MS_B2B_CAMPAIGNS ADD APPLY_TO_ALL_PRODUCTS BIT DEFAULT 0;
    PRINT 'APPLY_TO_ALL_PRODUCTS sutunu eklendi.';
END
GO

-- Indeks ekle
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_CAMPAIGNS_DATES' AND object_id = OBJECT_ID('MS_B2B_CAMPAIGNS'))
BEGIN
    CREATE INDEX IX_CAMPAIGNS_DATES ON MS_B2B_CAMPAIGNS(START_DATE, END_DATE);
    PRINT 'IX_CAMPAIGNS_DATES indeksi eklendi.';
END
GO

-- ============================================
-- Kampanya Urunleri Tablosu
-- ============================================
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MS_B2B_CAMPAIGN_PRODUCTS')
BEGIN
    CREATE TABLE MS_B2B_CAMPAIGN_PRODUCTS (
        LOGICALREF INT IDENTITY(1,1) PRIMARY KEY,
        CAMPAIGN_REF INT NOT NULL,
        STOCKREF INT NOT NULL,
        CREATED_DATE DATETIME DEFAULT GETDATE(),
        CONSTRAINT FK_CAMPAIGN_PRODUCTS_CAMPAIGN FOREIGN KEY (CAMPAIGN_REF)
            REFERENCES MS_B2B_CAMPAIGNS(LOGICALREF) ON DELETE CASCADE,
        CONSTRAINT UQ_CAMPAIGN_PRODUCT UNIQUE(CAMPAIGN_REF, STOCKREF)
    );

    CREATE INDEX IX_CAMPAIGN_PRODUCTS_CAMPAIGN ON MS_B2B_CAMPAIGN_PRODUCTS(CAMPAIGN_REF);
    CREATE INDEX IX_CAMPAIGN_PRODUCTS_STOCK ON MS_B2B_CAMPAIGN_PRODUCTS(STOCKREF);

    PRINT 'MS_B2B_CAMPAIGN_PRODUCTS tablosu olusturuldu.';
END
ELSE
    PRINT 'MS_B2B_CAMPAIGN_PRODUCTS tablosu zaten mevcut.';
GO

-- Mevcut kampanya urunlerini aktar
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'MS_B2B_CAMPAIGN_PRODUCTS')
    AND EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ITEMSPRICES') AND name = 'CAMPAIGNREF')
BEGIN
    INSERT INTO MS_B2B_CAMPAIGN_PRODUCTS (CAMPAIGN_REF, STOCKREF)
    SELECT DISTINCT IP.CAMPAIGNREF, IP.STOCKREF
    FROM MS_B2B_ITEMSPRICES IP
    WHERE IP.CAMPAIGNREF IS NOT NULL AND IP.CAMPAIGNREF > 0
    AND NOT EXISTS (
        SELECT 1 FROM MS_B2B_CAMPAIGN_PRODUCTS CP
        WHERE CP.CAMPAIGN_REF = IP.CAMPAIGNREF AND CP.STOCKREF = IP.STOCKREF
    );
    PRINT 'Mevcut kampanya urunleri aktarildi.';
END
GO

-- ============================================
-- Kampanya Bayileri Tablosu
-- ============================================
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MS_B2B_CAMPAIGN_DEALERS')
BEGIN
    CREATE TABLE MS_B2B_CAMPAIGN_DEALERS (
        LOGICALREF INT IDENTITY(1,1) PRIMARY KEY,
        CAMPAIGN_REF INT NOT NULL,
        MEMBERREF INT NOT NULL,
        CREATED_DATE DATETIME DEFAULT GETDATE(),
        CONSTRAINT FK_CAMPAIGN_DEALERS_CAMPAIGN FOREIGN KEY (CAMPAIGN_REF)
            REFERENCES MS_B2B_CAMPAIGNS(LOGICALREF) ON DELETE CASCADE,
        CONSTRAINT UQ_CAMPAIGN_DEALER UNIQUE(CAMPAIGN_REF, MEMBERREF)
    );

    CREATE INDEX IX_CAMPAIGN_DEALERS_CAMPAIGN ON MS_B2B_CAMPAIGN_DEALERS(CAMPAIGN_REF);
    CREATE INDEX IX_CAMPAIGN_DEALERS_MEMBER ON MS_B2B_CAMPAIGN_DEALERS(MEMBERREF);

    PRINT 'MS_B2B_CAMPAIGN_DEALERS tablosu olusturuldu.';
END
ELSE
    PRINT 'MS_B2B_CAMPAIGN_DEALERS tablosu zaten mevcut.';
GO

PRINT 'Kampanya sistemi migration basariyla tamamlandi!';
GO