-- Create SUPPLIER_PLAN table
CREATE TABLE [dbo].[SUPPLIER_PLAN] (
    SP_ID INT IDENTITY(1,1) PRIMARY KEY,
    PLAN_NAME VARCHAR(100) NOT NULL,
    PLAN_DESCRIPTION VARCHAR(500),
    PLAN_ICON VARCHAR(100),
    SUBSCRIPTION_DAYS INT NOT NULL,
    AUD_ID BIGINT NOT NULL,

    CONSTRAINT FK_SUPPLIER_PLAN_AUDIT FOREIGN KEY (AUD_ID)
        REFERENCES AUDIT_LOG (AUD_ID)
);

-- Create SUPPLIER_PLAN_LINK table
CREATE TABLE [dbo].[SUPPLIER_PLAN_LINK] (
    SPL_ID INT IDENTITY(1,1) PRIMARY KEY,
    SP_ID INT NOT NULL,
    UD_ID INT NOT NULL,
    PLAN_START_DATE DATETIME NOT NULL,
    PLAN_END_DATE DATETIME NOT NULL,

    -- Computed column for active status
    IS_ACTIVE AS (
        CASE 
            WHEN GETDATE() BETWEEN PLAN_START_DATE AND PLAN_END_DATE THEN 1
            ELSE 0
        END
    ),

    AUD_ID BIGINT NOT NULL,

    -- Foreign Keys
    CONSTRAINT FK_SUPPLIER_PLAN_LINK_PLAN FOREIGN KEY (SP_ID)
        REFERENCES SUPPLIER_PLAN (SP_ID),

    CONSTRAINT FK_SUPPLIER_PLAN_LINK_USER FOREIGN KEY (UD_ID)
        REFERENCES USER_DETAILS (UD_ID),

    CONSTRAINT FK_SUPPLIER_PLAN_LINK_AUDIT FOREIGN KEY (AUD_ID)
        REFERENCES AUDIT_LOG (AUD_ID)
);
