namespace FrameworkDAL.Query.AuditQuery
{
    /// <summary>
    /// SQL constants for AuditQuery endpoints.
    ///
    /// Required indexes (see migration AuditChangeTracking_Tables.sql):
    ///   TEVENTLOG: IX_TEVENTLOG on (TENANTID, TIMESTAMP DESC)
    ///   TEVENTLOG: IX_TEVENTLOG_USERID on (TENANTID, USERID, TIMESTAMP DESC)
    ///   TEVENTLOG: IX_TEVENTLOG_SOURCEID_DATAID on (TENANTID, SOURCEID, DATAID)
    ///   TEVENTLOGCHANGE: IX_TEVENTLOGCHANGE_EVENTLOGID
    ///   TEVENTLOGCHANGE: IX_TEVENTLOGCHANGE_TENANTID_FIELDNAME
    ///
    /// Archive queries use identical SQL with table name TEVENTLOG_ARCHIVE.
    /// </summary>
    public static class AuditQueryQB
    {
        private const string EventLogSelect = @"
SELECT
    EL.EVENTLOGID       AS EventLogId,
    EL.EVENTTYPEID      AS EventTypeId,
    ET.EVENTTYPECODE    AS EventTypeCode,
    ET.EVENTTYPENAME    AS EventTypeName,
    EL.USERID           AS UserId,
    U.USERNAME          AS UserName,
    U.USERCODE          AS UserCode,
    EL.EVENTTEXT        AS EventText,
    EL.DATA             AS Data,
    EL.SOURCEID         AS SourceId,
    S.BIZTRANSACTIONTYPENAME AS SourceName,
    EL.DATAID           AS DataId,
    EL.OUID             AS OuId,
    OU.ORGANIZATIONUNITNAME AS OuName,
    EL.MACHINEIP        AS MachineIp,
    EL.TIMESTAMP        AS Timestamp,
    EL.TAGS             AS Tags
FROM {TABLE} EL
LEFT JOIN MEVENTTYPE        ET  ON EL.EVENTTYPEID   = ET.EVENTTYPEID
LEFT JOIN MUSER             U   ON EL.USERID         = U.USERID
LEFT JOIN MBIZTRANSACTIONTYPE S ON EL.SOURCEID       = S.BIZTRANSACTIONTYPEID
LEFT JOIN MORGANIZATIONUNIT OU  ON EL.OUID           = OU.OUID";

        // ── GetByEntity ───────────────────────────────────────────────────────

        /// <summary>All audit events for a given EntityType (SOURCEID) and EntityId (DATAID), paginated.</summary>
        public const string GET_BY_ENTITY = @"
SELECT COUNT(*) OVER() AS TotalCount,
" + EventLogSelectFlat + @"
WHERE EL.TENANTID  = @TenantId
  AND EL.SOURCEID  = @EntityType
  AND EL.DATAID    = @EntityId
ORDER BY EL.TIMESTAMP DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        /// <summary>Archive equivalent — same query on TEVENTLOG_ARCHIVE.</summary>
        public const string GET_BY_ENTITY_ARCHIVE = @"
SELECT COUNT(*) OVER() AS TotalCount,
" + EventLogSelectFlatArchive + @"
WHERE EL.TENANTID  = @TenantId
  AND EL.SOURCEID  = @EntityType
  AND EL.DATAID    = @EntityId
ORDER BY EL.TIMESTAMP DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // ── GetByUser ─────────────────────────────────────────────────────────

        /// <summary>All audit events for a given UserId, with optional date range, paginated.</summary>
        public const string GET_BY_USER = @"
SELECT COUNT(*) OVER() AS TotalCount,
" + EventLogSelectFlat + @"
WHERE EL.TENANTID  = @TenantId
  AND EL.USERID    = @UserId
  AND (@DateFrom   IS NULL OR EL.TIMESTAMP >= @DateFrom)
  AND (@DateTo     IS NULL OR EL.TIMESTAMP <= @DateTo)
ORDER BY EL.TIMESTAMP DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        /// <summary>Archive equivalent.</summary>
        public const string GET_BY_USER_ARCHIVE = @"
SELECT COUNT(*) OVER() AS TotalCount,
" + EventLogSelectFlatArchive + @"
WHERE EL.TENANTID  = @TenantId
  AND EL.USERID    = @UserId
  AND (@DateFrom   IS NULL OR EL.TIMESTAMP >= @DateFrom)
  AND (@DateTo     IS NULL OR EL.TIMESTAMP <= @DateTo)
ORDER BY EL.TIMESTAMP DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // ── SearchAudit ───────────────────────────────────────────────────────

