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

namespace PayRollDAL.Query.Policy
{
    public static class PolicyQB
    {
        public const string GET_POLICY = @"SELECT
    A.POLICYID AS PolicyId,
    A.POLICYCODE AS PolicyCode,
    A.POLICYNAME AS PolicyName,
    A.POLICYDESCRIPTION AS PolicyDescription,
    A.POLICYPAGEID AS PolicyPageId,
    A.CURRENCYID AS CurrencyId,
    C.CURRENCYCODE AS CurrencyCode,
    C.CURRENCYNAME AS CurrencyName
FROM MPOLICY A
LEFT JOIN MCURRENCY C ON A.CURRENCYID = C.CURRENCYID
WHERE A.POLICYID = @policyid";

        public const string GET_POLICYVERSION_BY_POLICYID = @"SELECT
    V.POLICYVERSIONID AS PolicyVersionId,
    V.POLICYID AS PolicyId,
    V.VERSION AS PolicyVersionVersion,
    V.FROMDATE AS PolicyVersionFromDate,
    V.TODATE AS PolicyVersionToDate,
    V.REMARKS AS PolicyVersionRemarks
FROM MPOLICYVERSION V
WHERE V.POLICYID = @policyid
ORDER BY V.VERSION";

        public const string GET_POLICYVERSION_BY_ID = @"SELECT
    V.POLICYVERSIONID AS PolicyVersionId,
    V.POLICYID AS PolicyId,
    P.POLICYCODE AS PolicyCode,
    P.POLICYNAME AS PolicyName,
    V.VERSION AS PolicyVersionVersion,
    V.FROMDATE AS PolicyVersionFromDate,
    V.TODATE AS PolicyVersionToDate,
    V.REMARKS AS PolicyVersionRemarks
FROM MPOLICYVERSION V
INNER JOIN MPOLICY P ON V.POLICYID = P.POLICYID
WHERE V.POLICYVERSIONID = @policyversionid";

        public const string GET_POLICYVERSION_BY_FROMDATE = @"SELECT TOP 1
    V.POLICYVERSIONID AS PolicyVersionId,
    V.POLICYID AS PolicyId,
    P.POLICYCODE AS PolicyCode,
    P.POLICYNAME AS PolicyName,
    V.VERSION AS PolicyVersionVersion,
    V.FROMDATE AS PolicyVersionFromDate,
    V.TODATE AS PolicyVersionToDate,
    V.REMARKS AS PolicyVersionRemarks
FROM MPOLICYVERSION V
INNER JOIN MPOLICY P ON V.POLICYID = P.POLICYID
WHERE V.POLICYVERSIONID = @policyversionid
AND V.FROMDATE <= @fromdate
ORDER BY V.FROMDATE DESC";

        public const string GET_POLICYVERSIONDETAIL = @"SELECT
    D.POLICYVERSIONDETAILID AS PolicyVersionDetailId,
    D.POLICYVERSIONID AS PolicyVersionParentId,
    D.SLNO AS PolicyVersionDetailSlNo,

    D.HEADID AS HeadId,
    H.HEADCODE AS HeadCode,
    H.HEADNAME AS HeadName,

    D.CITYCLASSID AS CityClassId,
    CC.CITYCLASSCODE AS CityClassCode,
    CC.CITYCLASSNAME AS CityClassName,

    D.SALARYGRADEID AS SalaryGradeId,
    SG.SALARYGRADECODE AS SalaryGradeCode,
    SG.SALARYGRADENAME AS SalaryGradeName,

    D.MODEID AS ModeId,
    M.MODECODE AS ModeCode,
    M.MODENAME AS ModeName,

    D.VEHICLETYPEID AS VehicleTypeId,
    VT.VEHICLETYPECODE AS VehicleTypeCode,
    VT.VEHICLETYPENAME AS VehicleTypeName,

    D.AMOUNT AS PolicyVersionDetailAmount,
    D.REMARKS AS PolicyVersionDetailRemarks

FROM MPOLICYVERSIONDETAIL D
LEFT JOIN MHEAD H ON D.HEADID = H.HEADID
LEFT JOIN MCITYCLASS CC ON D.CITYCLASSID = CC.CITYCLASSID
LEFT JOIN MSALARYGRADE SG ON D.SALARYGRADEID = SG.SALARYGRADEID
LEFT JOIN MMODE M ON D.MODEID = M.MODEID
LEFT JOIN MVEHICLETYPE VT ON D.VEHICLETYPEID = VT.VEHICLETYPEID
WHERE D.POLICYVERSIONID = @policyversionid
ORDER BY D.SLNO";

        public const string SAVE_POLICY = @"INSERT INTO MPOLICY
(
    POLICYID, POLICYCODE, POLICYNAME, POLICYDESCRIPTION, POLICYPAGEID, CURRENCYID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @PolicyId, @PolicyCode, @PolicyName, @PolicyDescription, @PolicyPageId, @CurrencyId,
    @PolicyCreatedById, @PolicyCreatedOn, @PolicyModifiedById, @PolicyModifiedOn
);";

