namespace ComplianceDAL.Query.ComplianceAlert;

public static class ComplianceAlertQB
{
    // Requires index on TCOMPLIANCE_ALERT(GSTIN, ISACTIVE, CLIENTID, DATABASENAME)
    public const string GET_ACTIVE_ALERTS = @"
        SELECT ALERTID, ALERTTYPE, GSTIN, RETURNPERIOD, ARTIFACTID,
               ALERTMESSAGE, SEVERITY, ISACTIVE, DISMISSEDBYID, DISMISSEDON, CREATEDON
        FROM   TCOMPLIANCE_ALERT
        WHERE  ISACTIVE     = 1
          AND  CLIENTID     = @ClientId
          AND  DATABASENAME = @DatabaseName
          AND  (@GSTIN = '' OR GSTIN = @GSTIN)
        ORDER BY
            CASE SEVERITY WHEN 'Critical' THEN 1 WHEN 'Warning' THEN 2 ELSE 3 END,
            CREATEDON DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_ACTIVE_ALERTS_COUNT = @"
        SELECT COUNT(*)
        FROM   TCOMPLIANCE_ALERT
        WHERE  ISACTIVE     = 1
          AND  CLIENTID     = @ClientId
          AND  DATABASENAME = @DatabaseName
          AND  (@GSTIN = '' OR GSTIN = @GSTIN)";

    // Idempotent upsert — deduplicates on AlertType+GSTIN+ReturnPeriod to avoid re-alerting on same
    // event. Matches on the same columns IX_ALERT_TYPE_PERIOD covers (that index is non-unique, so
    // this MERGE — not a DB constraint — is what actually enforces the dedup).
    public const string UPSERT_ALERT = @"
        MERGE TCOMPLIANCE_ALERT AS target
        USING (SELECT @AlertType AS ALERTTYPE, @ReturnPeriod AS RETURNPERIOD, @GSTIN AS GSTIN) AS src
        ON  target.ALERTTYPE    = src.ALERTTYPE
        AND target.RETURNPERIOD = src.RETURNPERIOD
        AND target.GSTIN        = src.GSTIN
        AND target.CLIENTID     = @ClientId
        AND target.DATABASENAME = @DatabaseName
        WHEN MATCHED THEN UPDATE SET
            ALERTMESSAGE  = @AlertMessage,
            SEVERITY      = @Severity,
            ISACTIVE      = 1,
            DISMISSEDBYID = NULL,
            DISMISSEDON   = NULL
        WHEN NOT MATCHED THEN INSERT
            (ALERTTYPE, GSTIN, RETURNPERIOD, ARTIFACTID, ALERTMESSAGE, SEVERITY, ISACTIVE, CLIENTID, DATABASENAME)
        VALUES
            (@AlertType, @GSTIN, @ReturnPeriod, @ArtifactId, @AlertMessage, @Severity, 1, @ClientId, @DatabaseName)
        OUTPUT INSERTED.ALERTID;";

    public const string DISMISS_ALERT = @"
        UPDATE TCOMPLIANCE_ALERT
        SET    ISACTIVE     = 0,
               DISMISSEDBYID = @DismissedById,
               DISMISSEDON  = GETUTCDATE()
        WHERE  ALERTID      = @AlertId
          AND  CLIENTID     = @ClientId
          AND  DATABASENAME = @DatabaseName";

    public const string GET_ALERT_CONFIG = @"
        SELECT ALERTCONFIGID, GSTIN,
               FILINGDEADLINEDAYSBEFOR AS FilingDeadlineDaysBefore,
               EWBEXPIRYHOURSBEFORE    AS EWBExpiryHoursBefore,
               IRNPENDINGDAYSWINDOW    AS IRNPendingDaysWindow,
               SUBMISSIONFAILEDHOURS   AS SubmissionFailedHours,
               ENABLEFILINGALERTS, ENABLERECONALERTS, ENABLEEWBALERTS, ENABLEIRNALERTS
        FROM   MCOMPLIANCE_ALERT_CONFIG
        WHERE  GSTIN        = @GSTIN
          AND  CLIENTID     = @ClientId
          AND  DATABASENAME = @DatabaseName";

    // Upsert keyed on the real unique constraint UX_ALERT_CONFIG_GSTIN (GSTIN, CLIENTID, DATABASENAME)
    public const string UPSERT_ALERT_CONFIG = @"
        MERGE MCOMPLIANCE_ALERT_CONFIG AS target
        USING (SELECT @GSTIN AS GSTIN) AS src
        ON  target.GSTIN        = src.GSTIN
        AND target.CLIENTID     = @ClientId
        AND target.DATABASENAME = @DatabaseName
        WHEN MATCHED THEN UPDATE SET
            FILINGDEADLINEDAYSBEFOR = @FilingDeadlineDaysBefore,
            EWBEXPIRYHOURSBEFORE    = @EWBExpiryHoursBefore,
            IRNPENDINGDAYSWINDOW    = @IRNPendingDaysWindow,
            SUBMISSIONFAILEDHOURS   = @SubmissionFailedHours,
            ENABLEFILINGALERTS      = @EnableFilingAlerts,
            ENABLERECONALERTS       = @EnableReconAlerts,
            ENABLEEWBALERTS         = @EnableEWBAlerts,
            ENABLEIRNALERTS         = @EnableIRNAlerts
        WHEN NOT MATCHED THEN INSERT
            (GSTIN, FILINGDEADLINEDAYSBEFOR, EWBEXPIRYHOURSBEFORE, IRNPENDINGDAYSWINDOW,
             SUBMISSIONFAILEDHOURS, ENABLEFILINGALERTS, ENABLERECONALERTS,
             ENABLEEWBALERTS, ENABLEIRNALERTS, CLIENTID, DATABASENAME)
        VALUES
            (@GSTIN, @FilingDeadlineDaysBefore, @EWBExpiryHoursBefore, @IRNPendingDaysWindow,
             @SubmissionFailedHours, @EnableFilingAlerts, @EnableReconAlerts,
             @EnableEWBAlerts, @EnableIRNAlerts, @ClientId, @DatabaseName)
        OUTPUT INSERTED.ALERTCONFIGID;";

