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

namespace MaintenanceDAL.Query.Activity
{
    public static class ActivityQB
    {
        public const string GET_ACTIVITY = @"
            SELECT
    a.ACTIVITYID           AS activityid,
    a.ACTIVITYCODE         AS activitycode,
    a.ACTIVITYNAME         AS activityname,
    a.ACTIVITYNATURE       AS activitynature,
    a.ACTIVITYTYPEID       AS activitytypeid,
    a.PREFIX               AS activityprefix,
    a.ISTAKEPENDING        AS activityistakepending,
    a.ISALLOCREQ           AS activityisallocreq,
    a.ISALLOCDIRECT        AS activityisallocdirect, 
    a.ISPRODUCTIVE         AS activityisproductive,
    a.ISREPEATABLE         AS activityisrepeatable,
    a.ISMULTIRESREQ        AS activityismultiresreq,
    a.ISALLOWREWORK        AS activityisallowrework,
    a.ISOUTPUTBASED        AS activityisoutputbased,
    a.ISHAVEPRECON         AS activityishaveprecon,
    a.PRECONCHKLIST        AS activitypreconchklist,
    a.ISHAVEPOSTCON        AS activityishavepostcon,
    a.POSTCONCHKLIST       AS activitypostconchklist,
    a.ISREVIEWREQ          AS activityisreviewreq,
    a.REVIEWCHKLIST        AS activityreviewchklist,
    a.ISALLOCBASED         AS activityisallocbased,
    a.ALLOCTYPE            AS activityalloctype,
    a.ACTGENTYPE           AS activityactgentype,
    a.AUTOTYPE             AS activityautotype,
    a.AUTOTYPEPROCESS      AS activityautotypeprocess,
    a.CLASSID              AS activityclassid,
    a.SUBCLASSID           AS activitysubclassid,
    a.ISACTIVE             AS activityisactive,
    a.REMARKS              AS activityremarks,
    a.ISNOBASED            AS activityisnobased,
    a.MAXDAYS              AS activitymaxdays,
    a.MAXHRS               AS activitymaxhrs,
    a.MINDAYS              AS activitymindays,
    a.MINHRS               AS activityminhrs,
    a.AVGDAYS              AS activityavgdays,
    a.AVGHRS               AS activityavghrs,
    a.ISTRAVEL             AS activityistravel,
    a.ISF2F                AS activityisf2f,
    a.ISEXPENSE            AS activityisexpense,
    a.FUNDCODE             AS activityfundcode,
    a.ISREMINDER           AS activityisreminder,
    a.RMDNOOFDAYS          AS activityrmdnoofdays,
    a.RMDNOOFHRS           AS activityrmdnoofhrs,
    a.RMDMODE              AS activityrmdmode,
    a.ISHOUR               AS activityishour,
    a.ISCHARGE             AS activityischarge,
    a.LABOURAMT            AS activitylaboutamt,
    a.OHAMT                AS activityohamt,
    a.CHARGETYPECODE       AS activitychargetypecode,
    a.COLLACCCODE          AS activitycollacccode,
    a.PAIDACCCODE          AS activitypaidacccode,
    a.ACTIVITYREF          AS activityactivityref,
    a.ACTCOSTTYPE          AS activityactcosttype,
    a.TIMEUOM              AS activitytimeuom,
    a.ITEMCODE             AS activityitemcode,
    a.SORTORDER            AS activitysortorder,
    a.STATUS               AS activitystatus,
    a.VERSION              AS activityversion,
    a.CREATEDBYID          AS activitycreatedbyid,
    a.CREATEDON            AS activitycreatedon,
    a.MODIFIEDBYID         AS activitymodifiedbyid,
    a.MODIFIEDON           AS activitymodifiedon,
    a.SOURCETYPE           AS sourcetype,
    a.TENANTID             AS tenantid,
    a.UOMID                AS uomid,
    a.RECOVERYTYPE         AS activityrecoverytype,
    a.RECOVERYPERCENTAGE   AS activityrecoverypercentage,
    a.ISCLAIM              AS activityisclaim,
    a.BILLACCOUNTID        AS billaccountid,
    a.EXPENSEACCOUNTID     AS expenseaccountname,
    a.ITEMID               AS itemid,
    a.SKUID                AS skuid,
    a.PARAMETERSETID       AS parametersetid,
    a.PRECHECKLISTID       AS prechecklistid,
    a.POSTCHECKLISTID      AS postchecklistid,
    a.REVIEWCHECKLISTID    AS reviewchecklistid,
    a.FORMTEMPLATEID       AS fortemplateid,
    a.BOMID                AS bomid,
    a.BORID                AS borid,
    a.APPLICABLERESULTS    AS activityapplicableresults,
    a.APPLICABLESTAGES     AS activityapplicablestages,
    a.ISSCORE              AS activityisscore,
    a.ISLETTER             AS activityisletter,
    a.ISCITYCLASS          AS activityiscityclass,
    a.ISGRADE              AS activityisgrade,
    a.ISMODE               AS activityismode,
    a.ISVEHICLETYPE        AS activityisvehicletype,
    a.ISDISTANCE           AS activityisdistance

