namespace PayRollDAL.Query.SAP
{
    /// <summary>
    /// SQL constants for SAP IT Declaration integration queries.
    /// Required index: TDECLARATION(STATUS, PERIODID, EMPLOYEEID)
    /// </summary>
    public static class ITDeclarationSapQB
    {
        /// <summary>
        /// Fetches all declaration rows (header + detail) needed to build SAP infotype payloads.
        /// Returns one flat row per TDECLARATIONDETAIL record; declarations with no details
        /// return one row with null detail columns.
        ///
        /// Filters (pass 0 to skip a filter):
        ///   @DeclarationId — specific declaration
        ///   @EmployeeId    — specific employee
        ///   @PeriodId      — specific payroll period
        /// </summary>
        public const string GET_DECLARATIONS_FOR_SAP = @"
            SELECT
                D.DECLARATIONID                     AS DeclarationId,
                D.EMPLOYEEID                        AS EmployeeId,
                E.EMPLOYEECODE                      AS EmployeeCode,
                CAST(YEAR(P.FROMDATE) AS NVARCHAR(4)) AS FiscalYear,
                P.FROMDATE                          AS PeriodFromDate,
                P.TODATE                            AS PeriodToDate,
                D.CALCULATIONMETHOD                 AS CalculationMethod,
                D.NOOFEDUCATION                     AS NoOfEducation,
                D.NOOFHOSTEL                        AS NoOfHostel,
                ISNULL(D.PREVIOUSSALARY, 0)         AS PreviousSalary,
                ISNULL(D.PREVIOUSTDS, 0)            AS PreviousTDS,
                ISNULL(D.PREVIOUSPT, 0)             AS PreviousPT,
                ISNULL(D.PREVIOUSPF, 0)             AS PreviousPF,
                ISNULL(D.REMARKS, '')               AS DeclarationRemarks,
                DD.DECLARATIONDETAILID              AS DeclarationDetailId,
                DD.DECLARATIONTYPE                  AS DeclarationDetailType,
                ISNULL(DD.PARTICULARS, '')          AS Particulars,
                ISNULL(DD.DECLAREDAMOUNT, 0)        AS DeclaredAmount,
                ISNULL(DD.APPROVEDAMOUNT, 0)        AS ApprovedAmount,
                DD.FROMPERIOD                       AS FromPeriod,
                DD.TOPERIOD                         AS ToPeriod,
                ISNULL(DD.INTEREST, 0)              AS Interest,
                ISNULL(DD.PRINCIPLE, 0)             AS Principle,
                ISNULL(DD.ISMETRO, 1)               AS IsMetro,
                ISNULL(DD.RENTRECEIVED, 0)          AS RentReceived,
                ISNULL(DD.LOCALTAX, 0)              AS LocalTax,
                ISNULL(DD.STANDARDDEDUCTION, 0)     AS StandardDeduction,
                ISNULL(DD.NETINCOME, 0)             AS NetIncome,
                ISNULL(DD.DETAILREMARKS, '')        AS DetailRemarks,
                ISNULL(TDC.NAME, '')                AS ContactName,
                ISNULL(TDC.PAN, '')                 AS ContactPAN,
                ISNULL(TDC.TAN, '')                 AS ContactTAN,
                ISNULL(TDC.ADDRESSLINE1, '')        AS ContactAddress1,
                ISNULL(TDC.ADDRESSLINE2, '')        AS ContactAddress2,
                ISNULL(TDC.ADDRESSLINE3, '')        AS ContactAddress3,
                ISNULL(TDC.ADDRESSLINE4, '')        AS ContactAddress4,
                ISNULL(TDC.ADDRESSLINE5, '')        AS ContactAddress5,
                ISNULL(TDS.SECTIONDETAIL, '')       AS SectionDetail
            FROM   TDECLARATION D
            INNER JOIN MEMPLOYEE E  ON E.EMPLOYEEID = D.EMPLOYEEID
            INNER JOIN MPERIOD   P  ON P.PERIODID   = D.PERIODID
            LEFT  JOIN TDECLARATIONDETAIL DD ON DD.DECLARATIONID = D.DECLARATIONID
            LEFT  JOIN MTDSCONTACT        TDC ON TDC.TDSCONTACTID = DD.TDSCONTACTID
            LEFT  JOIN MTDSSECTIONDETAIL  TDS ON TDS.TDSSECTIONDETAILID = DD.TDSSECTIONDETAILID
            WHERE  D.STATUS != 2
              AND  (@DeclarationId = 0 OR D.DECLARATIONID = @DeclarationId)
              AND  (@EmployeeId    = 0 OR D.EMPLOYEEID    = @EmployeeId)
              AND  (@PeriodId      = 0 OR D.PERIODID      = @PeriodId)
            ORDER BY D.DECLARATIONID, DD.DECLARATIONDETAILID;";