    // Find GSTINs with filing deadlines approaching — used by nightly alert job
    public const string GET_PENDING_FILING_GSTINS = @"
        SELECT p.GSTIN, p.RETURNTYPE, p.PERIODYEAR, p.PERIODMONTH
        FROM   TCOMPLIANCE_PERIOD p
        JOIN   MCOMPLIANCE_ALERT_CONFIG cfg
               ON  cfg.GSTIN        = p.GSTIN
               AND cfg.CLIENTID     = p.CLIENTID
               AND cfg.DATABASENAME = p.DATABASENAME
               AND cfg.ENABLEFILINGALERTS = 1
        WHERE  p.STATUS        NOT IN ('Filed','Submitted')
          AND  p.CLIENTID      = @ClientId
          AND  p.DATABASENAME  = @DatabaseName
          AND  (
                CASE p.RETURNTYPE
                    WHEN 'GSTR1'  THEN DATEADD(day, 11, DATEADD(month, 1, DATEFROMPARTS(p.PERIODYEAR, p.PERIODMONTH, 1)))
                    WHEN 'GSTR3B' THEN DATEADD(day, 20, DATEADD(month, 1, DATEFROMPARTS(p.PERIODYEAR, p.PERIODMONTH, 1)))
                    ELSE               DATEADD(month, 2, DATEFROMPARTS(p.PERIODYEAR, p.PERIODMONTH, 1))
                END
               ) <= DATEADD(day, cfg.FILINGDEADLINEDAYSBEFOR, GETUTCDATE())";

    // Find EWBs expiring within the configured window
    public const string GET_EXPIRING_EWBS = @"
        SELECT e.EWAYBILLID, e.GSTIN, e.EWBNUMBER, e.VALIDUPTO
        FROM   TGST_EWAYBILL e
        JOIN   MCOMPLIANCE_ALERT_CONFIG cfg
               ON  cfg.GSTIN        = e.GSTIN
               AND cfg.CLIENTID     = e.CLIENTID
               AND cfg.DATABASENAME = e.DATABASENAME
               AND cfg.ENABLEEWBALERTS = 1
        WHERE  e.STATUS        = 'Active'
          AND  e.CLIENTID      = @ClientId
          AND  e.DATABASENAME  = @DatabaseName
          AND  e.VALIDUPTO     <= DATEADD(hour, cfg.EWBEXPIRYHOURSBEFORE, GETUTCDATE())
          AND  e.VALIDUPTO      > GETUTCDATE()";

    // Find invoices without IRN older than configured window
    public const string GET_INVOICES_WITHOUT_IRN = @"
        SELECT o.OUTWARDID, o.GSTIN, o.RETURNPERIOD, o.INVOICENUMBER, o.INVOICEDATE
        FROM   TGST_OUTWARD o
        JOIN   MCOMPLIANCE_ALERT_CONFIG cfg
               ON  cfg.GSTIN        = o.GSTIN
               AND cfg.CLIENTID     = o.CLIENTID
               AND cfg.DATABASENAME = o.DATABASENAME
               AND cfg.ENABLEIRNALERTS = 1
        WHERE  o.CLIENTID     = @ClientId
          AND  o.DATABASENAME = @DatabaseName
          AND  o.INVOICEDATE  <= DATEADD(day, -cfg.IRNPENDINGDAYSWINDOW, GETUTCDATE())
          AND  NOT EXISTS (
               SELECT 1 FROM TGST_EINVOICE e
               WHERE  e.DOCUMENTID   = o.SOURCEDOCUMENTID
                 AND  e.CLIENTID     = o.CLIENTID
                 AND  e.DATABASENAME = o.DATABASENAME
                 AND  e.STATUS       = 'Generated')";

    // Find old REJECTED artifacts
    public const string GET_OLD_REJECTED_ARTIFACTS = @"
        SELECT a.ARTIFACTID, a.GSTIN, a.RETURNPERIOD, a.CREATEDON
        FROM   TCOMPLIANCE_ARTIFACT a
        JOIN   MCOMPLIANCE_ALERT_CONFIG cfg
               ON  cfg.GSTIN        = a.GSTIN
               AND cfg.CLIENTID     = a.CLIENTID
               AND cfg.DATABASENAME = a.DATABASENAME
        WHERE  a.STATUS      = 'REJECTED'
          AND  a.CLIENTID    = @ClientId
          AND  a.DATABASENAME= @DatabaseName
          AND  a.CREATEDON   <= DATEADD(hour, -cfg.SUBMISSIONFAILEDHOURS, GETUTCDATE())";
}