using System;

namespace PayRollDAL.Query.EmployeeTransfer
{
    // ─────────────────────────────────────────────────────────────────────
    //  EmployeeTransferQB – SQL for all EmployeeTransfer operations
    //  (TEMPLOYEETRANSFER / TEMPLOYEETRANSFERDETAIL)
    //
    //  GB4 parity: Unisoft.PayrollBLL.EmployeeTransfer.EmployeeTransferBLL
    //  (NHibernate hbm mappings: EmployeeTransfer.hbm.xml / EmployeeTransferDetail.hbm.xml).
    //
    //  Rules:
    //    • Const strings only — no SQL concatenation
    //    • All params are named (@Param) — no string interpolation
    //    • TEMPLOYEETRANSFERDETAIL.EMPLOYEETRANSFERDETAILID is application-assigned
    //      (GB4 NHibernate generator="assigned") — never IDENTITY
    //    • Detail update strategy: DELETE existing rows then re-INSERT
    // ─────────────────────────────────────────────────────────────────────

    public static class EmployeeTransferQB
    {
        // ── Get single header ────────────────────────────────────────────

        public const string GET_EMPLOYEETRANSFER = @"
SELECT
    ET.EMPLOYEETRANSFERID        AS EmployeeTransferId,
    ET.TRANSFERNUMBER            AS EmployeeTransferTransferNumber,
    ET.TRANSFERDATE              AS EmployeeTransferTransferDate,
    ET.EFFECTIVEFROM             AS EmployeeTransferEffectiveFrom,
    ET.REFERENCENUMBER           AS EmployeeTransferReferenceNumber,
    ET.REFERENCEDATE             AS EmployeeTransferReferenceDate,
    ET.REMARKS                   AS EmployeeTransferRemarks,
    ET.STATUS                    AS EmployeeTransferStatus,
    ET.VERSION                   AS EmployeeTransferVersion,
    ET.CREATEDBYID               AS EmployeeTransferCreatedById,
    ET.CREATEDON                 AS EmployeeTransferCreatedOn,
    CB.USERNAME                  AS EmployeeTransferCreatedByName,
    ET.MODIFIEDBYID              AS EmployeeTransferModifiedById,
    ET.MODIFIEDON                AS EmployeeTransferModifiedOn,
    MB.USERNAME                  AS EmployeeTransferModifiedByName,

    ET.BIZTRANSACTIONTYPEID      AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE   AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME   AS BizTransactionTypeName,
    BTT.ASSETPOSTTYPE            AS BizTransactionTypeAssetPostType,

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

    ET.TRANSFERTYPEID            AS TransferTypeId,
    TT.GCMCODE                   AS TransferTypeCode,
    TT.GCMNAME                   AS TransferTypeName,

    ET.PERIODID                  AS PeriodId,
    PE.PERIODCODE                AS PeriodCode,
    PE.PERIODNAME                AS PeriodName,
    PE.FROMDATE                  AS PeriodFromDate,
    PE.TODATE                    AS PeriodToDate

FROM TEMPLOYEETRANSFER ET
LEFT JOIN MUSER               CB  ON CB.USERID              = ET.CREATEDBYID
LEFT JOIN MUSER               MB  ON MB.USERID              = ET.MODIFIEDBYID
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = ET.BIZTRANSACTIONTYPEID
LEFT JOIN MORGANIZATIONUNIT   OU  ON OU.OUID                = ET.OUID
LEFT JOIN MGCM                TT  ON TT.GCMID               = ET.TRANSFERTYPEID
LEFT JOIN MPERIOD             PE  ON PE.PERIODID            = ET.PERIODID
WHERE ET.EMPLOYEETRANSFERID = @EmployeeTransferId;";

        // ── Get all detail rows for a header ─────────────────────────────

        public const string GET_EMPLOYEETRANSFERDETAIL_BY_HEADER = @"
SELECT
    ETD.EMPLOYEETRANSFERDETAILID AS EmployeeTransferDetailId,
    ETD.SLNO                     AS EmployeeTransferDetailSlNo,
    ETD.EMPLOYEETRANSFERID       AS EmployeeTransferId,
    ETD.LINEREMARKS              AS EmployeeTransferDetailLineRemarks,

