namespace EnablementDAL.Query.Show;

// -----------------------------------------------------------------------------
// Admin CRUD for TSHOWSCHEDULE (author-time). The resolver's own read path
// (ShowResolverQB.GET_SHOW_SCHEDULES) is separate and untouched by this file.
// A Show may have zero or one schedule row — see DDL comment on TSHOWSCHEDULE.
// -----------------------------------------------------------------------------
public static class ShowScheduleQB
{
    public const string GET_SHOW_SCHEDULE = @"
        SELECT S.TSHOWSCHEDULEID AS ShowScheduleId,
               S.SHOWID          AS ShowId,
               S.SCHEDULETYPE    AS ScheduleType,
               S.SCHEDULEMODE    AS ScheduleMode,
               S.STARTDATE       AS StartDate,
               S.ENDDATE         AS EndDate,
               S.SCHEDULETIME    AS ScheduleTime,
               S.TIMEZONE        AS Timezone,
               S.RECURRENCEJSON  AS RecurrenceJson,
               S.ISACTIVE        AS IsActive,
               S.REMARKS         AS Remarks,
               S.STATUS          AS Status,
               S.SORTORDER       AS SortOrder,
               S.CREATEDBYID     AS CreatedById,
               S.CREATEDON       AS CreatedOn,
               S.MODIFIEDBYID    AS ModifiedById,
               S.MODIFIEDON      AS ModifiedOn,
               S.SOURCETYPE      AS SourceType,
               S.TENANTID        AS TenantId
        FROM   TSHOWSCHEDULE S
        WHERE  S.SHOWID   = @ShowId
        AND    S.TENANTID = @TenantId
        AND    S.STATUS   <> 0";

    public const string INSERT_SHOW_SCHEDULE = @"
        INSERT INTO TSHOWSCHEDULE
               (TSHOWSCHEDULEID, SHOWID, TENANTID,
                SCHEDULETYPE, SCHEDULEMODE, STARTDATE, ENDDATE, SCHEDULETIME, TIMEZONE, RECURRENCEJSON,
                ISACTIVE, REMARKS,
                VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE)
        VALUES (@ShowScheduleId, @ShowId, @TenantId,
                @ScheduleType, @ScheduleMode, @StartDate, @EndDate, @ScheduleTime, @Timezone, @RecurrenceJson,
                @IsActive, @Remarks,
                0, 1, @SortOrder,
                @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SourceType)";

    public const string UPDATE_SHOW_SCHEDULE = @"
        UPDATE TSHOWSCHEDULE
        SET    SCHEDULETYPE   = @ScheduleType,
               SCHEDULEMODE   = @ScheduleMode,
               STARTDATE      = @StartDate,
               ENDDATE        = @EndDate,
               SCHEDULETIME   = @ScheduleTime,
               TIMEZONE       = @Timezone,
               RECURRENCEJSON = @RecurrenceJson,
               ISACTIVE       = @IsActive,
               REMARKS        = @Remarks,
               SORTORDER      = @SortOrder,
               VERSION        = VERSION + 1,
               MODIFIEDBYID   = @ModifiedById,
               MODIFIEDON     = GETUTCDATE()
        WHERE  TSHOWSCHEDULEID = @ShowScheduleId
        AND    TENANTID        = @TenantId";

    // Soft delete — same convention as TSHOWRULE/TSHOWCONTENTLINK (STATUS = 0).
    public const string DELETE_SHOW_SCHEDULE = @"
        UPDATE TSHOWSCHEDULE
        SET    STATUS       = 0,
               ISACTIVE     = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  TSHOWSCHEDULEID = @ShowScheduleId
        AND    TENANTID        = @TenantId";

    public const string GET_SELECTLIST_SHOW_SCHEDULE = @"
WITH ShowSchedule AS
(
    SELECT
        ss.TSHOWSCHEDULEID  AS ShowScheduleId,
        ss.SHOWID           AS ShowId,
        ss.SCHEDULETYPE     AS ScheduleType,
        ss.SCHEDULEMODE     AS ScheduleMode,
        ss.SCHEDULETIME     AS ScheduleTime,
        ss.RECURRENCEJSON   AS RecurrenceJson,

        ROW_NUMBER() OVER (ORDER BY ss.TSHOWSCHEDULEID) AS RowNum

    FROM dbo.TSHOWSCHEDULE ss
)

SELECT
    ShowScheduleId,
    ShowId,
    ScheduleType,
    ScheduleMode,
    ScheduleTime,
    RecurrenceJson
FROM ShowSchedule
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR
    (RowNum BETWEEN @firstnumber AND @maxresult);";
    public const string GET_SHOW_SCHEDULE_LIST = @"
SELECT
    ss.TSHOWSCHEDULEID      AS ShowScheduleId,
    ss.SHOWID               AS ShowId,
    ss.SCHEDULETYPE         AS ScheduleType,
    ss.SCHEDULEMODE         AS ScheduleMode,
    ss.STARTDATE            AS StartDate,
    ss.ENDDATE              AS EndDate,
    ss.SCHEDULETIME         AS ScheduleTime,
    ss.TIMEZONE             AS Timezone,
    ss.RECURRENCEJSON       AS RecurrenceJson,
    ss.ISACTIVE             AS IsActive,
    ss.REMARKS              AS Remarks,
    ss.STATUS               AS Status,
    ss.SORTORDER            AS SortOrder,
    ss.CREATEDBYID          AS CreatedById,
    ss.CREATEDON            AS CreatedOn,
    ss.MODIFIEDBYID         AS ModifiedById,
    ss.MODIFIEDON           AS ModifiedOn,
    ss.SOURCETYPE           AS SourceType,
    ss.TENANTID             AS TenantId
FROM dbo.TSHOWSCHEDULE ss;";
}