namespace FrameworkDAL.Query.ChangeTracking
{
    /// <summary>
    /// SQL constants for TEVENTLOGCHANGE.
    /// Required indexes (see migration AuditChangeTracking_Tables.sql):
    ///   IX_TEVENTLOGCHANGE_EVENTLOGID      — fast lookup by event log row
    ///   IX_TEVENTLOGCHANGE_TENANTID_FIELDNAME — fast field-filter queries
    /// </summary>
    public static class ChangeTrackingQB
    {
        /// <summary>
        /// Bulk-inserts one change record per field difference.
        /// Called once per EventLogId; repeated per row in a loop inside ChangeTrackingDAL.
        /// Using individual INSERTs (not TVP) because change counts are small (typically &lt;20 per event).
        /// </summary>
        public const string INSERT_CHANGE = @"
INSERT INTO TEVENTLOGCHANGE (EVENTLOGID, FIELDNAME, OLDVALUE, NEWVALUE, TENANTID)
VALUES (@EventLogId, @FieldName, @OldValue, @NewValue, @TenantId)";

        /// <summary>
        /// Returns all change records for a given audit event, newest row first.
        /// </summary>
        public const string GET_BY_EVENTLOG_ID = @"
SELECT
    EVENTLOGCHANGEID    AS EventLogChangeId,
    EVENTLOGID          AS EventLogId,
    FIELDNAME           AS FieldName,
    OLDVALUE            AS OldValue,
    NEWVALUE            AS NewValue,
    TENANTID            AS TenantId,
    CREATEDON           AS CreatedOn
FROM TEVENTLOGCHANGE
WHERE EVENTLOGID = @EventLogId
  AND TENANTID   = @TenantId
ORDER BY EVENTLOGCHANGEID";
    }
}