    ETD.EMPLOYEEID                AS EmployeeId,
    EMP.EMPLOYEECODE              AS EmployeeCode,
    EMP.EMPLOYEENAME               AS EmployeeName,

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

    ETD.DEPARTMENTID               AS DepartmentId,
    DEPT.DEPARTMENTCODE            AS DepartmentCode,
    DEPT.DEPARTMENTNAME            AS DepartmentName,

    ETD.DESIGNATIONID              AS DesignationId,
    DESG.GCMCODE                   AS DesignationCode,
    DESG.GCMNAME                   AS DesignationName,

    ETD.LOCATIONID                 AS LocationId,
    LOC.LOCATIONCODE               AS LocationCode,
    LOC.LOCATIONNAME               AS LocationName,

    ETD.GRADEID                    AS GradeId,
    GRD.GCMCODE                    AS GradeCode,
    GRD.GCMNAME                    AS GradeName,

    ETD.REPORTINGTOID              AS ReportingToId,
    RPT.EMPLOYEECODE               AS ReportingToCode,
    RPT.EMPLOYEENAME               AS ReportingToName,

    ETD.RESOURCETYPEID             AS ResourceTypeId,
    RT.RESOURCETYPECODE            AS ResourceTypeCode,
    RT.RESOURCETYPENAME            AS ResourceTypeName,

    ETD.EMPLOYEETYPEID             AS EmployeeTypeId,
    EMT.GCMCODE                    AS EmployeeTypeCode,
    EMT.GCMNAME                    AS EmployeeTypeName,

    ETD.STAFFTYPEID                AS StaffTypeId,
    ST.GCMCODE                     AS StaffTypeCode,
    ST.GCMNAME                     AS StaffTypeName,

    ETD.PAYGROUPID                 AS PayGroupId,
    PG.PAYGROUPCODE                AS PayGroupCode,
    PG.PAYGROUPNAME                AS PayGroupName,

    ETD.WORKTYPEID                 AS WorkTypeId,
    WT.GCMCODE                     AS WorkTypeCode,
    WT.GCMNAME                     AS WorkTypeName,

    ETD.SEGMENTID                  AS SegmentId,
    SEG.GCMCODE                    AS SegmentCode,
    SEG.GCMNAME                    AS SegmentName,

    ETD.UNITID                     AS UnitId,
    UNT.GCMCODE                    AS UnitCode,
    UNT.GCMNAME                    AS UnitName

FROM TEMPLOYEETRANSFERDETAIL ETD
LEFT JOIN MEMPLOYEE     EMP  ON EMP.EMPLOYEEID     = ETD.EMPLOYEEID
LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID          = ETD.OUID
LEFT JOIN MDEPARTMENT   DEPT ON DEPT.DEPARTMENTID  = ETD.DEPARTMENTID
LEFT JOIN MGCM          DESG ON DESG.GCMID         = ETD.DESIGNATIONID
LEFT JOIN MLOCATION     LOC  ON LOC.LOCATIONID     = ETD.LOCATIONID
LEFT JOIN MGCM          GRD  ON GRD.GCMID          = ETD.GRADEID
LEFT JOIN MEMPLOYEE     RPT  ON RPT.EMPLOYEEID     = ETD.REPORTINGTOID
LEFT JOIN MRESOURCETYPE RT   ON RT.RESOURCETYPEID  = ETD.RESOURCETYPEID
LEFT JOIN MGCM          EMT  ON EMT.GCMID          = ETD.EMPLOYEETYPEID
LEFT JOIN MGCM          ST   ON ST.GCMID           = ETD.STAFFTYPEID
LEFT JOIN MPAYGROUP     PG   ON PG.PAYGROUPID      = ETD.PAYGROUPID
LEFT JOIN MGCM          WT   ON WT.GCMID           = ETD.WORKTYPEID
LEFT JOIN MGCM          SEG  ON SEG.GCMID          = ETD.SEGMENTID
LEFT JOIN MGCM          UNT  ON UNT.GCMID          = ETD.UNITID
WHERE ETD.EMPLOYEETRANSFERID = @EmployeeTransferId
ORDER BY ETD.SLNO;";

        // ── Insert header ─────────────────────────────────────────────────

