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

namespace PayRollDAL.Query.Village
{
    // Indexes recommended:
    //   MVILLAGE: IX_MVILLAGE_TENANTID_STATUS (TENANTID, STATUS)
    public static class VillageQB
    {
        // ── Picklist (paged, dual dialect) ──────────────────────────────────
        // GB4 parity: VillageBLL.GetSelectListVillage →
        // GetGenericCriteriaLoadFields<Poco.Village>("Id,Code,Name", FirstNumber, MaxResult, LoginDTO, CriteriaDTO)

        public const string GET_SELECTLIST_VILLAGE_SQL = @"
            WITH Paged AS (
                SELECT V.VILLAGEID   AS Id,
                       V.VILLAGECODE AS Code,
                       V.VILLAGENAME AS Name,
                       ROW_NUMBER() OVER (ORDER BY V.VILLAGENAME) AS RowNum
                FROM   MVILLAGE V
                WHERE  1 = 1
                AND    (@searchtext IS NULL OR V.VILLAGECODE LIKE @searchtext OR V.VILLAGENAME LIKE @searchtext)
            )
            SELECT Id, Code, Name
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_VILLAGE_PG = @"
            WITH Paged AS (
                SELECT V.""VILLAGEID""   AS ""Id"",
                       V.""VILLAGECODE"" AS ""Code"",
                       V.""VILLAGENAME"" AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY V.""VILLAGENAME"") AS ""RowNum""
                FROM   ""MVILLAGE"" V
                WHERE  1 = 1
                AND    (:searchtext IS NULL OR V.""VILLAGECODE"" LIKE :searchtext OR V.""VILLAGENAME"" LIKE :searchtext)
            )
            SELECT ""Id"", ""Code"", ""Name""
            FROM   Paged
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        // ── Count — real COUNT(*), not GB4's hardcoded "0" ───────────────────

        public const string GET_SELECTLIST_VILLAGE_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   MVILLAGE V
            WHERE  1 = 1
            AND    (@searchtext IS NULL OR V.VILLAGECODE LIKE @searchtext OR V.VILLAGENAME LIKE @searchtext);";

        public const string GET_SELECTLIST_VILLAGE_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   ""MVILLAGE"" V
            WHERE  1 = 1
            AND    (:searchtext IS NULL OR V.""VILLAGECODE"" LIKE :searchtext OR V.""VILLAGENAME"" LIKE :searchtext);";

        // ── Get single record (with Vehicle/Transporter join) ──────────────

        public const string GET_VILLAGE_SQL = @"
            SELECT
                V.VILLAGEID         AS VillageId,
                V.VILLAGECODE       AS VillageCode,
                V.VILLAGENAME       AS VillageName,
                V.DISTANCE          AS VillageDistance,
                V.EMPLOYERAMOUNT    AS VillageEmployerAmount,
                V.EMPLOYEEAMOUNT    AS VillageEmployeeAmount,
                V.VEHICLEID         AS VehicleId,
                VE.VEHICLENUMBER    AS VehicleNumber,
                VE.TRANSPORTERID    AS TransporterId,
                P.PARTYCODE         AS TransporterCode,
                P.PARTYNAME         AS TransporterName,
                V.SORTORDER         AS VillageSortOrder,
                V.STATUS            AS VillageStatus,
                V.VERSION           AS VillageVersion,
                V.CREATEDBYID       AS VillageCreatedById,
                V.CREATEDON         AS VillageCreatedOn,
                V.MODIFIEDBYID      AS VillageModifiedById,
                V.MODIFIEDON        AS VillageModifiedOn
            FROM   MVILLAGE V
            LEFT JOIN MVEHICLE VE ON VE.VEHICLEID = V.VEHICLEID
            LEFT JOIN MPARTY   P  ON P.PARTYID    = VE.TRANSPORTERID
            WHERE  V.VILLAGEID = @villageid;";

