using FirebaseAdmin.Messaging;
using iText.StyledXmlParser.Jsoup.Select;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.Notification
{
    public class NotificationQB
    {
        public const string GET_NOTIFICATION_USER =@"SELECT*
                                                FROM
                                                (
                                                    SELECT
                                                        a.NOTIFICATIONID AS NotificationId,
                                                        b.NOTIFICATIONTOID AS NotificationToId,
                                                        ISNULL(a.EVENTLOGID, 0)       AS EventLogId,

                                                        a.FROMUSERID AS FromUserId,
                                                        d.USERCODE AS FromUserCode,
                                                        d.USERNAME AS FromUserName,

                                                        b.TOUSERID AS ToUserId,
                                                        c.USERCODE AS ToUserCode,
                                                        c.USERNAME AS ToUserName,

                                                        b.NOTIFICATIONSTATUS AS NotificationToNotificationStatus,

                                                        a.MESSAGE AS NotificationMessage,
                                                        a.MODIFIEDON AS NotificationModifiedon,

                                                        ISNULL(a.DATAID, -1)          AS DataId,

                                                        COUNT(1) OVER()               AS NumberOfRecords,

                                                        ROW_NUMBER() OVER(ORDER BY a.MODIFIEDON DESC) AS RowNum

                                                    FROM TNOTIFICATION a
                                                    INNER JOIN TNOTIFICATIONTO b ON a.NOTIFICATIONID = b.NOTIFICATIONID
                                                    INNER JOIN MUSER c ON b.TOUSERID = c.USERID
                                                    INNER JOIN MUSER d ON a.FROMUSERID = d.USERID

                                                    WHERE 1 = 1
                                                    /**where**/
                                                ) AS Result

                                                WHERE
                                                (
                                                    (@FirstNumber = -1 AND @MaxResult = -1)

                                                    OR
                                                    (RowNum > @FirstNumber AND RowNum <= (@FirstNumber + @MaxResult))
                                                )
                                                ORDER BY RowNum;";

        public const string UPDATE_NOTIFICATION_TO = @"
UPDATE TNOTIFICATIONTO
SET    NOTIFICATIONSTATUS = 1,
       MODIFIEDON         = GETDATE()
WHERE  NOTIFICATIONTOID   = @NotificationToId";

        public const string UPDATE_ALL_NOTIFICATION = @"
UPDATE TNOTIFICATIONTO
SET    NOTIFICATIONSTATUS = 1,
       MODIFIEDON         = GETDATE()
WHERE  TOUSERID           = @UserId
AND    NOTIFICATIONSTATUS = 0";

        public const string DELETE_ALL_NOTIFICATION = @"
DELETE FROM TNOTIFICATIONTO
WHERE  TOUSERID = @UserId";

        public const string DELETE_ONE_NOTIFICATION = @"
DELETE FROM TNOTIFICATIONTO
WHERE  NOTIFICATIONTOID = @NotificationToId";

        // ── Notification write ───────────────────────────────────────────────
        // Same shape as DataAlert's ObservabilityAlertQB.INSERT_NOTIFICATION —
        // one TNOTIFICATION row, then one TNOTIFICATIONTO row per recipient.
        // NOTE: TNOTIFICATION has no CREATEDON column on the live schema (verified via
        // INFORMATION_SCHEMA on GB5DEMO 2026-08-08) — only MODIFIEDON. The DataAlert module's
        // ObservabilityAlertQB.INSERT_NOTIFICATION references CREATEDON and is therefore broken
        // the same way this one would have been; not fixed here since it's a separate module.
        public const string INSERT_NOTIFICATION = @"
INSERT INTO TNOTIFICATION (FROMUSERID, MESSAGE, EVENTLOGID, DATAID, MODIFIEDON)
OUTPUT Inserted.NOTIFICATIONID
VALUES (@FromUserId, @Message, -1, @DataId, GETDATE())";

        public const string INSERT_NOTIFICATION_POSTGRESQL = @"
INSERT INTO TNOTIFICATION (FROMUSERID, MESSAGE, EVENTLOGID, DATAID, MODIFIEDON)
VALUES (@FromUserId, @Message, -1, @DataId, NOW())
RETURNING NOTIFICATIONID";

        public const string INSERT_NOTIFICATION_TO = @"
INSERT INTO TNOTIFICATIONTO (NOTIFICATIONID, TOUSERID, NOTIFICATIONSTATUS)
VALUES (@NotificationId, @ToUserId, 0)";
    }
}