-- ========================================
-- Migration: 003_announcement_system.sql
-- Feature: Duyuru Sistemi (Announcement System)
-- Description: Mevcut MS_B2B_ANNOUNCEMENTS tablosunu güncelle, yeni özellikler ekle
-- Date: 2026-02-16
-- Strategy: ALTER TABLE - Mevcut verileri koru
-- ========================================

-- ANNOUNCEMENT_TYPE sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'ANNOUNCEMENT_TYPE')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD ANNOUNCEMENT_TYPE TINYINT DEFAULT 0;
    PRINT 'ANNOUNCEMENT_TYPE column added.';
END
GO

-- PRIORITY sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'PRIORITY')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD PRIORITY TINYINT DEFAULT 0;
    PRINT 'PRIORITY column added.';
END
GO

-- START_DATE sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'START_DATE')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD START_DATE DATETIME NULL;
    PRINT 'START_DATE column added.';
END
GO

-- START_DATE verilerini doldur
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'START_DATE')
BEGIN
    UPDATE MS_B2B_ANNOUNCEMENTS SET START_DATE = ADDEDDATE WHERE START_DATE IS NULL AND ADDEDDATE IS NOT NULL;
    UPDATE MS_B2B_ANNOUNCEMENTS SET START_DATE = GETDATE() WHERE START_DATE IS NULL;
    PRINT 'START_DATE populated from ADDEDDATE.';
END
GO

-- END_DATE sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'END_DATE')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD END_DATE DATETIME NULL;
    PRINT 'END_DATE column added.';
END
GO

-- IS_ACTIVE sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'IS_ACTIVE')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD IS_ACTIVE BIT DEFAULT 1;
    PRINT 'IS_ACTIVE column added.';
END
GO

-- IS_ACTIVE verilerini doldur
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'IS_ACTIVE')
BEGIN
    UPDATE MS_B2B_ANNOUNCEMENTS SET IS_ACTIVE = CASE WHEN STATUS = 0 THEN 1 ELSE 0 END WHERE IS_ACTIVE IS NULL;
    PRINT 'IS_ACTIVE populated from STATUS.';
END
GO

-- CREATED_BY sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'CREATED_BY')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD CREATED_BY INT NULL;
    PRINT 'CREATED_BY column added.';
END
GO

-- CREATED_DATE sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'CREATED_DATE')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD CREATED_DATE DATETIME NULL;
    PRINT 'CREATED_DATE column added.';
END
GO

-- CREATED_DATE verilerini doldur
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'CREATED_DATE')
BEGIN
    UPDATE MS_B2B_ANNOUNCEMENTS SET CREATED_DATE = ADDEDDATE WHERE CREATED_DATE IS NULL AND ADDEDDATE IS NOT NULL;
    UPDATE MS_B2B_ANNOUNCEMENTS SET CREATED_DATE = GETDATE() WHERE CREATED_DATE IS NULL;
    PRINT 'CREATED_DATE populated from ADDEDDATE.';
END
GO

-- CONTENT sütunu ekle
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'CONTENT')
BEGIN
    ALTER TABLE MS_B2B_ANNOUNCEMENTS ADD CONTENT NVARCHAR(MAX) NULL;
    PRINT 'CONTENT column added.';
END
GO

-- CONTENT verilerini doldur
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'CONTENT')
BEGIN
    UPDATE MS_B2B_ANNOUNCEMENTS SET CONTENT = TEXTCONTENT WHERE CONTENT IS NULL;
    PRINT 'CONTENT populated from TEXTCONTENT.';
END
GO

-- Indeksler ekle
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'IX_ANNOUNCEMENTS_ACTIVE')
BEGIN
    CREATE INDEX IX_ANNOUNCEMENTS_ACTIVE ON MS_B2B_ANNOUNCEMENTS(IS_ACTIVE, START_DATE DESC);
    PRINT 'IX_ANNOUNCEMENTS_ACTIVE index created.';
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'IX_ANNOUNCEMENTS_DATE_RANGE')
BEGIN
    CREATE INDEX IX_ANNOUNCEMENTS_DATE_RANGE ON MS_B2B_ANNOUNCEMENTS(START_DATE, END_DATE);
    PRINT 'IX_ANNOUNCEMENTS_DATE_RANGE index created.';
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'IX_ANNOUNCEMENTS_TYPE')
BEGIN
    CREATE INDEX IX_ANNOUNCEMENTS_TYPE ON MS_B2B_ANNOUNCEMENTS(ANNOUNCEMENT_TYPE);
    PRINT 'IX_ANNOUNCEMENTS_TYPE index created.';
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENTS') AND name = 'IX_ANNOUNCEMENTS_PRIORITY')
BEGIN
    CREATE INDEX IX_ANNOUNCEMENTS_PRIORITY ON MS_B2B_ANNOUNCEMENTS(PRIORITY DESC);
    PRINT 'IX_ANNOUNCEMENTS_PRIORITY index created.';
END
GO

PRINT 'MS_B2B_ANNOUNCEMENTS table updated successfully.';
GO

-- Duyuru okunma kayitlari tablosu olustur
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MS_B2B_ANNOUNCEMENT_READS') AND type in (N'U'))
BEGIN
    CREATE TABLE MS_B2B_ANNOUNCEMENT_READS (
        LOGICALREF INT IDENTITY(1,1) PRIMARY KEY,
        ANNOUNCEMENT_REF INT NOT NULL,
        MEMBERREF INT NOT NULL,
        READ_DATE DATETIME DEFAULT GETDATE(),
        CONSTRAINT UQ_ANNOUNCEMENT_READS UNIQUE(ANNOUNCEMENT_REF, MEMBERREF)
    );

    CREATE INDEX IX_ANNOUNCEMENT_READS_MEMBER ON MS_B2B_ANNOUNCEMENT_READS(MEMBERREF);
    CREATE INDEX IX_ANNOUNCEMENT_READS_ANN ON MS_B2B_ANNOUNCEMENT_READS(ANNOUNCEMENT_REF);
    CREATE INDEX IX_ANNOUNCEMENT_READS_DATE ON MS_B2B_ANNOUNCEMENT_READS(READ_DATE DESC);

    PRINT 'MS_B2B_ANNOUNCEMENT_READS table created successfully.';
END
ELSE
BEGIN
    PRINT 'MS_B2B_ANNOUNCEMENT_READS table already exists.';
END
GO

PRINT 'Migration 003_announcement_system.sql completed successfully.';
GO