        public const string GET_VILLAGE_PG = @"
            SELECT
                V.""VILLAGEID""         AS ""VillageId"",
                V.""VILLAGECODE""       AS ""VillageCode"",
                V.""VILLAGENAME""       AS ""VillageName"",
                V.""DISTANCE""          AS ""VillageDistance"",
                V.""EMPLOYERAMOUNT""    AS ""VillageEmployerAmount"",
                V.""EMPLOYEEAMOUNT""    AS ""VillageEmployeeAmount"",
                V.""VEHICLEID""         AS ""VehicleId"",
                VE.""VEHICLENUMBER""    AS ""VehicleNumber"",
                VE.""TRANSPORTERID""    AS ""TransporterId"",
                P.""PARTYCODE""         AS ""TransporterCode"",
                P.""PARTYNAME""         AS ""TransporterName"",
                V.""SORTORDER""         AS ""VillageSortOrder"",
                V.""STATUS""            AS ""VillageStatus"",
                V.""VERSION""           AS ""VillageVersion"",
                V.""CREATEDBYID""       AS ""VillageCreatedById"",
                V.""CREATEDON""         AS ""VillageCreatedOn"",
                V.""MODIFIEDBYID""      AS ""VillageModifiedById"",
                V.""MODIFIEDON""        AS ""VillageModifiedOn""
            FROM   ""MVILLAGE"" V
            LEFT JOIN ""MVEHICLE"" VE ON VE.""VEHICLEID"" = V.""VEHICLEID""
            LEFT JOIN ""MPARTY""   P  ON P.""PARTYID""    = VE.""TRANSPORTERID""
            WHERE  V.""VILLAGEID"" = :villageid;";

        // ── Save (insert) ────────────────────────────────────────────────

        public const string SAVE_VILLAGE_SQL = @"INSERT INTO MVILLAGE
(
    VILLAGEID, VILLAGECODE, VILLAGENAME, DISTANCE, EMPLOYERAMOUNT, EMPLOYEEAMOUNT,
    VEHICLEID, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @VillageId, @VillageCode, @VillageName, @VillageDistance, @VillageEmployerAmount, @VillageEmployeeAmount,
    @VehicleId, @VillageSortOrder, @VillageStatus, 1, @VillageCreatedById, @VillageCreatedOn, @VillageModifiedById, @VillageModifiedOn
);";

        public const string SAVE_VILLAGE_PG = @"INSERT INTO ""MVILLAGE""
(
    ""VILLAGEID"", ""VILLAGECODE"", ""VILLAGENAME"", ""DISTANCE"", ""EMPLOYERAMOUNT"", ""EMPLOYEEAMOUNT"",
    ""VEHICLEID"", ""SORTORDER"", ""STATUS"", ""VERSION"", ""CREATEDBYID"", ""CREATEDON"", ""MODIFIEDBYID"", ""MODIFIEDON""
)
VALUES
(
    :VillageId, :VillageCode, :VillageName, :VillageDistance, :VillageEmployerAmount, :VillageEmployeeAmount,
    :VehicleId, :VillageSortOrder, :VillageStatus, 1, :VillageCreatedById, :VillageCreatedOn, :VillageModifiedById, :VillageModifiedOn
);";

        // ── Update ───────────────────────────────────────────────────────

        public const string UPDATE_VILLAGE_SQL = @"UPDATE MVILLAGE
SET
    VILLAGECODE      = @VillageCode,
    VILLAGENAME      = @VillageName,
    DISTANCE         = @VillageDistance,
    EMPLOYERAMOUNT   = @VillageEmployerAmount,
    EMPLOYEEAMOUNT   = @VillageEmployeeAmount,
    VEHICLEID        = @VehicleId,
    SORTORDER        = @VillageSortOrder,
    STATUS           = @VillageStatus,
    VERSION          = VERSION + 1,
    MODIFIEDBYID     = @VillageModifiedById,
    MODIFIEDON       = @VillageModifiedOn
WHERE VILLAGEID = @VillageId;";

        public const string UPDATE_VILLAGE_PG = @"UPDATE ""MVILLAGE""
SET
    ""VILLAGECODE""      = :VillageCode,
    ""VILLAGENAME""      = :VillageName,
    ""DISTANCE""         = :VillageDistance,
    ""EMPLOYERAMOUNT""   = :VillageEmployerAmount,
    ""EMPLOYEEAMOUNT""   = :VillageEmployeeAmount,
    ""VEHICLEID""        = :VehicleId,
    ""SORTORDER""        = :VillageSortOrder,
    ""STATUS""           = :VillageStatus,
    ""VERSION""          = ""VERSION"" + 1,
    ""MODIFIEDBYID""     = :VillageModifiedById,
    ""MODIFIEDON""       = :VillageModifiedOn
WHERE ""VILLAGEID"" = :VillageId;";

        // ── Delete ───────────────────────────────────────────────────────

        public const string DELETE_VILLAGE_SQL = @"DELETE FROM MVILLAGE WHERE VILLAGEID = @villageid;";

        public const string DELETE_VILLAGE_PG = @"DELETE FROM ""MVILLAGE"" WHERE ""VILLAGEID"" = :villageid;";
    }
}