-- Script for creating website_news table for news

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[website_news]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[website_news] (
        [id] INT IDENTITY(1,1) NOT NULL,
        [title] NVARCHAR(100) NOT NULL,
        [subtitle] NVARCHAR(200) NULL,
        [content] NVARCHAR(MAX) NOT NULL,
        [image_url] NVARCHAR(255) NULL,
        [category] NVARCHAR(20) DEFAULT 'info' NOT NULL, -- Category: 'info', 'maj', 'event'
        [created_at] DATETIME2(7) DEFAULT GETDATE() NOT NULL,
        [updated_at] DATETIME2(7) DEFAULT GETDATE() NOT NULL,
        [is_published] BIT DEFAULT 1 NOT NULL,
        CONSTRAINT [PK_website_news] PRIMARY KEY CLUSTERED ([id] ASC)
    );
    
    -- Creating a CHECK constraint to limit category values
    ALTER TABLE [dbo].[website_news] ADD CONSTRAINT [CK_website_news_category] 
    CHECK ([category] IN ('info', 'maj', 'event'));
    
    PRINT 'Table website_news successfully created';
END
ELSE
BEGIN
    PRINT 'Table website_news already exists';
    
    -- If the table exists but not the category column, add it
    IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[website_news]') AND name = 'category')
    BEGIN
        ALTER TABLE [dbo].[website_news] ADD [category] NVARCHAR(20) DEFAULT 'info' NOT NULL;
        
        -- Adding the CHECK constraint
        ALTER TABLE [dbo].[website_news] ADD CONSTRAINT [CK_website_news_category] 
        CHECK ([category] IN ('info', 'maj', 'event'));
        
        PRINT 'Column category added to table website_news';
    END
    ELSE
    BEGIN
        PRINT 'Table website_news and column category already exist';
    END
END