namespace ComplianceDAL.Query.ComplianceAnalytics;

public static class ComplianceAnalyticsQB
{
    // Requires indexes on TGST_OUTWARD(GSTIN, RETURNPERIOD, FILINGSTATUS, CLIENTID, DATABASENAME)
    // and TGST_INWARD(OURGSTIN, RETURNPERIOD, CLIENTID, DATABASENAME)
    public const string GET_GST_SUMMARY = @"
        SELECT
            o.GSTIN,
            o.RETURNPERIOD                                                           AS ReturnPeriod,
            COALESCE(SUM(o.TAXABLEVALUE),  0)                                       AS TotalTaxableValue,
            COALESCE(SUM(o.IGSTAMOUNT),    0)                                       AS TotalIGST,
            COALESCE(SUM(o.CGSTAMOUNT),    0)                                       AS TotalCGST,
            COALESCE(SUM(o.SGSTAMOUNT),    0)                                       AS TotalSGST,
            COALESCE(SUM(o.CESSAMOUNT),    0)                                       AS TotalCess,
            COALESCE(SUM(i.IGSTAMOUNT),    0)                                       AS TotalITCIGST,
            COALESCE(SUM(i.CGSTAMOUNT),    0)                                       AS TotalITCCGST,
            COALESCE(SUM(i.SGSTAMOUNT),    0)                                       AS TotalITCSGST,
            COUNT(DISTINCT o.OUTWARDID)                                             AS TotalDocuments,
            COUNT(DISTINCT CASE WHEN o.FILINGSTATUS = 2 THEN o.OUTWARDID END)       AS FiledDocuments,
            COUNT(DISTINCT CASE WHEN o.FILINGSTATUS = 0 THEN o.OUTWARDID END)       AS PendingDocuments,
            COUNT(DISTINCT CASE WHEN o.FILINGSTATUS < 0 THEN o.OUTWARDID END)       AS RejectedDocuments
        FROM  TGST_OUTWARD o
        LEFT  JOIN TGST_INWARD i
              ON  i.OURGSTIN      = o.GSTIN
              AND i.RETURNPERIOD  = o.RETURNPERIOD
              AND i.CLIENTID      = o.CLIENTID
              AND i.DATABASENAME  = o.DATABASENAME
        WHERE o.GSTIN        = @GSTIN
          AND o.RETURNPERIOD = @ReturnPeriod
          AND o.CLIENTID     = @ClientId
          AND o.DATABASENAME = @DatabaseName
        GROUP BY o.GSTIN, o.RETURNPERIOD";

    // Requires index on TGST_OUTWARD(CLIENTID, DATABASENAME, RETURNPERIOD)
    // and a join to OU master (MOUID) — columns OUId/OUName must come from ERP party tables
    public const string GET_OUWISE_SUMMARY = @"
        SELECT
            o.OUID                          AS OUId,
            ''                              AS OUName,
            o.GSTIN,
            o.RETURNPERIOD                  AS ReturnPeriod,
            SUM(o.TAXABLEVALUE)             AS TaxableValue,
            SUM(o.IGSTAMOUNT)               AS IGSTAmount,
            SUM(o.CGSTAMOUNT)               AS CGSTAmount,
            SUM(o.SGSTAMOUNT)               AS SGSTAmount,
            SUM(o.CESSAMOUNT)               AS CessAmount,
            COUNT(*)                        AS DocumentCount
        FROM  TGST_OUTWARD o
        WHERE o.RETURNPERIOD = @ReturnPeriod
          AND o.CLIENTID     = @ClientId
          AND o.DATABASENAME = @DatabaseName
          AND (@GSTIN = '' OR o.GSTIN = @GSTIN)
        GROUP BY o.OUID, o.GSTIN, o.RETURNPERIOD
        ORDER BY o.OUID, o.GSTIN";

    // HSN summary aggregated from TGST_OUTWARD (HSN columns stored per invoice row)
    // Requires HSNCODE, UOM columns on TGST_OUTWARD (added via TGST_OUTWARD_HSN join or inline fields)
    public const string GET_HSN_SUMMARY = @"
        SELECT
            o.HSNCODE                       AS HSNCode,
            ''                              AS Description,
            o.UOM                           AS UOM,
            SUM(o.QUANTITY)                 AS TotalQuantity,
            SUM(o.TAXABLEVALUE)             AS TaxableValue,
            SUM(o.IGSTAMOUNT)               AS IGSTAmount,
            SUM(o.CGSTAMOUNT)               AS CGSTAmount,
            SUM(o.SGSTAMOUNT)               AS SGSTAmount,
            SUM(o.CESSAMOUNT)               AS CessAmount
        FROM  TGST_OUTWARD o
        WHERE o.RETURNPERIOD = @ReturnPeriod
          AND o.CLIENTID     = @ClientId
          AND o.DATABASENAME = @DatabaseName
          AND (@GSTIN = '' OR o.GSTIN = @GSTIN)
          AND o.HSNCODE IS NOT NULL
        GROUP BY o.HSNCODE, o.UOM
        ORDER BY o.HSNCODE";

