namespace FrameworkDAL.Query.Archive
{
    /// <summary>
    /// SQL for the nightly archive job.
    /// Both statements are run inside a single transaction per tenant (BEGIN/COMMIT in job).
    ///
    /// TEVENTLOG_ARCHIVE must exist before running (see AuditChangeTracking_Tables.sql migration).
    /// ARCHIVEDON column in TEVENTLOG_ARCHIVE records when the row was moved.
    /// </summary>
    public static class EventLogArchiveQB
    {
        /// <summary>
        /// Copies rows older than the retention cutoff from TEVENTLOG → TEVENTLOG_ARCHIVE.
        /// Uses INSERT…SELECT for batch efficiency (no row-by-row loop).
        /// </summary>
        public const string INSERT_ARCHIVE = @"
INSERT INTO TEVENTLOG_ARCHIVE (
    EVENTLOGID, EVENTTEXT, LINK, SOURCEID, USERID, TAGS, EVENTVALUE,
    DATA, GEO, EVENTTYPEID, EVENTCLASSID, LOGBOOKID, DATAID, [TIMESTAMP],
    EVENTCHANNELID, OUID, MACHINEIP, MODEOFWORKING, LOGINEVENTLOGID,
    TENANTID, ARCHIVEDON
)
SELECT
    EVENTLOGID, EVENTTEXT, LINK, SOURCEID, USERID, TAGS, EVENTVALUE,
    DATA, GEO, EVENTTYPEID, EVENTCLASSID, LOGBOOKID, DATAID, [TIMESTAMP],
    EVENTCHANNELID, OUID, MACHINEIP, MODEOFWORKING, LOGINEVENTLOGID,
    TENANTID, GETDATE()
FROM TEVENTLOG
WHERE TENANTID  = @TenantId
  AND [TIMESTAMP] < @CutoffDate";

        /// <summary>
        /// Removes the rows that were just archived from TEVENTLOG.
        /// Always run AFTER INSERT_ARCHIVE, inside the same transaction.
        /// </summary>
        public const string DELETE_HOT = @"
DELETE FROM TEVENTLOG
WHERE TENANTID    = @TenantId
  AND [TIMESTAMP] < @CutoffDate";

        /// <summary>Returns counts from both tables for health-check / stats logging.</summary>
        public const string GET_STATS = @"
SELECT
    (SELECT COUNT(*) FROM TEVENTLOG         WHERE TENANTID = @TenantId) AS HotCount,
    (SELECT COUNT(*) FROM TEVENTLOG_ARCHIVE WHERE TENANTID = @TenantId) AS ArchiveCount";
    }
}