namespace IceImportDAL.Query.IceMapFtpSource;

// SQL constants for MICEMAPFTPSOURCE / MICEMAPFTPPROCESSEDFILE — column names verified against
// DB/Migrations/20260708_IceImport_Phase0_Schema.sql (MICEMAPFTPSOURCE) and
// DB/Migrations/20260709_IceImport_Phase4_Schema.sql (MICEMAPFTPPROCESSEDFILE), not inferred from
// DTO property names.
//
// Neither table carries a TENANTID/CLIENTID column (mirrors MICEMAP/MICEMAPDETAILS themselves,
// which are also un-filtered by tenant at the SQL level — tenant isolation for this whole family of
// tables comes from IQueryExecutor's per-tenant connection routing off LoginDTO, not a WHERE
// clause). Every method here still requires LoginDTO purely to route to the right physical
// database; callers (ScheduledImportService) additionally validate IceMapId ownership up front via
// IIceMapBLL.GetIceMap (throws if the map doesn't resolve for the caller's tenant) before ever
// touching MICEMAPFTPSOURCE, same discipline IceImportRunExecutionService already uses.
public static class IceMapFtpSourceQB
{
    // ICEMAPID is the table's PK (one FTP source config per IceMap) — plain equality lookup.
    public const string GET_BY_ICEMAPID = @"
        SELECT ICEMAPID            AS IceMapId,
               CONNECTIONTYPE      AS ConnectionType,
               HOSTVAULTREF        AS HostVaultRef,
               USERNAMEVAULTREF    AS UsernameVaultRef,
               CREDENTIALVAULTREF  AS CredentialVaultRef,
               REMOTEFOLDERPATH    AS RemoteFolderPath,
               FILENAMEPATTERN     AS FileNamePattern,
               POSTPROCESSACTION   AS PostProcessAction,
               ARCHIVEFOLDERPATH   AS ArchiveFolderPath
        FROM   MICEMAPFTPSOURCE
        WHERE  ICEMAPID = @IceMapId";

    // Upsert — one row per IceMapId (PK). Mirrors PayRollDAL.Query.Employee.EmployeeQB's
    // SAVE_OR_UPDATE_EMPLOYEE_ESI/PF MERGE pattern (this codebase's established single-row-per-key
    // upsert idiom for SQL Server).
    public const string SAVE = @"
        MERGE INTO MICEMAPFTPSOURCE AS Target
        USING (SELECT @IceMapId AS ICEMAPID) AS Source
        ON (Target.ICEMAPID = Source.ICEMAPID)
        WHEN MATCHED THEN
            UPDATE SET
                CONNECTIONTYPE     = @ConnectionType,
                HOSTVAULTREF       = @HostVaultRef,
                USERNAMEVAULTREF   = @UsernameVaultRef,
                CREDENTIALVAULTREF = @CredentialVaultRef,
                REMOTEFOLDERPATH   = @RemoteFolderPath,
                FILENAMEPATTERN    = @FileNamePattern,
                POSTPROCESSACTION  = @PostProcessAction,
                ARCHIVEFOLDERPATH  = @ArchiveFolderPath
        WHEN NOT MATCHED THEN
            INSERT (ICEMAPID, CONNECTIONTYPE, HOSTVAULTREF, USERNAMEVAULTREF, CREDENTIALVAULTREF,
                    REMOTEFOLDERPATH, FILENAMEPATTERN, POSTPROCESSACTION, ARCHIVEFOLDERPATH)
            VALUES (@IceMapId, @ConnectionType, @HostVaultRef, @UsernameVaultRef, @CredentialVaultRef,
                    @RemoteFolderPath, @FileNamePattern, @PostProcessAction, @ArchiveFolderPath);";

    // Covering: PK (ICEMAPID, REMOTEFILENAME) — single-row lookup, no separate index needed.
    public const string GET_PROCESSED_LAST_MODIFIED = @"
        SELECT REMOTEFILELASTMODIFIEDUTC
        FROM   MICEMAPFTPPROCESSEDFILE
        WHERE  ICEMAPID = @IceMapId AND REMOTEFILENAME = @RemoteFileName";

    // Upsert — one row per (IceMapId, RemoteFileName); re-processing the same file just refreshes
    // REMOTEFILELASTMODIFIEDUTC/RUNID/PROCESSEDON on the existing row rather than accumulating a
    // new one, matching the migration's PK shape and IsFileAlreadyProcessedAsync's
    // "compare stored vs. current timestamp" contract.
    public const string MARK_PROCESSED = @"
        MERGE INTO MICEMAPFTPPROCESSEDFILE AS Target
        USING (SELECT @IceMapId AS ICEMAPID, @RemoteFileName AS REMOTEFILENAME) AS Source
        ON (Target.ICEMAPID = Source.ICEMAPID AND Target.REMOTEFILENAME = Source.REMOTEFILENAME)
        WHEN MATCHED THEN
            UPDATE SET
                REMOTEFILELASTMODIFIEDUTC = @RemoteFileLastModifiedUtc,
                RUNID       = @RunId,
                PROCESSEDON = SYSUTCDATETIME()
        WHEN NOT MATCHED THEN
            INSERT (ICEMAPID, REMOTEFILENAME, REMOTEFILELASTMODIFIEDUTC, RUNID, PROCESSEDON)
            VALUES (@IceMapId, @RemoteFileName, @RemoteFileLastModifiedUtc, @RunId, SYSUTCDATETIME());";
}