using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.Relieving
{
    public static class RelievingQB
    {
        public const string GET_RELIEVING = @"SELECT
    A.RELIEVINGID AS RelievingId,

    A.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    A.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,

    A.RELIEVINGNUMBER AS RelievingRelievingNumber,
    A.RELIEVINGDATE AS RelievingRelievingDate,
    A.REFERENCENUMBER AS RelievingReferenceNumber,
    A.REFERENCEDATE AS RelievingReferenceDate,

    A.EMPLOYEEID AS EmployeeId,
    E.EMPLOYEECODE AS EmployeeCode,
    E.EMPLOYEENAME AS EmployeeName,

    A.RELIEVINGINCHARGEID AS InchargeId,
    I.EMPLOYEECODE AS InchargeCode,
    I.EMPLOYEENAME AS InchargeName,

    A.RELIEVINGTYPEID AS RelievingTypeId,
    GC.GCMCODE AS RelievingTypeCode,
    GC.GCMNAME AS RelievingTypeName,

    A.NOCLISTID AS NOCListId,
    NL.NOCLISTCODE AS NOCListCode,
    NL.NOCLISTNAME AS NOCListName,

    A.REASON AS RelievingReason,
    A.DATEOFINTIMATION AS RelievingDateOfIntimation,
    A.DATEOFRELIVING AS RelievingDateOfRelieving,
    A.SEPARATIONDATE AS RelievingSeparationDate,
    A.REMARKS AS RelievingRemarks,
    A.RELIEVINGSTATUS AS RelievingRelivingStatus,

    A.SETTLEMENTAMOUNT AS RelievingSettlementAmount,
    A.SETTLEMENTTYPE AS RelievingSettlementType,
    A.SETTLEMENTSTATUS AS RelievingSettlementStatus,

    A.SALARYFORDAYS AS RelievingSalaryForDays,
    A.NOTICEPERIOD AS RelievingNoticePeriod,
    A.RECOVERYDAYS AS RelievingRecoveryDays,
    A.TERMINATIONDAYS AS RelievingTerminationDays,
    A.OTHERDAYS AS RelievingOtherDays,
    A.PAYFORDAYS AS RelievingPayForDays,
    A.DAYSREMARKS AS RelievingDaysRemarks,
    A.ENCASHDAYS AS RelievingEnCashDays,
    A.EXCESSLEAVEDAYS AS RelievingExcessLeaveDays,
    A.BONUSPERCENT AS RelievingBonusPercent,

    A.PAYPERIODID AS PayPeriodId,
    PP.PERIODCODE AS PayPeriodCode,
    PP.PERIODNAME AS PayPeriodName,

    A.STATUS AS RelievingStatus,
    A.VERSION AS RelievingVersion,
    A.CREATEDBYID AS RelievingCreatedById,
    A.CREATEDON AS RelievingCreatedOn,
    A.MODIFIEDBYID AS RelievingModifiedById,
    A.MODIFIEDON AS RelievingModifiedOn

FROM TRELIEVING A
LEFT JOIN MORGANIZATIONUNIT OU   ON A.OUID = OU.ORGANIZATIONUNITID
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON A.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MEMPLOYEE E             ON A.EMPLOYEEID = E.EMPLOYEEID
LEFT JOIN MEMPLOYEE I             ON A.RELIEVINGINCHARGEID = I.EMPLOYEEID
LEFT JOIN MGCM GC                 ON A.RELIEVINGTYPEID = GC.GCMID
LEFT JOIN MNOCLIST NL             ON A.NOCLISTID = NL.NOCLISTID
LEFT JOIN MPAYPERIOD PP           ON A.PAYPERIODID = PP.PAYPERIODID
WHERE A.RELIEVINGID = @relievingid";

        public const string GET_RELIEVINGDETAIL = @"SELECT
    D.RELIEVINGDETAILID AS RelievingDetailId,
    D.RELIEVINGID AS RelievingId,
    D.SLNO AS RelievingDetailSlNo,

    D.NOCLISTDETAILID AS NOCListDetailId,
    ND.DEPARTMENTID AS NOCListDetailDepartmentId,
    DEPT.DEPARTMENTCODE AS NOCListDetailDepartmentCode,
    DEPT.DEPARTMENTNAME AS NOCListDetailDepartmentName,

    D.STATUS AS RelievingDetailStatus,
    D.STATUSDATE AS RelievingDetailStatusDate,
    D.REMARKS AS RelievingDetailRemarks,

    D.TOPAY AS RelievingDetailToPay,
    D.TOCOLLECT AS RelievingDetailToCollect,

    D.NOCBYID AS NocById,
    NB.EMPLOYEECODE AS NocByCode,
    NB.EMPLOYEENAME AS NocByName

FROM TRELIEVINGDETAIL D
LEFT JOIN MNOCLISTDETAIL ND ON D.NOCLISTDETAILID = ND.NOCLISTDETAILID
LEFT JOIN MDEPARTMENT DEPT  ON ND.DEPARTMENTID = DEPT.DEPARTMENTID
LEFT JOIN MEMPLOYEE NB      ON D.NOCBYID = NB.EMPLOYEEID
WHERE D.RELIEVINGID = @relievingid
ORDER BY D.SLNO";

        public const string SAVE_RELIEVING = @"INSERT INTO TRELIEVING
(
    RELIEVINGID, OUID, BIZTRANSACTIONTYPEID, RELIEVINGNUMBER, RELIEVINGDATE,
    REFERENCENUMBER, REFERENCEDATE, EMPLOYEEID, RELIEVINGINCHARGEID, RELIEVINGTYPEID,
    NOCLISTID, REASON, DATEOFINTIMATION, DATEOFRELIVING, SEPARATIONDATE,
    REMARKS, RELIEVINGSTATUS, SETTLEMENTAMOUNT, SETTLEMENTTYPE, SETTLEMENTSTATUS,
    SALARYFORDAYS, NOTICEPERIOD, RECOVERYDAYS, TERMINATIONDAYS, OTHERDAYS,
    PAYFORDAYS, DAYSREMARKS, ENCASHDAYS, EXCESSLEAVEDAYS, BONUSPERCENT,
    PAYPERIODID, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @RelievingId, @OUId, @BIZTransactionTypeId, @RelievingRelievingNumber, @RelievingRelievingDate,
    @RelievingReferenceNumber, @RelievingReferenceDate, @EmployeeId, @InchargeId, @RelievingTypeId,
    @NOCListId, @RelievingReason, @RelievingDateOfIntimation, @RelievingDateOfRelieving, @RelievingSeparationDate,
    @RelievingRemarks, @RelievingRelivingStatus, @RelievingSettlementAmount, @RelievingSettlementType, @RelievingSettlementStatus,
    @RelievingSalaryForDays, @RelievingNoticePeriod, @RelievingRecoveryDays, @RelievingTerminationDays, @RelievingOtherDays,
    @RelievingPayForDays, @RelievingDaysRemarks, @RelievingEnCashDays, @RelievingExcessLeaveDays, @RelievingBonusPercent,
    @PayPeriodId, @RelievingStatus, 1, @RelievingCreatedById, @RelievingCreatedOn, @RelievingModifiedById, @RelievingModifiedOn
);";

        public const string UPDATE_RELIEVING = @"UPDATE TRELIEVING
SET
    OUID                 = @OUId,
    BIZTRANSACTIONTYPEID = @BIZTransactionTypeId,
    RELIEVINGNUMBER      = @RelievingRelievingNumber,
    RELIEVINGDATE        = @RelievingRelievingDate,
    REFERENCENUMBER      = @RelievingReferenceNumber,
    REFERENCEDATE        = @RelievingReferenceDate,
    EMPLOYEEID           = @EmployeeId,
    RELIEVINGINCHARGEID  = @InchargeId,
    RELIEVINGTYPEID      = @RelievingTypeId,
    NOCLISTID            = @NOCListId,
    REASON               = @RelievingReason,
    DATEOFINTIMATION     = @RelievingDateOfIntimation,
    DATEOFRELIVING       = @RelievingDateOfRelieving,
    SEPARATIONDATE       = @RelievingSeparationDate,
    REMARKS              = @RelievingRemarks,
    RELIEVINGSTATUS      = @RelievingRelivingStatus,
    SETTLEMENTAMOUNT     = @RelievingSettlementAmount,
    SETTLEMENTTYPE       = @RelievingSettlementType,
    SETTLEMENTSTATUS     = @RelievingSettlementStatus,
    SALARYFORDAYS        = @RelievingSalaryForDays,
    NOTICEPERIOD         = @RelievingNoticePeriod,
    RECOVERYDAYS         = @RelievingRecoveryDays,
    TERMINATIONDAYS      = @RelievingTerminationDays,
    OTHERDAYS            = @RelievingOtherDays,
    PAYFORDAYS           = @RelievingPayForDays,
    DAYSREMARKS          = @RelievingDaysRemarks,
    ENCASHDAYS           = @RelievingEnCashDays,
    EXCESSLEAVEDAYS      = @RelievingExcessLeaveDays,
    BONUSPERCENT         = @RelievingBonusPercent,
    PAYPERIODID          = @PayPeriodId,
    STATUS               = @RelievingStatus,
    VERSION              = VERSION + 1,
    MODIFIEDBYID         = @RelievingModifiedById,
    MODIFIEDON           = @RelievingModifiedOn
WHERE RELIEVINGID = @RelievingId;";

        public const string DELETE_RELIEVING = @"DELETE FROM TRELIEVING
                                            WHERE RELIEVINGID = @relievingid;";

        public const string SAVE_RELIEVINGDETAIL = @"INSERT INTO TRELIEVINGDETAIL
(
    RELIEVINGDETAILID, RELIEVINGID, SLNO, NOCLISTDETAILID,
    STATUS, STATUSDATE, REMARKS, TOPAY, TOCOLLECT, NOCBYID
)
VALUES
(
    @RelievingDetailId, @RelievingId, @RelievingDetailSlNo, @NOCListDetailId,
    @RelievingDetailStatus, @RelievingDetailStatusDate, @RelievingDetailRemarks,
    @RelievingDetailToPay, @RelievingDetailToCollect, @NocById
);";

        public const string DELETE_RELIEVINGDETAIL = @"DELETE FROM TRELIEVINGDETAIL
                                            WHERE RELIEVINGID = @relievingid;";

        // ── Relieving Settlement rows (separate settlement grid, save-only endpoint) ──

        public const string SAVE_RELIEVINGSETTLEMENT = @"INSERT INTO TRELIEVINGSETTLEMENT
(
    RELIEVINGSETTLEMENTID, RELIEVINGSETTLEMENTSLNO, RELIEVINGSETTLEMENTOPERATIONTYPE,
    RELIEVINGID, SETTLEMENTAMOUNTTYPEID, RELIEVINGSETTLEMENTAMOUNT, RELIEVINGSETTLEMENTREMARKS,
    SETTLEMENTAMOUNT, SETTLEMENTCATEGORYID, PAYPROCESSID
)
VALUES
(
    @RelievingSettlementId, @RelievingSettlementSlno, @RelievingSettlementOperationType,
    @RelievingId, @SettlementAmountTypeId, @RelievingSettlementAmount, @RelievingSettlementRemarks,
    @SettlementAmount, @SettlementCategoryId, @PayprocessId
);";

        public const string UPDATE_RELIEVINGSETTLEMENT = @"UPDATE TRELIEVINGSETTLEMENT
SET
    RELIEVINGSETTLEMENTSLNO          = @RelievingSettlementSlno,
    RELIEVINGSETTLEMENTOPERATIONTYPE = @RelievingSettlementOperationType,
    RELIEVINGID                      = @RelievingId,
    SETTLEMENTAMOUNTTYPEID           = @SettlementAmountTypeId,
    RELIEVINGSETTLEMENTAMOUNT        = @RelievingSettlementAmount,
    RELIEVINGSETTLEMENTREMARKS       = @RelievingSettlementRemarks,
    SETTLEMENTAMOUNT                 = @SettlementAmount,
    SETTLEMENTCATEGORYID             = @SettlementCategoryId,
    PAYPROCESSID                     = @PayprocessId
WHERE RELIEVINGSETTLEMENTID = @RelievingSettlementId;";

        // Parameterized delete, keyed by RelievingId — replaces GB4's raw string-concatenated
        // "DELETE FROM TRELIEVINGSETTLEMENT WHERE RELIEVINGID =" + id (SQL-injection-shaped).
        public const string DELETE_RELIEVINGSETTLEMENT_BY_RELIEVINGID = @"DELETE FROM TRELIEVINGSETTLEMENT
                                            WHERE RELIEVINGID = @relievingid;";

        // Feeds the settlement Add/Sub totals back onto the Relieving header
        // (GB4 parity: RelievingBLL.SaveRelievingSettlement "Update Releiving Table" block).
        public const string UPDATE_RELIEVING_SETTLEMENTAMOUNT = @"UPDATE TRELIEVING
SET SETTLEMENTAMOUNT = @settlementamount
WHERE RELIEVINGID = @relievingid;";

        public const string GET_SELECTLIST_RELIEVING = @"SELECT
    A.RELIEVINGID     AS Id,
    A.RELIEVINGNUMBER AS RelievingNumber,
    A.SEPARATIONDATE  AS SeperationDate,
    B.EMPLOYEECODE    AS Code,
    B.EMPLOYEENAME    AS Name,
    CASE A.SETTLEMENTTYPE WHEN 0 THEN 'Not Applicable' WHEN 1 THEN 'With Salary' ELSE 'Seperate Process' END AS SettlementType
FROM TRELIEVING A
JOIN MEMPLOYEE B ON A.EMPLOYEEID = B.EMPLOYEEID
WHERE 1 = 1";
    }
}