    // Exception report: missing IRN, recon mismatches, submission failures, pending returns
    public const string GET_EXCEPTION_REPORT = @"
        SELECT 'MissingIRN'         AS ExceptionType,
               o.GSTIN,
               o.RETURNPERIOD       AS ReturnPeriod,
               o.INVOICENUMBER      AS DocumentNumber,
               'Invoice has no IRN' AS Description,
               'Warning'            AS Severity,
               o.INVOICEDATE        AS DocumentDate,
               CAST(NULL AS INT)    AS ArtifactId
        FROM   TGST_OUTWARD o
        WHERE  o.RETURNPERIOD = @ReturnPeriod
          AND  o.CLIENTID     = @ClientId
          AND  o.DATABASENAME = @DatabaseName
          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')

        UNION ALL

        SELECT 'ReconMismatch',
               r.OURGSTIN,
               r.PERIOD,
               i.INVOICENUMBER,
               'Reconciliation mismatch: ' + r.STATUS,
               'Warning',
               CAST(NULL AS DATE),
               CAST(NULL AS INT)
        FROM   TGST_RECON r
        JOIN   TGST_INWARD i ON i.INWARDID = r.INWARDID
        WHERE  r.PERIOD      = @ReturnPeriod
          AND  r.CLIENTID    = @ClientId
          AND  r.DATABASENAME= @DatabaseName
          AND  r.STATUS       IN ('Mismatch','MissingInPortal','MissingInERP')

        UNION ALL

        SELECT 'SubmissionFailed',
               a.GSTIN,
               a.RETURNPERIOD,
               CAST(a.ARTIFACTID AS VARCHAR(20)),
               'Artifact in REJECTED state',
               'Critical',
               CAST(a.CREATEDON AS DATE),
               a.ARTIFACTID
        FROM   TCOMPLIANCE_ARTIFACT a
        WHERE  a.STATUS      = 'REJECTED'
          AND  a.RETURNPERIOD = @ReturnPeriod
          AND  a.CLIENTID    = @ClientId
          AND  a.DATABASENAME= @DatabaseName

        ORDER BY ExceptionType, CASE WHEN DocumentDate IS NULL THEN 1 ELSE 0 END, DocumentDate
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_EXCEPTION_COUNT = @"
        SELECT COUNT(*) FROM (
            SELECT 1 FROM TGST_OUTWARD o
            WHERE  o.RETURNPERIOD = @ReturnPeriod
              AND  o.CLIENTID     = @ClientId
              AND  o.DATABASENAME = @DatabaseName
              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')
            UNION ALL
            SELECT 1 FROM TGST_RECON r
            WHERE  r.PERIOD      = @ReturnPeriod
              AND  r.CLIENTID    = @ClientId
              AND  r.DATABASENAME= @DatabaseName
              AND  r.STATUS       IN ('Mismatch','MissingInPortal','MissingInERP')
            UNION ALL
            SELECT 1 FROM TCOMPLIANCE_ARTIFACT a
            WHERE  a.STATUS      = 'REJECTED'
              AND  a.RETURNPERIOD = @ReturnPeriod
              AND  a.CLIENTID    = @ClientId
              AND  a.DATABASENAME= @DatabaseName
        ) x";

    // Filing status calendar — all GSTINs × return types × months for a given year
    // Requires index on TCOMPLIANCE_PERIOD(CLIENTID, DATABASENAME, PERIODYEAR)
    public const string GET_FILING_STATUS_SUMMARY = @"
        SELECT
            p.GSTIN,
            COALESCE(r.LEGALNAME, '')       AS LegalName,
            p.PERIODYEAR                    AS PeriodYear,
            p.PERIODMONTH                   AS PeriodMonth,
            p.RETURNTYPE                    AS ReturnType,
            CASE
                WHEN p.STATUS = 'Filed'     THEN 'Filed'
                WHEN p.STATUS = 'Submitted' THEN 'Submitted'
                WHEN p.STATUS = 'Draft'     THEN 'Draft'
                WHEN GETUTCDATE() > (
                    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(day, 31, DATEADD(month, 1, DATEFROMPARTS(p.PERIODYEAR, p.PERIODMONTH, 1)))
                    END)
                THEN 'Overdue'
                ELSE 'NotStarted'
            END                             AS Status,
            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                             AS DueDate,
            p.FILEDON                       AS FiledOn,
            p.ACKNUMBER                     AS AckNumber
        FROM  TCOMPLIANCE_PERIOD p
        LEFT  JOIN MGST_REGISTRATION r
              ON  r.GSTIN        = p.GSTIN
              AND r.CLIENTID     = p.CLIENTID
              AND r.DATABASENAME = p.DATABASENAME
        WHERE p.PERIODYEAR   = @PeriodYear
          AND p.CLIENTID     = @ClientId
          AND p.DATABASENAME = @DatabaseName
          AND (@GSTIN = '' OR p.GSTIN = @GSTIN)
        ORDER BY p.GSTIN, p.PERIODMONTH, p.RETURNTYPE";