        /// <summary>
        /// Full-featured audit search.
        /// When FieldName filter is set, restricts to events that have at least one
        /// TEVENTLOGCHANGE row for that field (EXISTS subquery — index-friendly).
        /// </summary>
        public const string SEARCH = @"
SELECT COUNT(*) OVER() AS TotalCount,
" + EventLogSelectFlat + @"
WHERE EL.TENANTID      = @TenantId
  AND (@UserId         IS NULL OR EL.USERID      = @UserId)
  AND (@EntityType     IS NULL OR EL.SOURCEID    = @EntityType)
  AND (@EntityId       IS NULL OR EL.DATAID      = @EntityId)
  AND (@EventTypeId    IS NULL OR EL.EVENTTYPEID = @EventTypeId)
  AND (@DateFrom       IS NULL OR EL.TIMESTAMP  >= @DateFrom)
  AND (@DateTo         IS NULL OR EL.TIMESTAMP  <= @DateTo)
  AND (@FieldName      IS NULL OR EXISTS (
        SELECT 1 FROM TEVENTLOGCHANGE C
        WHERE C.EVENTLOGID = EL.EVENTLOGID
          AND C.TENANTID   = EL.TENANTID
          AND C.FIELDNAME  = @FieldName))
ORDER BY EL.TIMESTAMP DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        /// <summary>Archive equivalent.</summary>
        public const string SEARCH_ARCHIVE = @"
SELECT COUNT(*) OVER() AS TotalCount,
" + EventLogSelectFlatArchive + @"
WHERE EL.TENANTID      = @TenantId
  AND (@UserId         IS NULL OR EL.USERID      = @UserId)
  AND (@EntityType     IS NULL OR EL.SOURCEID    = @EntityType)
  AND (@EntityId       IS NULL OR EL.DATAID      = @EntityId)
  AND (@EventTypeId    IS NULL OR EL.EVENTTYPEID = @EventTypeId)
  AND (@DateFrom       IS NULL OR EL.TIMESTAMP  >= @DateFrom)
  AND (@DateTo         IS NULL OR EL.TIMESTAMP  <= @DateTo)
  AND (@FieldName      IS NULL OR EXISTS (
        SELECT 1 FROM TEVENTLOGCHANGE C
        WHERE C.EVENTLOGID = EL.EVENTLOGID
          AND C.TENANTID   = EL.TENANTID
          AND C.FIELDNAME  = @FieldName))
ORDER BY EL.TIMESTAMP DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // ── Inline helpers (shared projection strings) ────────────────────────

        // NOTE: The string interpolation pattern used elsewhere in the project would require
        // a runtime build. For SQL constants we flatten the shared SELECT directly.

        private const string EventLogSelectFlat = @"
    EL.EVENTLOGID       AS EventLogId,
    EL.EVENTTYPEID      AS EventTypeId,
    ET.EVENTTYPECODE    AS EventTypeCode,
    ET.EVENTTYPENAME    AS EventTypeName,
    EL.USERID           AS UserId,
    U.USERNAME          AS UserName,
    U.USERCODE          AS UserCode,
    EL.EVENTTEXT        AS EventText,
    EL.DATA             AS Data,
    EL.SOURCEID         AS SourceId,
    S.BIZTRANSACTIONTYPENAME AS SourceName,
    EL.DATAID           AS DataId,
    EL.OUID             AS OuId,
    OU.ORGANIZATIONUNITNAME  AS OuName,
    EL.MACHINEIP        AS MachineIp,
    EL.TIMESTAMP        AS Timestamp,
    EL.TAGS             AS Tags
FROM TEVENTLOG EL
LEFT JOIN MEVENTTYPE         ET  ON EL.EVENTTYPEID = ET.EVENTTYPEID
LEFT JOIN MUSER              U   ON EL.USERID       = U.USERID
LEFT JOIN MBIZTRANSACTIONTYPE S  ON EL.SOURCEID     = S.BIZTRANSACTIONTYPEID
LEFT JOIN MORGANIZATIONUNIT  OU  ON EL.OUID         = OU.OUID";

        private const string EventLogSelectFlatArchive = @"
    EL.EVENTLOGID       AS EventLogId,
    EL.EVENTTYPEID      AS EventTypeId,
    ET.EVENTTYPECODE    AS EventTypeCode,
    ET.EVENTTYPENAME    AS EventTypeName,
    EL.USERID           AS UserId,
    U.USERNAME          AS UserName,
    U.USERCODE          AS UserCode,
    EL.EVENTTEXT        AS EventText,
    EL.DATA             AS Data,
    EL.SOURCEID         AS SourceId,
    S.BIZTRANSACTIONTYPENAME AS SourceName,
    EL.DATAID           AS DataId,
    EL.OUID             AS OuId,
    OU.ORGANIZATIONUNITNAME  AS OuName,
    EL.MACHINEIP        AS MachineIp,
    EL.TIMESTAMP        AS Timestamp,
    EL.TAGS             AS Tags
FROM TEVENTLOG_ARCHIVE EL
LEFT JOIN MEVENTTYPE         ET  ON EL.EVENTTYPEID = ET.EVENTTYPEID
LEFT JOIN MUSER              U   ON EL.USERID       = U.USERID
LEFT JOIN MBIZTRANSACTIONTYPE S  ON EL.SOURCEID     = S.BIZTRANSACTIONTYPEID
LEFT JOIN MORGANIZATIONUNIT  OU  ON EL.OUID         = OU.OUID";
    }
}