        public const string SAVE_EMPLOYEETRANSFER = @"
INSERT INTO TEMPLOYEETRANSFER
(
    EMPLOYEETRANSFERID,
    TRANSFERNUMBER,
    TRANSFERDATE,
    EFFECTIVEFROM,
    REFERENCENUMBER,
    REFERENCEDATE,
    REMARKS,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    BIZTRANSACTIONTYPEID,
    OUID,
    TRANSFERTYPEID,
    PERIODID
)
VALUES
(
    @EmployeeTransferId,
    @EmployeeTransferTransferNumber,
    @EmployeeTransferTransferDate,
    @EmployeeTransferEffectiveFrom,
    @EmployeeTransferReferenceNumber,
    @EmployeeTransferReferenceDate,
    @EmployeeTransferRemarks,
    @EmployeeTransferStatus,
    @EmployeeTransferVersion,
    @EmployeeTransferCreatedById,
    @EmployeeTransferCreatedOn,
    @EmployeeTransferModifiedById,
    @EmployeeTransferModifiedOn,
    @BizTransactionTypeId,
    @OUId,
    @TransferTypeId,
    @PeriodId
);";

        // ── Update header ─────────────────────────────────────────────────

        public const string UPDATE_EMPLOYEETRANSFER = @"
UPDATE TEMPLOYEETRANSFER
SET
    TRANSFERNUMBER      = @EmployeeTransferTransferNumber,
    TRANSFERDATE        = @EmployeeTransferTransferDate,
    EFFECTIVEFROM       = @EmployeeTransferEffectiveFrom,
    REFERENCENUMBER     = @EmployeeTransferReferenceNumber,
    REFERENCEDATE       = @EmployeeTransferReferenceDate,
    REMARKS             = @EmployeeTransferRemarks,
    STATUS              = @EmployeeTransferStatus,
    VERSION             = @EmployeeTransferVersion,
    MODIFIEDBYID        = @EmployeeTransferModifiedById,
    MODIFIEDON          = @EmployeeTransferModifiedOn,
    BIZTRANSACTIONTYPEID = @BizTransactionTypeId,
    OUID                = @OUId,
    TRANSFERTYPEID      = @TransferTypeId,
    PERIODID            = @PeriodId
WHERE EMPLOYEETRANSFERID = @EmployeeTransferId;";

        // ── Insert one detail row (EMPLOYEETRANSFERDETAILID is application-assigned) ──

        public const string SAVE_EMPLOYEETRANSFERDETAIL = @"
INSERT INTO TEMPLOYEETRANSFERDETAIL
(
    EMPLOYEETRANSFERDETAILID,
    EMPLOYEETRANSFERID,
    SLNO,
    LINEREMARKS,
    EMPLOYEEID,
    OUID,
    DEPARTMENTID,
    DESIGNATIONID,
    LOCATIONID,
    GRADEID,
    REPORTINGTOID,
    RESOURCETYPEID,
    EMPLOYEETYPEID,
    STAFFTYPEID,
    PAYGROUPID,
    WORKTYPEID,
    SEGMENTID,
    UNITID
)
VALUES
(
    @EmployeeTransferDetailId,
    @EmployeeTransferId,
    @EmployeeTransferDetailSlNo,
    @EmployeeTransferDetailLineRemarks,
    @EmployeeId,
    @OUId,
    @DepartmentId,
    @DesignationId,
    @LocationId,
    @GradeId,
    @ReportingToId,
    @ResourceTypeId,
    @EmployeeTypeId,
    @StaffTypeId,
    @PayGroupId,
    @WorkTypeId,
    @SegmentId,
    @UnitId
);";

        // ── Delete all detail rows for a header (used before re-insert on update, and on delete) ──

        public const string DELETE_EMPLOYEETRANSFERDETAIL_BY_HEADER = @"
DELETE FROM TEMPLOYEETRANSFERDETAIL
WHERE EMPLOYEETRANSFERID = @EmployeeTransferId;";

        // ── Delete header ─────────────────────────────────────────────────

        public const string DELETE_EMPLOYEETRANSFER = @"
DELETE FROM TEMPLOYEETRANSFER
WHERE EMPLOYEETRANSFERID = @EmployeeTransferId;";

        // ── Picklist (Id + TransferNumber only) ──────────────────────────

