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

namespace AccountsDAL.Query.Insurance
{
    public class InsuranceQB
    {
        public const string GET_INSURANCE = @"
                SELECT
                    MI.INSURANCEID           AS InsuranceId,
                    MI.OUTYPE                AS InsuranceOUType,

                    MI.OUGROUPID              AS OUGroupId,
                    OG.ORGANIZATIONGROUPCODE AS OUGroupCode,
                    OG.ORGANIZATIONGROUPNAME AS OUGroupName,

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

                    MI.INSURANCETYPE         AS InsuranceInsuranceType,
                    MI.POLICYNUMBER          AS InsurancePolicyNumber,

                    MI.VEHICLEID             AS VehicleId,
                    V.VEHICLENUMBER          AS VehicleNumber,

                    MI.PARTYID               AS PartyId,
                    P.PARTYCODE              AS PartyCode,
                    P.PARTYNAME              AS PartyName,

                    MI.STARTDATE             AS InsuranceStartDate,
                    MI.ENDDATE               AS InsuranceEndDate,
                    MI.COVERAGEAMOUNT        AS InsuranceCoverageAmount,
                    MI.PREMIUMAMOUNT         AS InsurancePremiumAmount,
                    MI.PERIODTYPE            AS InsurancePeriodType,
                    MI.LASTPAIDON            AS InsuranceLastPaidOn,
                    MI.LASTPAIDTILL          AS InsuranceLastPaidTill,
                    MI.PREMIUMDUEON          AS InsurancePremiumDueOn,

                    MI.INSURANCESTATUS       AS InsuranceInsuranceOStatus,
                    MI.REMARKS               AS InsuranceRemarks,

                    MI.SORTORDER             AS InsuranceSortOrder,
                    MI.STATUS                AS InsuranceStatus,
                    MI.VERSION               AS InsuranceVersion,

                    MI.CREATEDBYID           AS InsuranceCreatedById,
                    U1.USERNAME              AS InsuranceCreatedByName,
                    MI.CREATEDON             AS InsuranceCreatedOn,

                    MI.MODIFIEDBYID          AS InsuranceModifiedById,
                    U2.USERNAME              AS InsuranceModifiedByName,
                    MI.MODIFIEDON            AS InsuranceModifiedOn,

                    MI.ASSETID              AS AssetId,
                    A.ASSETCODE             AS AssetCode,
                    A.ASSETNAME             AS AssetName,

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

                    MI.MACHINEID            AS MachineId,
                    M.MACHINECODE           AS MachineCode,
                    M.MACHINENAME           AS MachineName

                FROM MINSURANCE MI

                LEFT JOIN MORGANIZATIONGROUP OG
                       ON MI.OUGROUPID = OG.ORGANIZATIONGROUPID

                LEFT JOIN MORGANIZATIONUNIT OU
                       ON MI.OUID = OU.OUID

                LEFT JOIN MVEHICLE V
                       ON MI.VEHICLEID = V.VEHICLEID

                LEFT JOIN MPARTY P
                       ON MI.PARTYID = P.PARTYID

                LEFT JOIN MUSER U1
                       ON MI.CREATEDBYID = U1.USERID

                LEFT JOIN MUSER U2
                       ON MI.MODIFIEDBYID = U2.USERID

                LEFT JOIN MASSET A
                       ON MI.ASSETID = A.ASSETID

                LEFT JOIN MEMPLOYEE E
                       ON MI.EMPLOYEEID = E.EMPLOYEEID

                LEFT JOIN MMACHINE M
                       ON MI.MACHINEID = M.MACHINEID

                WHERE MI.INSURANCEID = @InsuranceId";

        // Full-DTO insurance list (InsuranceDTO, not InsurancePicklistDTO) — mirrors GET_INSURANCE's
        // joins exactly, but filtered by CriteriaDTO (search text against POLICYNUMBER + optional
        // Status Equal/NotEqual) instead of a single InsuranceId, with no FirstNumber/MaxResult
        // paging and no dynamic OrderBy — same shape as AccountQB.GET_ACCOUNT_LIST.
        public const string GET_INSURANCE_LIST = @"
                SELECT
                    MI.INSURANCEID           AS InsuranceId,
                    MI.OUTYPE                AS InsuranceOUType,