            FROM MACTIVITY a
            WHERE a.ACTIVITYID = @activityid;";


        public const string GetSelectListActivity = @"SELECT
    a.ACTIVITYID   AS id,
    a.ACTIVITYCODE AS code,
    a.ACTIVITYNAME AS name,
    a.UOMId        AS uomid,
    mu.UOMCode      AS uomcode,
    mu.UOMName      AS uomname
    FROM MACTIVITY a ,muom mu 
    where 
    a.UOMID = mu.UOMID;";

        public const string SAVE_ACTIVITY = @"
INSERT INTO MACTIVITY
(
    ACTIVITYID,
    ACTIVITYCODE,
    ACTIVITYNAME,
    ACTIVITYNATURE,
    ACTIVITYTYPEID,
    PREFIX,
    ISTAKEPENDING,
    ISALLOCREQ,
    ISALLOCDIRECT,
    ISPRODUCTIVE,
    ISREPEATABLE,
    ISMULTIRESREQ,
    ISALLOWREWORK,
    ISOUTPUTBASED,
    ISHAVEPRECON,
    PRECONCHKLIST,
    ISHAVEPOSTCON,
    POSTCONCHKLIST,
    ISREVIEWREQ,
    REVIEWCHKLIST,
    ISALLOCBASED,
    ALLOCTYPE,
    ACTGENTYPE,
    AUTOTYPE,
    AUTOTYPEPROCESS,
    CLASSID,
    SUBCLASSID,
    ISACTIVE,
    REMARKS,
    ISNOBASED,
    MAXDAYS,
    MAXHRS,
    MINDAYS,
    MINHRS,
    AVGDAYS,
    AVGHRS,
    ISTRAVEL,
    ISF2F,
    ISEXPENSE,
    FUNDCODE,
    ISREMINDER,
    RMDNOOFDAYS,
    RMDNOOFHRS,
    RMDMODE,
    ISHOUR,
    ISCHARGE,
    LABOURAMT,
    OHAMT,
    CHARGETYPECODE,
    COLLACCCODE,
    PAIDACCCODE,
    ACTIVITYREF,
    ACTCOSTTYPE,
    TIMEUOM,
    ITEMCODE,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID,
    UOMID,
    RECOVERYTYPE,
    RECOVERYPERCENTAGE,
    ISCLAIM,
    BILLACCOUNTID,
    EXPENSEACCOUNTID,
    ITEMID,
    SKUID,
    PARAMETERSETID,
    PRECHECKLISTID,
    POSTCHECKLISTID,
    REVIEWCHECKLISTID,
    FORMTEMPLATEID,
    BOMID,
    BORID,
    APPLICABLERESULTS,
    APPLICABLESTAGES,
    ISSCORE,
    ISLETTER,
    ISCITYCLASS,
    ISGRADE,
    ISMODE,
    ISVEHICLETYPE,
    ISDISTANCE
)
VALUES
(
    @ActivityId,
    @ActivityCode,
    @ActivityName,
    @ActivityNature,
    @ActivityTypeId,
    @ActivityPrefix,
    @ActivityIsTakePending,
    @ActivityIsAllocReq,
    @ActivityIsAllocDirect,
    @ActivityIsProductive,
    @ActivityIsRepeatable,
    @ActivityIsMultiResReq,
    @ActivityIsAllowRework,
    @ActivityIsOutputBased,
    @ActivityIsHavePrecon,
    @ActivityPreconChkList,
    @ActivityIsHavePostcon,
    @ActivityPostconChkList,
    @ActivityIsReviewReq,
    @ActivityReviewChkList,
    @ActivityIsAllocBased,
    @ActivityAllocType,
    @ActivityActGenType,
    @ActivityAutoType,
    @ActivityAutoTypeProcess,
    @ActivityClassId,
    @ActivitySubClassId,
    @ActivityIsActive,
    @ActivityRemarks,
    @ActivityIsNoBased,
    @ActivityMaxDays,
    @ActivityMaxHrs,
    @ActivityMinDays,
    @ActivityMinHrs,
    @ActivityAvgDays,
    @ActivityAvgHrs,
    @ActivityIsTravel,
    @ActivityIsF2F,
    @ActivityIsExpense,
    @ActivityFundCode,
    @ActivityIsReminder,
    @ActivityRMDNoOfDays,
    @ActivityRMDNoOfHrs,
    @ActivityRMDMode,
    @ActivityIsHour,
    @ActivityIsCharge,
    @ActivityLaboutAmt,
    @ActivityOHAmt,
    @ActivityChargeTypeCode,
    @ActivityCollAccCode,
    @ActivityPaidAccCode,
    @ActivityActivityRef,
    @ActivityActCostType,
    @ActivityTimeUOM,
    @ActivityItemCode,
    @ActivitySortOrder,
    @ActivityStatus,
    @ActivityVersion,
    @ActivityCreatedById,
    @ActivityCreatedOn,
    @ActivityModifiedById,
    @ActivityModifiedOn,
    @SourceType,
    @TenantId,
    @UOMId,
    @ActivityRecoveryType,
    @ActivityRecoveryPercentage,
    @ActivityIsClaim,
    @BillAccountId,
    @ExpenseAccountId,
    @ItemId,
    @SKUId,
    @ParameterSetId,
    @PreCheckListId,
    @PostCheckListId,
    @ReviewCheckListId,
    @ForTemplateId,
    @BOMId,
    @BORId,
    @ActivityApplicableResults,
    @ActivityApplicableStages,
    @ActivityIsScore,
    @ActivityIsLetter,
    @ActivityIsCityClass,
    @ActivityIsGrade,
    @ActivityIsMode,
    @ActivityIsVehicleType,
    @ActivityIsDistance
);";