        /// <summary>PostgreSQL variant — same logic with lowercase identifiers.</summary>
        public const string GET_DECLARATIONS_FOR_SAP_PG = @"
            SELECT
                d.declarationid                           AS ""DeclarationId"",
                d.employeeid                              AS ""EmployeeId"",
                e.employeecode                            AS ""EmployeeCode"",
                CAST(EXTRACT(YEAR FROM p.fromdate) AS TEXT) AS ""FiscalYear"",
                p.fromdate                                AS ""PeriodFromDate"",
                p.todate                                  AS ""PeriodToDate"",
                d.calculationmethod                       AS ""CalculationMethod"",
                d.noofeducation                           AS ""NoOfEducation"",
                d.noofhostel                              AS ""NoOfHostel"",
                COALESCE(d.previoussalary, 0)             AS ""PreviousSalary"",
                COALESCE(d.previoustds, 0)                AS ""PreviousTDS"",
                COALESCE(d.previouspt, 0)                 AS ""PreviousPT"",
                COALESCE(d.previouspf, 0)                 AS ""PreviousPF"",
                COALESCE(d.remarks, '')                   AS ""DeclarationRemarks"",
                dd.declarationdetailid                    AS ""DeclarationDetailId"",
                dd.declarationtype                        AS ""DeclarationDetailType"",
                COALESCE(dd.particulars, '')              AS ""Particulars"",
                COALESCE(dd.declaredamount, 0)            AS ""DeclaredAmount"",
                COALESCE(dd.approvedamount, 0)            AS ""ApprovedAmount"",
                dd.fromperiod                             AS ""FromPeriod"",
                dd.toperiod                               AS ""ToPeriod"",
                COALESCE(dd.interest, 0)                  AS ""Interest"",
                COALESCE(dd.principle, 0)                 AS ""Principle"",
                COALESCE(dd.ismetro, 1)                   AS ""IsMetro"",
                COALESCE(dd.rentreceived, 0)              AS ""RentReceived"",
                COALESCE(dd.localtax, 0)                  AS ""LocalTax"",
                COALESCE(dd.standarddeduction, 0)         AS ""StandardDeduction"",
                COALESCE(dd.netincome, 0)                 AS ""NetIncome"",
                COALESCE(dd.detailremarks, '')            AS ""DetailRemarks"",
                COALESCE(tdc.name, '')                    AS ""ContactName"",
                COALESCE(tdc.pan, '')                     AS ""ContactPAN"",
                COALESCE(tdc.tan, '')                     AS ""ContactTAN"",
                COALESCE(tdc.addressline1, '')            AS ""ContactAddress1"",
                COALESCE(tdc.addressline2, '')            AS ""ContactAddress2"",
                COALESCE(tdc.addressline3, '')            AS ""ContactAddress3"",
                COALESCE(tdc.addressline4, '')            AS ""ContactAddress4"",
                COALESCE(tdc.addressline5, '')            AS ""ContactAddress5"",
                COALESCE(tds.sectiondetail, '')           AS ""SectionDetail""
            FROM   tdeclaration d
            INNER JOIN memployee e  ON e.employeeid = d.employeeid
            INNER JOIN mperiod   p  ON p.periodid   = d.periodid
            LEFT  JOIN tdeclarationdetail dd ON dd.declarationid = d.declarationid
            LEFT  JOIN mtdscontact        tdc ON tdc.tdscontactid = dd.tdscontactid
            LEFT  JOIN mtdssectiondetail  tds ON tds.tdssectiondetailid = dd.tdssectiondetailid
            WHERE  d.status != 2
              AND  (@DeclarationId = 0 OR d.declarationid = @DeclarationId)
              AND  (@EmployeeId    = 0 OR d.employeeid    = @EmployeeId)
              AND  (@PeriodId      = 0 OR d.periodid      = @PeriodId)
            ORDER BY d.declarationid, dd.declarationdetailid;";
    }
}