        public const string GET_SELECTLIST_EMPLOYEETRANSFER = @"
WITH EmployeeTransferSelectList AS
(
    SELECT
        ET.EMPLOYEETRANSFERID AS Id,
        ET.TRANSFERNUMBER     AS TransferNumber,
        ROW_NUMBER() OVER (ORDER BY ET.EMPLOYEETRANSFERID) AS RowNum
    FROM TEMPLOYEETRANSFER ET
)
SELECT Id, TransferNumber
FROM EmployeeTransferSelectList
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_SELECTLIST_EMPLOYEETRANSFER_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM TEMPLOYEETRANSFER ET;";

        // ── Paged header-only list ────────────────────────────────────────
        // GB4 parity note: GB4 has no true paged list for EmployeeTransfer — only the
        // Id/TransferNumber picklist above, plus a complex multi-temp-table report query
        // (EmployeeQueryBuilder.GET_EMPLOYEE_TRANSFER_REPORT_TEMP1/TEMP2/FINAL/COUNT) tied
        // to a report engine not present in GB4. This is a simple ROW_NUMBER()-paginated
        // full header row list (no detail rows), matching the shape of
        // AdditionDeductionQB.GET_ADDITIONDEDUCTION_LIST.

        public const string GET_EMPLOYEETRANSFER_LIST = @"
WITH EmployeeTransferList AS
(
    SELECT
        ET.EMPLOYEETRANSFERID        AS EmployeeTransferId,
        ET.TRANSFERNUMBER            AS EmployeeTransferTransferNumber,
        ET.TRANSFERDATE              AS EmployeeTransferTransferDate,
        ET.EFFECTIVEFROM             AS EmployeeTransferEffectiveFrom,
        ET.REFERENCENUMBER           AS EmployeeTransferReferenceNumber,
        ET.REFERENCEDATE             AS EmployeeTransferReferenceDate,
        ET.REMARKS                   AS EmployeeTransferRemarks,
        ET.STATUS                    AS EmployeeTransferStatus,
        ET.VERSION                   AS EmployeeTransferVersion,
        ET.CREATEDBYID               AS EmployeeTransferCreatedById,
        ET.CREATEDON                 AS EmployeeTransferCreatedOn,
        CB.USERNAME                  AS EmployeeTransferCreatedByName,
        ET.MODIFIEDBYID              AS EmployeeTransferModifiedById,
        ET.MODIFIEDON                AS EmployeeTransferModifiedOn,
        MB.USERNAME                  AS EmployeeTransferModifiedByName,

        ET.BIZTRANSACTIONTYPEID      AS BizTransactionTypeId,
        BTT.BIZTRANSACTIONTYPECODE   AS BizTransactionTypeCode,
        BTT.BIZTRANSACTIONTYPENAME   AS BizTransactionTypeName,
        BTT.ASSETPOSTTYPE            AS BizTransactionTypeAssetPostType,

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

        ET.TRANSFERTYPEID            AS TransferTypeId,
        TT.GCMCODE                   AS TransferTypeCode,
        TT.GCMNAME                   AS TransferTypeName,

        ET.PERIODID                  AS PeriodId,
        PE.PERIODCODE                AS PeriodCode,
        PE.PERIODNAME                AS PeriodName,
        PE.FROMDATE                  AS PeriodFromDate,
        PE.TODATE                    AS PeriodToDate,

        ROW_NUMBER() OVER (ORDER BY ET.EMPLOYEETRANSFERID DESC) AS RowNum
    FROM TEMPLOYEETRANSFER ET
    LEFT JOIN MUSER               CB  ON CB.USERID              = ET.CREATEDBYID
    LEFT JOIN MUSER               MB  ON MB.USERID              = ET.MODIFIEDBYID
    LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = ET.BIZTRANSACTIONTYPEID
    LEFT JOIN MORGANIZATIONUNIT   OU  ON OU.OUID                = ET.OUID
    LEFT JOIN MGCM                TT  ON TT.GCMID               = ET.TRANSFERTYPEID
    LEFT JOIN MPERIOD             PE  ON PE.PERIODID            = ET.PERIODID
)
SELECT *
FROM EmployeeTransferList
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_EMPLOYEETRANSFER_LIST_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM TEMPLOYEETRANSFER ET;";
    }
}