        // Update: all editable columns except the PK (ACTIVITYID), tenant (WHERE filter) and
        // create-audit columns (CREATEDBYID/CREATEDON), which must be preserved on update.
        // Tenant-scoped WHERE per multi-tenancy rules. Column↔param mapping mirrors SAVE_ACTIVITY.
        public const string UPDATE_ACTIVITY = @"
UPDATE MACTIVITY SET
    ACTIVITYCODE       = @ActivityCode,
    ACTIVITYNAME       = @ActivityName,
    ACTIVITYNATURE     = @ActivityNature,
    ACTIVITYTYPEID     = @ActivityTypeId,
    PREFIX             = @ActivityPrefix,
    ISTAKEPENDING      = @ActivityIsTakePending,
    ISALLOCREQ         = @ActivityIsAllocReq,
    ISALLOCDIRECT      = @ActivityIsAllocDirect,
    ISPRODUCTIVE       = @ActivityIsProductive,
    ISREPEATABLE       = @ActivityIsRepeatable,
    ISMULTIRESREQ      = @ActivityIsMultiResReq,
    ISALLOWREWORK      = @ActivityIsAllowRework,
    ISOUTPUTBASED      = @ActivityIsOutputBased,
    ISHAVEPRECON       = @ActivityIsHavePrecon,
    PRECONCHKLIST      = @ActivityPreconChkList,
    ISHAVEPOSTCON      = @ActivityIsHavePostcon,
    POSTCONCHKLIST     = @ActivityPostconChkList,
    ISREVIEWREQ        = @ActivityIsReviewReq,
    REVIEWCHKLIST      = @ActivityReviewChkList,
    ISALLOCBASED       = @ActivityIsAllocBased,
    ALLOCTYPE          = @ActivityAllocType,
    ACTGENTYPE         = @ActivityActGenType,
    AUTOTYPE           = @ActivityAutoType,
    AUTOTYPEPROCESS    = @ActivityAutoTypeProcess,
    CLASSID            = @ActivityClassId,
    SUBCLASSID         = @ActivitySubClassId,
    ISACTIVE           = @ActivityIsActive,
    REMARKS            = @ActivityRemarks,
    ISNOBASED          = @ActivityIsNoBased,
    MAXDAYS            = @ActivityMaxDays,
    MAXHRS             = @ActivityMaxHrs,
    MINDAYS            = @ActivityMinDays,
    MINHRS             = @ActivityMinHrs,
    AVGDAYS            = @ActivityAvgDays,
    AVGHRS             = @ActivityAvgHrs,
    ISTRAVEL           = @ActivityIsTravel,
    ISF2F              = @ActivityIsF2F,
    ISEXPENSE          = @ActivityIsExpense,
    FUNDCODE           = @ActivityFundCode,
    ISREMINDER         = @ActivityIsReminder,
    RMDNOOFDAYS        = @ActivityRMDNoOfDays,
    RMDNOOFHRS         = @ActivityRMDNoOfHrs,
    RMDMODE            = @ActivityRMDMode,
    ISHOUR             = @ActivityIsHour,
    ISCHARGE           = @ActivityIsCharge,
    LABOURAMT          = @ActivityLaboutAmt,
    OHAMT              = @ActivityOHAmt,
    CHARGETYPECODE     = @ActivityChargeTypeCode,
    COLLACCCODE        = @ActivityCollAccCode,
    PAIDACCCODE        = @ActivityPaidAccCode,
    ACTIVITYREF        = @ActivityActivityRef,
    ACTCOSTTYPE        = @ActivityActCostType,
    TIMEUOM            = @ActivityTimeUOM,
    ITEMCODE           = @ActivityItemCode,
    SORTORDER          = @ActivitySortOrder,
    STATUS             = @ActivityStatus,
    VERSION            = @ActivityVersion,
    MODIFIEDBYID       = @ActivityModifiedById,
    MODIFIEDON         = @ActivityModifiedOn,
    SOURCETYPE         = @SourceType,
    UOMID              = @UOMId,
    RECOVERYTYPE       = @ActivityRecoveryType,
    RECOVERYPERCENTAGE = @ActivityRecoveryPercentage,
    ISCLAIM            = @ActivityIsClaim,
    BILLACCOUNTID      = @BillAccountId,
    EXPENSEACCOUNTID   = @ExpenseAccountId,
    ITEMID             = @ItemId,
    SKUID              = @SKUId,
    PARAMETERSETID     = @ParameterSetId,
    PRECHECKLISTID     = @PreCheckListId,
    POSTCHECKLISTID    = @PostCheckListId,
    REVIEWCHECKLISTID  = @ReviewCheckListId,
    FORMTEMPLATEID     = @ForTemplateId,
    BOMID              = @BOMId,
    BORID              = @BORId,
    APPLICABLERESULTS  = @ActivityApplicableResults,
    APPLICABLESTAGES   = @ActivityApplicableStages,
    ISSCORE            = @ActivityIsScore,
    ISLETTER           = @ActivityIsLetter,
    ISCITYCLASS        = @ActivityIsCityClass,
    ISGRADE            = @ActivityIsGrade,
    ISMODE             = @ActivityIsMode,
    ISVEHICLETYPE      = @ActivityIsVehicleType,
    ISDISTANCE         = @ActivityIsDistance
WHERE ACTIVITYID = @ActivityId AND TENANTID = @TenantId;";

        // Hard delete of a master Activity row. Tenant isolation is handled by the per-login
        // connection in IQueryExecutor (delete receives only the id, so no @TenantId param).
        public const string DELETE_ACTIVITY = @"
DELETE FROM MACTIVITY
WHERE ACTIVITYID = @ActivityId;";
    }
}