        public const string UPDATE_POLICY = @"UPDATE MPOLICY
SET
    POLICYCODE          = @PolicyCode,
    POLICYNAME           = @PolicyName,
    POLICYDESCRIPTION    = @PolicyDescription,
    POLICYPAGEID         = @PolicyPageId,
    CURRENCYID           = @CurrencyId,
    MODIFIEDBYID         = @PolicyModifiedById,
    MODIFIEDON           = @PolicyModifiedOn
WHERE POLICYID = @PolicyId;";

        public const string DELETE_POLICY = @"DELETE FROM MPOLICY
                                            WHERE POLICYID = @policyid;";

        public const string SAVE_POLICYVERSION = @"INSERT INTO MPOLICYVERSION
(
    POLICYVERSIONID, POLICYID, VERSION, FROMDATE, TODATE, REMARKS
)
VALUES
(
    @PolicyVersionId, @PolicyId, @PolicyVersionVersion, @PolicyVersionFromDate, @PolicyVersionToDate, @PolicyVersionRemarks
);";

        public const string DELETE_POLICYVERSION_BY_POLICYID = @"DELETE FROM MPOLICYVERSION
                                            WHERE POLICYID = @policyid;";

        public const string SAVE_POLICYVERSIONDETAIL = @"INSERT INTO MPOLICYVERSIONDETAIL
(
    POLICYVERSIONDETAILID, POLICYVERSIONID, SLNO, HEADID, CITYCLASSID, SALARYGRADEID, MODEID, VEHICLETYPEID, AMOUNT, REMARKS
)
VALUES
(
    @PolicyVersionDetailId, @PolicyVersionParentId, @PolicyVersionDetailSlNo, @HeadId, @CityClassId, @SalaryGradeId, @ModeId, @VehicleTypeId, @PolicyVersionDetailAmount, @PolicyVersionDetailRemarks
);";

        public const string DELETE_POLICYVERSIONDETAIL_BY_VERSIONID = @"DELETE FROM MPOLICYVERSIONDETAIL
                                            WHERE POLICYVERSIONID = @policyversionid;";

        public const string GET_SELECTLIST_POLICY = @"SELECT
    A.POLICYID   AS Id,
    A.POLICYCODE AS Code,
    A.POLICYNAME AS Name
FROM MPOLICY A
WHERE 1 = 1";

        public const string GET_SELECTLIST_POLICYVERSION = @"SELECT
    V.POLICYVERSIONID AS Id,
    V.VERSION          AS Version,
    V.FROMDATE          AS FromDate,
    V.TODATE            AS ToDate
FROM MPOLICYVERSION V
WHERE V.POLICYID = @policyid";

        // ── Policy Version Picklist Report (cross-policy, paged, dual-dialect) ─
        // Indexes recommended:
        //   MPOLICYVERSION: IX_MPOLICYVERSION_POLICYID (POLICYID)

        public const string GET_POLICYVERSION_PICKLIST_REPORT_SQL = @"
WITH Paged AS (
    SELECT a.POLICYID AS Id,
           b.VERSION   AS Version,
           b.FROMDATE  AS FromDate,
           b.TODATE    AS ToDate,
           ROW_NUMBER() OVER (ORDER BY a.POLICYID, b.VERSION) AS RowNum
    FROM   MPOLICY a
    INNER JOIN MPOLICYVERSION b ON a.POLICYID = b.POLICYID
    WHERE  1 = 1 /*FILTER*/
)
SELECT Id, Version, FromDate, ToDate
FROM   Paged
WHERE  (@firstnumber = -1 AND @maxresult = -1)
   OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_POLICYVERSION_PICKLIST_REPORT_PG = @"
WITH Paged AS (
    SELECT a.POLICYID AS ""Id"",
           b.VERSION   AS ""Version"",
           b.FROMDATE  AS ""FromDate"",
           b.TODATE    AS ""ToDate"",
           ROW_NUMBER() OVER (ORDER BY a.POLICYID, b.VERSION) AS ""RowNum""
    FROM   MPOLICY a
    INNER JOIN MPOLICYVERSION b ON a.POLICYID = b.POLICYID
    WHERE  1 = 1 /*FILTER*/
)
SELECT ""Id"", ""Version"", ""FromDate"", ""ToDate""
FROM   Paged
WHERE  (:firstnumber = -1 AND :maxresult = -1)
   OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_POLICYVERSION_PICKLIST_REPORT_COUNT_SQL = @"
SELECT COUNT(*)
FROM   MPOLICY a
INNER JOIN MPOLICYVERSION b ON a.POLICYID = b.POLICYID
WHERE  1 = 1 /*FILTER*/;";

        public const string GET_POLICYVERSION_PICKLIST_REPORT_COUNT_PG = @"
SELECT COUNT(*)
FROM   MPOLICY a
INNER JOIN MPOLICYVERSION b ON a.POLICYID = b.POLICYID
WHERE  1 = 1 /*FILTER*/;";
    }
}