namespace EnablementDAL.Query.Show;

// -----------------------------------------------------------------------------
// Admin CRUD for MSHOWCHANNEL (master) and TSHOWCHANNEL (Show-to-Channel
// many-to-many assignment junction). TSHOW.CHANNELID (existing single-value
// column) remains the "primary/default channel" for backward compatibility;
// the full assignment set lives in TSHOWCHANNEL.
// -----------------------------------------------------------------------------
public static class ShowChannelQB
{
    public const string GET_SHOW_CHANNEL = @"
        SELECT C.CHANNELID           AS ChannelId,
               C.CHANNELCODE         AS ChannelCode,
               C.CHANNELNAME         AS ChannelName,
               C.DESCRIPTION         AS Description,
               C.SUBSCRIPTIONTYPE    AS SubscriptionType,
               C.AUTOSUBSCRIBERULE   AS AutoSubscribeRule,
               C.ISPORTALINTERNAL    AS IsPortalInternal,
               C.ISPORTALESS         AS IsPortalEss,
               C.ISPORTALVENDOR      AS IsPortalVendor,
               C.ISPORTALCUSTOMER    AS IsPortalCustomer,
               C.ISACTIVE            AS IsActive,
               C.VERSION             AS Version,
               C.STATUS              AS Status,
               C.SORTORDER           AS SortOrder,
               C.CREATEDBYID         AS CreatedById,
               C.CREATEDON           AS CreatedOn,
               C.MODIFIEDBYID        AS ModifiedById,
               C.MODIFIEDON          AS ModifiedOn,
               C.SOURCETYPE          AS SourceType,
               C.TENANTID            AS TenantId
        FROM   MSHOWCHANNEL C
        WHERE  C.CHANNELID = @ChannelId
        AND    C.TENANTID  = @TenantId";

    public const string GET_SHOW_CHANNEL_LIST = @"
        SELECT C.CHANNELID           AS ChannelId,
               C.CHANNELCODE         AS ChannelCode,
               C.CHANNELNAME         AS ChannelName,
               C.DESCRIPTION         AS Description,
               C.SUBSCRIPTIONTYPE    AS SubscriptionType,
               C.AUTOSUBSCRIBERULE   AS AutoSubscribeRule,
               C.ISPORTALINTERNAL    AS IsPortalInternal,
               C.ISPORTALESS         AS IsPortalEss,
               C.ISPORTALVENDOR      AS IsPortalVendor,
               C.ISPORTALCUSTOMER    AS IsPortalCustomer,
               C.ISACTIVE            AS IsActive,
               C.VERSION             AS Version,
               C.STATUS              AS Status,
               C.SORTORDER           AS SortOrder,
               C.CREATEDBYID         AS CreatedById,
               C.CREATEDON           AS CreatedOn,
               C.MODIFIEDBYID        AS ModifiedById,
               C.MODIFIEDON          AS ModifiedOn,
               C.SOURCETYPE          AS SourceType,
               C.TENANTID            AS TenantId
        FROM   MSHOWCHANNEL C
        WHERE  C.TENANTID = @TenantId
        AND    C.STATUS   <> 0
        ORDER  BY C.SORTORDER
        OFFSET (@PageNo - 1) * @PageSize ROWS
        FETCH  NEXT @PageSize ROWS ONLY";

    public const string GET_SHOW_CHANNEL_LIST_COUNT = @"
        SELECT COUNT(1)
        FROM   MSHOWCHANNEL C
        WHERE  C.TENANTID = @TenantId
        AND    C.STATUS   <> 0";

