using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.EventLog
{
    public class EventLogQB
    {
        public const string GET_EVENTLOG = @" SELECT
                                            EL.EVENTTEXT                AS EventLogEventText,
                                            EL.LINK                     AS EventLogLink,
                                            EL.SOURCEID                 AS SourceId,
                                            S.BIZTRANSACTIONTYPECODE    AS SourceCode,
                                            S.BIZTRANSACTIONTYPENAME    AS SourceName,
                                            EL.USERID                   AS UserId,
                                            U.USERNAME                  AS UserName,
                                            U.USERCODE                  AS UserCode,
                                            EL.TAGS                     AS EventLogTags,
                                            EL.EVENTVALUE               AS EventValue,
                                            EL.DATA                     AS EventLogData,
                                            EL.GEO                      AS EventLogGeo,
                                            EL.EVENTTYPEID              AS EventTypeId,
                                            ET.EVENTTYPECODE            AS EventTypeCode,
                                            ET.EVENTTYPENAME            AS EventTypeName,
                                            ET.EVENTCATEGORY            AS EventTypeCategory,
                                            ET.ISBIZDOMAIN              AS EventTypeIsBusinessDomain,
                                            ET.ISAUDIT                  AS EventTypeIsAudit,
                                            ET.ISTELEMETRY              AS EventTypeIsTelemetry,
                                            ET.ISCOMPLIANCE             AS EventTypeIsCompliance,
                                            ET.RETENTIONDAYS            AS EventTypeRetentionDays,
                                            ET.STORAGETIER              AS EventTypeStorageTier,
                                            EL.EVENTCLASSID             AS EventClassId,
                                            EC.EVENTCLASSCODE                 AS EventClassCode,
                                            EC.EVENTCLASSNAME                 AS EventClassName,
                                            EL.LOGBOOKID                      AS EventLogLogBookId,
                                            EL.DATAID                         AS EventLogDataId,
                                            EL.TIMESTAMP                      AS TimeStamp,
                                            EL.EVENTCHANNELID                 AS EventChannelId,
                                            CH.EVENTCHANNELCODE               AS EventChannelCode,
                                            CH.EVENTCHANNELNAME               AS EventChannelName,
                                            EL.OUID                           AS OUId,
                                            OU.ORGANIZATIONUNITCODE           AS OUCode,
                                            OU.ORGANIZATIONUNITNAME           AS OUName,
                                            EL.MACHINEIP                      AS EventLogMachineIp,
                                            EL.MODEOFWORKING                  AS EventLogModeOfWorking,
                                            EL.LOGINEVENTLOGID                AS EventLogLoginEventLogId,
                                            EL.EVENTLOGID                     AS EventLogId

                                        FROM TEVENTLOG EL
                                        LEFT JOIN MBIZTRANSACTIONTYPE S ON EL.SOURCEID = S.BIZTRANSACTIONTYPEID
                                        LEFT JOIN MUSER U ON EL.USERID = U.USERID
                                        LEFT JOIN MORGANIZATIONUNIT OU ON EL.OUID = OU.OUID
                                        LEFT JOIN MEVENTTYPE ET ON EL.EVENTTYPEID = ET.EVENTTYPEID
                                        LEFT JOIN MEVENTCLASS EC ON EL.EVENTCLASSID = EC.EVENTCLASSID
                                        LEFT JOIN MEVENTCHANNEL CH ON EL.EVENTCHANNELID = CH.EVENTCHANNELID
                                            WHERE EL.EVENTLOGID = @eventlogid;";

        public const string SAVE_EVENTLOG = @"INSERT INTO TEVENTLOG (
                                            EVENTTEXT,
                                            LINK,
                                            SOURCEID,
                                            USERID,
                                            TAGS,
                                            EVENTVALUE,
                                            DATA,
                                            GEO,
                                            EVENTTYPEID,
                                            EVENTCLASSID,
                                            LOGBOOKID,
                                            DATAID,
                                            TIMESTAMP,
                                            EVENTCHANNELID,
                                            OUID,
                                            MACHINEIP,
                                            MODEOFWORKING,
                                            LOGINEVENTLOGID
                                        )
                                        VALUES (
                                            @EventLogEventText,
                                            @EventLogLink,
                                            @SourceId,
                                            @UserId,
                                            @EventLogTags,
                                            @EventLogEventValue,
                                            @EventLogData,
                                            @EventLogGeo,
                                            @EventTypeId,
                                            @EventClassId,
                                            @EventLogLogBookId,
                                            @EventLogDataId,
                                            @EventLogTimeStamp,
                                            @EventChannelId,
                                            @EventLogOUId,
                                            @EventLogMachineIp,
                                            @EventLogModeOfWorking,
                                            @EventLogLoginEventLogId
                                            );";


        public const string UPDATE_EVENTLOG = @"UPDATE dbo.MEVENTLOG
                                                SET
                                                    EVENTTEXT = ISNULL(@EventText, EVENTTEXT),
                                                    TAGS      = ISNULL(@Tags, TAGS),
                                                    EVENTVALUE= ISNULL(@EventValue, EVENTVALUE),
                                                    DATA      = ISNULL(@Data, DATA),
                                                    [TIMESTAMP] = ISNULL(@TimeStamp, [TIMESTAMP])
                                                WHERE EVENTLOGID = @EventLogId;";


        public const string DELETE_EVENTLOG = @"DELETE FROM TEVENTLOG
                                                WHERE EVENTLOGID = @eventlogid;";

        // ── Login event log — identity-returning variants ────────────────────
        // Used exclusively by SaveLoginEventLog (via ExecuteScalarAsync<int>)
        // to get the new EVENTLOGID back synchronously at login time.

        /// <summary>
        /// SQL Server: INSERT with OUTPUT Inserted.EVENTLOGID — returns the new EVENTLOGID scalar.
        /// Required index note: TEVENTLOG_EVENTTYPEID_MODEOFWORKING_USERID_INCLUDES covers login queries.
        /// </summary>
        public const string SAVE_LOGIN_EVENTLOG_SQL = @"
            INSERT INTO TEVENTLOG (
                EVENTTEXT, LINK, SOURCEID, USERID, TAGS, EVENTVALUE, DATA, GEO,
                EVENTTYPEID, EVENTCLASSID, LOGBOOKID, DATAID, TIMESTAMP, EVENTCHANNELID,
                OUID, MACHINEIP, MODEOFWORKING, LOGINEVENTLOGID
            )
            OUTPUT Inserted.EVENTLOGID
            VALUES (
                @EventLogEventText, @EventLogLink, @SourceId, @UserId, @EventLogTags,
                @EventLogEventValue, @EventLogData, @EventLogGeo, @EventTypeId, @EventClassId,
                @EventLogLogBookId, @EventLogDataId, @EventLogTimeStamp, @EventChannelId,
                @EventLogOUId, @EventLogMachineIp, @EventLogModeOfWorking, @EventLogLoginEventLogId
            );";

        /// <summary>
        /// PostgreSQL: INSERT … RETURNING EVENTLOGID — returns the new EVENTLOGID scalar.
        /// </summary>
        public const string SAVE_LOGIN_EVENTLOG_POSTGRESQL = @"
            INSERT INTO TEVENTLOG (
                EVENTTEXT, LINK, SOURCEID, USERID, TAGS, EVENTVALUE, DATA, GEO,
                EVENTTYPEID, EVENTCLASSID, LOGBOOKID, DATAID, TIMESTAMP, EVENTCHANNELID,
                OUID, MACHINEIP, MODEOFWORKING, LOGINEVENTLOGID
            )
            VALUES (
                @EventLogEventText, @EventLogLink, @SourceId, @UserId, @EventLogTags,
                @EventLogEventValue, @EventLogData, @EventLogGeo, @EventTypeId, @EventClassId,
                @EventLogLogBookId, @EventLogDataId, @EventLogTimeStamp, @EventChannelId,
                @EventLogOUId, @EventLogMachineIp, @EventLogModeOfWorking, @EventLogLoginEventLogId
            )
            RETURNING EVENTLOGID;";
    }
}