    // Recompute and upsert TCOMPLIANCE_PERIOD_SUMMARY from raw TGST_OUTWARD + TGST_INWARD data
    public const string UPSERT_PERIOD_SUMMARY = @"
        MERGE TCOMPLIANCE_PERIOD_SUMMARY AS target
        USING (SELECT @GSTIN AS GSTIN, @PeriodYear AS PERIODYEAR, @PeriodMonth AS PERIODMONTH,
                      @SummaryType AS SUMMARYTYPE) AS src
        ON  target.GSTIN       = src.GSTIN
        AND target.PERIODYEAR  = src.PERIODYEAR
        AND target.PERIODMONTH = src.PERIODMONTH
        AND target.SUMMARYTYPE = src.SUMMARYTYPE
        AND target.CLIENTID     = @ClientId
        AND target.DATABASENAME = @DatabaseName
        WHEN MATCHED THEN UPDATE SET
            TOTALTAXABLEVALUE = @TotalTaxableValue,
            TOTALIGST         = @TotalIGST,
            TOTALCGST         = @TotalCGST,
            TOTALSGST         = @TotalSGST,
            TOTALCESS         = @TotalCess,
            TOTALITCIGST      = @TotalITCIGST,
            TOTALITCCGST      = @TotalITCCGST,
            TOTALITCSGST      = @TotalITCSGST,
            TOTALDOCUMENTS    = @TotalDocuments,
            INVALIDDOCUMENTS  = @InvalidDocuments,
            RECOMPUTEDON      = GETUTCDATE()
        WHEN NOT MATCHED THEN INSERT
            (GSTIN, PERIODYEAR, PERIODMONTH, SUMMARYTYPE,
             TOTALTAXABLEVALUE, TOTALIGST, TOTALCGST, TOTALSGST, TOTALCESS,
             TOTALITCIGST, TOTALITCCGST, TOTALITCSGST,
             TOTALDOCUMENTS, INVALIDDOCUMENTS, RECOMPUTEDON, CLIENTID, DATABASENAME)
        VALUES
            (@GSTIN, @PeriodYear, @PeriodMonth, @SummaryType,
             @TotalTaxableValue, @TotalIGST, @TotalCGST, @TotalSGST, @TotalCess,
             @TotalITCIGST, @TotalITCCGST, @TotalITCSGST,
             @TotalDocuments, @InvalidDocuments, GETUTCDATE(), @ClientId, @DatabaseName)
        OUTPUT INSERTED.SUMMARYID;";

    public const string GET_PERIOD_SUMMARY_LIST = @"
        SELECT SUMMARYID, GSTIN, PERIODYEAR, PERIODMONTH, SUMMARYTYPE,
               TOTALTAXABLEVALUE, TOTALIGST, TOTALCGST, TOTALSGST, TOTALCESS,
               TOTALITCIGST, TOTALITCCGST, TOTALITCSGST,
               TOTALDOCUMENTS, INVALIDDOCUMENTS, RECOMPUTEDON
        FROM   TCOMPLIANCE_PERIOD_SUMMARY
        WHERE  GSTIN        = @GSTIN
          AND  PERIODYEAR   = @PeriodYear
          AND  CLIENTID     = @ClientId
          AND  DATABASENAME = @DatabaseName
          AND  (@SummaryType = '' OR SUMMARYTYPE = @SummaryType)
        ORDER BY PERIODMONTH, SUMMARYTYPE";

    // Raw GSTR-1 outward aggregate per period for recomputation
    public const string GET_OUTWARD_AGGREGATE_FOR_SUMMARY = @"
        SELECT
            COALESCE(SUM(TAXABLEVALUE), 0)  AS TotalTaxableValue,
            COALESCE(SUM(IGSTAMOUNT),   0)  AS TotalIGST,
            COALESCE(SUM(CGSTAMOUNT),   0)  AS TotalCGST,
            COALESCE(SUM(SGSTAMOUNT),   0)  AS TotalSGST,
            COALESCE(SUM(CESSAMOUNT),   0)  AS TotalCess,
            COUNT(*)                        AS TotalDocuments
        FROM  TGST_OUTWARD
        WHERE GSTIN        = @GSTIN
          AND RETURNPERIOD = @ReturnPeriod
          AND CLIENTID     = @ClientId
          AND DATABASENAME = @DatabaseName";

    public const string GET_INWARD_ITC_FOR_SUMMARY = @"
        SELECT
            COALESCE(SUM(IGSTAMOUNT), 0) AS TotalITCIGST,
            COALESCE(SUM(CGSTAMOUNT), 0) AS TotalITCCGST,
            COALESCE(SUM(SGSTAMOUNT), 0) AS TotalITCSGST
        FROM  TGST_INWARD
        WHERE OURGSTIN     = @GSTIN
          AND RETURNPERIOD = @ReturnPeriod
          AND ITCELIGIBLE  = 1
          AND CLIENTID     = @ClientId
          AND DATABASENAME = @DatabaseName";
}