    public const string INSERT_SHOW_CHANNEL = @"
        INSERT INTO MSHOWCHANNEL
               (CHANNELID, CHANNELCODE, CHANNELNAME, DESCRIPTION,
                SUBSCRIPTIONTYPE, AUTOSUBSCRIBERULE,
                ISPORTALINTERNAL, ISPORTALESS, ISPORTALVENDOR, ISPORTALCUSTOMER,
                ISACTIVE,
                VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES (@ChannelId, @ChannelCode, @ChannelName, @Description,
                @SubscriptionType, @AutoSubscribeRule,
                @IsPortalInternal, @IsPortalEss, @IsPortalVendor, @IsPortalCustomer,
                @IsActive,
                0, 1, @SortOrder,
                @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SourceType, @TenantId)";

    public const string UPDATE_SHOW_CHANNEL = @"
        UPDATE MSHOWCHANNEL
        SET    CHANNELCODE       = @ChannelCode,
               CHANNELNAME       = @ChannelName,
               DESCRIPTION       = @Description,
               SUBSCRIPTIONTYPE  = @SubscriptionType,
               AUTOSUBSCRIBERULE = @AutoSubscribeRule,
               ISPORTALINTERNAL  = @IsPortalInternal,
               ISPORTALESS       = @IsPortalEss,
               ISPORTALVENDOR    = @IsPortalVendor,
               ISPORTALCUSTOMER  = @IsPortalCustomer,
               ISACTIVE          = @IsActive,
               SORTORDER         = @SortOrder,
               VERSION           = VERSION + 1,
               MODIFIEDBYID      = @ModifiedById,
               MODIFIEDON        = GETUTCDATE()
        WHERE  CHANNELID = @ChannelId
        AND    TENANTID  = @TenantId";

    // Soft delete — same convention as TSHOWRULE (STATUS = 0, ISACTIVE = 0).
    public const string DELETE_SHOW_CHANNEL = @"
        UPDATE MSHOWCHANNEL
        SET    STATUS       = 0,
               ISACTIVE     = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  CHANNELID = @ChannelId
        AND    TENANTID  = @TenantId";

    public const string GET_SELECTLIST_SHOW_CHANNEL = @"
        SELECT C.CHANNELID   AS ChannelId,
               C.CHANNELCODE AS ChannelCode,
               C.CHANNELNAME AS ChannelName
        FROM   MSHOWCHANNEL C
        WHERE  C.ISACTIVE = 1
        AND    C.STATUS   <> 0
        AND    C.TENANTID = @TenantId
        ORDER  BY C.SORTORDER";

    // -------------------------------------------------------------------------
    // TSHOWCHANNEL — Show-to-Channel many-to-many assignment junction.
    // -------------------------------------------------------------------------

    public const string ASSIGN_CHANNEL_TO_SHOW = @"
        INSERT INTO TSHOWCHANNEL
               (SHOWCHANNELID, SHOWID, CHANNELID,
                VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES (@ShowChannelId, @ShowId, @ChannelId,
                0, 1, 9999,
                @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SourceType, @TenantId)";

    // Pure junction row — hard delete is fine, no soft-delete needed here.
    public const string UNASSIGN_CHANNEL_FROM_SHOW = @"
        DELETE FROM TSHOWCHANNEL
        WHERE  SHOWID    = @ShowId
        AND    CHANNELID = @ChannelId
        AND    TENANTID  = @TenantId";

    public const string GET_CHANNELS_FOR_SHOW = @"
        SELECT SC.SHOWCHANNELID     AS ShowChannelId,
               SC.SHOWID            AS ShowId,
               SC.CHANNELID         AS ChannelId,
               C.CHANNELCODE        AS ChannelCode,
               C.CHANNELNAME        AS ChannelName,
               SC.VERSION           AS Version,
               SC.STATUS            AS Status,
               SC.SORTORDER         AS SortOrder,
               SC.CREATEDBYID       AS CreatedById,
               SC.CREATEDON         AS CreatedOn,
               SC.MODIFIEDBYID      AS ModifiedById,
               SC.MODIFIEDON        AS ModifiedOn,
               SC.SOURCETYPE        AS SourceType,
               SC.TENANTID          AS TenantId
        FROM   TSHOWCHANNEL SC
        JOIN   MSHOWCHANNEL C ON C.CHANNELID = SC.CHANNELID
        WHERE  SC.SHOWID   = @ShowId
        AND    SC.TENANTID = @TenantId
        AND    SC.STATUS   <> 0
        ORDER  BY SC.SORTORDER";
}