                    MI.OUGROUPID              AS OUGroupId,
                    OG.ORGANIZATIONGROUPCODE AS OUGroupCode,
                    OG.ORGANIZATIONGROUPNAME AS OUGroupName,

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

                    MI.INSURANCETYPE         AS InsuranceInsuranceType,
                    MI.POLICYNUMBER          AS InsurancePolicyNumber,

                    MI.VEHICLEID             AS VehicleId,
                    V.VEHICLENUMBER          AS VehicleNumber,

                    MI.PARTYID               AS PartyId,
                    P.PARTYCODE              AS PartyCode,
                    P.PARTYNAME              AS PartyName,

                    MI.STARTDATE             AS InsuranceStartDate,
                    MI.ENDDATE               AS InsuranceEndDate,
                    MI.COVERAGEAMOUNT        AS InsuranceCoverageAmount,
                    MI.PREMIUMAMOUNT         AS InsurancePremiumAmount,
                    MI.PERIODTYPE            AS InsurancePeriodType,
                    MI.LASTPAIDON            AS InsuranceLastPaidOn,
                    MI.LASTPAIDTILL          AS InsuranceLastPaidTill,
                    MI.PREMIUMDUEON          AS InsurancePremiumDueOn,

                    MI.INSURANCESTATUS       AS InsuranceInsuranceOStatus,
                    MI.REMARKS               AS InsuranceRemarks,

                    MI.SORTORDER             AS InsuranceSortOrder,
                    MI.STATUS                AS InsuranceStatus,
                    MI.VERSION               AS InsuranceVersion,

                    MI.CREATEDBYID           AS InsuranceCreatedById,
                    U1.USERNAME              AS InsuranceCreatedByName,
                    MI.CREATEDON             AS InsuranceCreatedOn,

                    MI.MODIFIEDBYID          AS InsuranceModifiedById,
                    U2.USERNAME              AS InsuranceModifiedByName,
                    MI.MODIFIEDON            AS InsuranceModifiedOn,

                    MI.ASSETID              AS AssetId,
                    A.ASSETCODE             AS AssetCode,
                    A.ASSETNAME             AS AssetName,

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

                    MI.MACHINEID            AS MachineId,
                    M.MACHINECODE           AS MachineCode,
                    M.MACHINENAME           AS MachineName

                FROM MINSURANCE MI

                LEFT JOIN MORGANIZATIONGROUP OG
                       ON MI.OUGROUPID = OG.ORGANIZATIONGROUPID

                LEFT JOIN MORGANIZATIONUNIT OU
                       ON MI.OUID = OU.OUID

                LEFT JOIN MVEHICLE V
                       ON MI.VEHICLEID = V.VEHICLEID

                LEFT JOIN MPARTY P
                       ON MI.PARTYID = P.PARTYID

                LEFT JOIN MUSER U1
                       ON MI.CREATEDBYID = U1.USERID

                LEFT JOIN MUSER U2
                       ON MI.MODIFIEDBYID = U2.USERID

                LEFT JOIN MASSET A
                       ON MI.ASSETID = A.ASSETID

                LEFT JOIN MEMPLOYEE E
                       ON MI.EMPLOYEEID = E.EMPLOYEEID

                LEFT JOIN MMACHINE M
                       ON MI.MACHINEID = M.MACHINEID

                WHERE (@SearchText IS NULL OR @SearchText = ''
                       OR MI.POLICYNUMBER LIKE '%' + @SearchText + '%')
                  AND (@StatusEquals IS NULL OR MI.STATUS = @StatusEquals)
                  AND (@StatusNotEquals IS NULL OR MI.STATUS <> @StatusNotEquals)
                ORDER BY MI.POLICYNUMBER;";

        public const string SAVE_INSURANCE = @"
                  INSERT INTO MINSURANCE
                        (
                            INSURANCEID,
                            OUTYPE,
                            OUGROUPID,
                            OUID,
                            INSURANCETYPE,
                            POLICYNUMBER,
                            VEHICLEID,
                            PARTYID,
                            STARTDATE,
                            ENDDATE,
                            COVERAGEAMOUNT,
                            PREMIUMAMOUNT,
                            PERIODTYPE,
                            LASTPAIDON,
                            LASTPAIDTILL,
                            PREMIUMDUEON,
                      
                            REMARKS,
                            SORTORDER,
                            INSURANCESTATUS,
                            VERSION,
                            CREATEDBYID,
                            CREATEDON,
                            MODIFIEDBYID,
                            MODIFIEDON,
                            
                           
                            ASSETID,
                            EMPLOYEEID,
                            MACHINEID
                        )
                        VALUES
                        (
                            @InsuranceId,
                            @InsuranceOUType,
                            @OUGroupId,
                            @OUId,
                            @InsuranceInsuranceType,
                            @InsurancePolicyNumber,
                            @VehicleId,
                            @PartyId,
                            @InsuranceStartDate,
                            @InsuranceEndDate,
                            @InsuranceCoverageAmount,
                            @InsurancePremiumAmount,
                            @InsurancePeriodType,
                            @InsuranceLastPaidOn,
                            @InsuranceLastPaidTill,
                            @InsurancePremiumDueOn,
                           
                            @InsuranceRemarks,
                            @InsuranceSortOrder,
                            @InsuranceInsuranceOStatus,
                            @InsuranceVersion,
                            @InsuranceCreatedById,
                            @InsuranceCreatedOn,
                            @InsuranceModifiedById,
                            @InsuranceModifiedOn,
                           
                            
                            @AssetId,
                            @EmployeeId,
                            @MachineId
                        );";
        public const string UPDATE_INSURANCE = @"
                    UPDATE MINSURANCE
                    SET
                        OUTYPE             = @InsuranceOUType,
                        OUGROUPID          = @OUGroupId,
                        OUID               = @OUId,
                        INSURANCETYPE      = @InsuranceInsuranceType,
                        POLICYNUMBER       = @InsurancePolicyNumber,
                        VEHICLEID          = @VehicleId,
                        PARTYID            = @PartyId,
                        STARTDATE          = @InsuranceStartDate,
                        ENDDATE            = @InsuranceEndDate,
                        COVERAGEAMOUNT     = @InsuranceCoverageAmount,
                        PREMIUMAMOUNT      = @InsurancePremiumAmount,
                        PERIODTYPE         = @InsurancePeriodType,
                        LASTPAIDON         = @InsuranceLastPaidOn,
                        LASTPAIDTILL       = @InsuranceLastPaidTill,
                        PREMIUMDUEON       = @InsurancePremiumDueOn,
                        INSURANCESTATUS    = @InsuranceInsuranceOStatus,
                        REMARKS            = @InsuranceRemarks,
                        SORTORDER          = @InsuranceSortOrder,
                        
                        VERSION            = @InsuranceVersion,
                        MODIFIEDBYID       = @InsuranceModifiedById,
                        MODIFIEDON         = @InsuranceModifiedOn,
                      
                        ASSETID            = @AssetId,
                        EMPLOYEEID         = @EmployeeId,
                        MACHINEID          = @MachineId
                    WHERE INSURANCEID = @InsuranceId;";

        public const string DELETE_INSURANCE = @"
            DELETE FROM MINSURANCE
            WHERE INSURANCEID = @InsuranceId;";

        public const string GET_SELECTLIST_INSURANCE = @"
            SELECT
                INSURANCEID AS Id,
                POLICYNUMBER AS PolicyNumber,
                ENDDATE AS EndDate
            FROM MINSURANCE
            WHERE STATUS = 1
           
            ORDER BY POLICYNUMBER;";
    }
}