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

namespace GB5Shared.Query.CodeGeneration
{
    public static class CodeGenerationQB
    {
        public static string GET_ENTITY_BY_CODE = @"
    SELECT
        ENTITYID AS EntityId,
        ENTITYCODE AS EntityCode,
        ENTITYNAME AS EntityName,
        BASECLASSID AS BaseClassId,
        SECURITYLEVEL AS EntitySecurityLevel,
        ENTITYTYPE AS EntityType,
        ISPERSIST AS EntityISPersist,
        SCOPE AS EntityScope,
        PROJECTID AS ProjectId,
        MODULEID AS ModuleId,
        DBOBJECTID AS DbObjectId,
        ENTITYNATURE AS EntityNature,
        SECTION AS EntitySection,
        IMPLEMENTATIONTYPE AS EntityImplementationType,
        ISCODEAPPLICABLE AS EntityIsCodeApplicable,
        MAXCODESIZE AS EntityMaxCodeSize,
        DEFAULTSIZE AS EntityDefaultSize,
        ISMULTICATEGORY AS EntityIsMultiCategory,
        ISAUTO AS EntityIsAuto,
        ISMANUAL AS EntityIsManual,
        ISFORMULA AS EntityIsFormula,
        ORGANIZATIONUNITTYPE AS EntityOrganizationUnitType,
        ENTITYDEFINEDIN AS EntityDefinedIN,
        REMARKS AS EntityRemarks,
        ISREFERAL AS EntityIsReferal,
        ENTITYCATEGORY AS EntityEntityCategory,
        SORTORDER AS EntitySortOrder,
        STATUS AS EntityStatus,
        VERSION AS EntityVersion,
        SOURCETYPE AS EntitySourceType,
        CREATEDBYID AS EntityCreatedById,
        CREATEDON AS EntityCreatedOn,
        MODIFIEDBYID AS EntityModifiedById,
        MODIFIEDON AS EntityModifiedOn,
        POCOTODTOLINKID AS EntityPocoToDTOLinkId,
        POCOTOPICKLISTLINKID AS EntityPocoToPicklistLinkId,
        CATEGORYMEMBERID AS EntityCategoryMemberId,
        ISADDRESS AS EntityIsAddress,
        ISCONTACT AS EntityIsContact,
        ISSEARCH AS EntityIsSearch,
        SAVEDEPLOYMENTID AS EntitySaveDeploymentId,
        DELETEDEPLOYMENTID AS EntityDeleteDeploymentId,
        ADDONDBOBJECTID AS AddOnDbObjectId,
        MENUID AS MenuId,
        WEBFORMID AS WebformId,
        DISPLAYWEBFORMID AS DisplayWebformId,
        ISICEMAPREQUIRED AS EntityIsIceMapRequired,
        ISMULTITENANT AS EntityIsMultiTenant,
        PICKLISTID AS PicklistId
    FROM 
        MENTITY
    WHERE 
        ENTITYCODE = @entitycode";

        public static string GET_ENTITY_BY_ID = @"
    SELECT
        ENTITYID AS EntityId,
        ENTITYCODE AS EntityCode,
        ENTITYNAME AS EntityName,
        BASECLASSID AS BaseClassId,
        SECURITYLEVEL AS EntitySecurityLevel,
        ENTITYTYPE AS EntityType,
        ISPERSIST AS EntityISPersist,
        SCOPE AS EntityScope,
        PROJECTID AS ProjectId,
        MODULEID AS ModuleId,
        DBOBJECTID AS DbObjectId,
        ENTITYNATURE AS EntityNature,
        SECTION AS EntitySection,
        IMPLEMENTATIONTYPE AS EntityImplementationType,
        ISCODEAPPLICABLE AS EntityIsCodeApplicable,
        MAXCODESIZE AS EntityMaxCodeSize,
        DEFAULTSIZE AS EntityDefaultSize,
        ISMULTICATEGORY AS EntityIsMultiCategory,
        ISAUTO AS EntityIsAuto,
        ISMANUAL AS EntityIsManual,
        ISFORMULA AS EntityIsFormula,
        ORGANIZATIONUNITTYPE AS EntityOrganizationUnitType,
        ENTITYDEFINEDIN AS EntityDefinedIN,
        REMARKS AS EntityRemarks,
        ISREFERAL AS EntityIsReferal,
        ENTITYCATEGORY AS EntityEntityCategory,
        SORTORDER AS EntitySortOrder,
        STATUS AS EntityStatus,
        VERSION AS EntityVersion,
        SOURCETYPE AS EntitySourceType,
        CREATEDBYID AS EntityCreatedById,
        CREATEDON AS EntityCreatedOn,
        MODIFIEDBYID AS EntityModifiedById,
        MODIFIEDON AS EntityModifiedOn,
        POCOTODTOLINKID AS EntityPocoToDTOLinkId,
        POCOTOPICKLISTLINKID AS EntityPocoToPicklistLinkId,
        CATEGORYMEMBERID AS EntityCategoryMemberId,
        ISADDRESS AS EntityIsAddress,
        ISCONTACT AS EntityIsContact,
        ISSEARCH AS EntityIsSearch,
        SAVEDEPLOYMENTID AS EntitySaveDeploymentId,
        DELETEDEPLOYMENTID AS EntityDeleteDeploymentId,
        ADDONDBOBJECTID AS AddOnDbObjectId,
        MENUID AS MenuId,
        WEBFORMID AS WebformId,
        DISPLAYWEBFORMID AS DisplayWebformId,
        ISICEMAPREQUIRED AS EntityIsIceMapRequired,
        ISMULTITENANT AS EntityIsMultiTenant,
        PICKLISTID AS PicklistId
    FROM 
        MENTITY
    WHERE 
        ENTITYID = @entityid";

        public const string LOAD_CODE_DEFINE_BY_PARAMS_BASED_ON_CATEGORY_ID_SQL = @"
                    SELECT
                        cd.CODEDEFINEID         AS CodeDefineId,
                        cd.ENTITYID              AS EntityId,
                        cd.SLNO                   AS CodeDefineSlNo,
                        cd.CATEGORYCODE          AS CodeDefineCategoryCode,
                        cd.CATEGORYNAME          AS CodeDefineCategoryName,
                        cd.GENERATIONTYPE        AS CodeDefineGenerationType,
                        cd.CATPREFIX              AS CodeDefineCatPrefix,
                        cd.CATSUFFIX              AS CodeDefineCatSuffix,
                        cd.FORMULAID              AS CodeDefineFormulaId,
                        f.FORMULACODE             AS CodeDefineFormulaCode,
                        f.FORMULANAME             AS CodeDefineFormulaName,
                        cd.FORMULABASEDFIELDS     AS CodeDefineFormulaBasedFields,
                        cd.CATSTARTNO             AS CodeDefineCatStartNo,
                        cd.CATENDNO               AS CodeDefineCatEndNo,
                        cd.CATLASTNO              AS CodeDefineCatLastNo,
                        cd.AUTOLOADID             AS CodeDefineAutoLoadId,
                        cd.REMARKS                 AS CodeDefineRemarks,
                        cd.CODESIZE                AS CodeDefineCodeSize,
                        cd.TEMPLATEID              AS CodeDefineTemplateId,
                        cd.CREATEDBYID             AS CodeDefineCreatedById,
                        cd.CREATEDON                AS CodeDefineCreatedOn,
                        cd.MODIFIEDBYID             AS CodeDefineModifiedById,
                        cd.MODIFIEDON                AS CodeDefineModifiedOn,
                        cd.SORTORDER                 AS CodeDefineSortOrder,
                        cd.STATUS                     AS CodeDefineStatus,
                        cd.VERSION                     AS CodeDefineVersion,
                        cd.SOURCETYPE                  AS CodeDefineSourceType,
                        e.ENTITYCODE                   AS EntityCode,
                        e.ENTITYNAME                   AS EntityName,
                        e.ISMULTICATEGORY               AS EntityIsMultiCategory,
                        cd.APPLICABLEOUIDS              AS CodeDefineApplicableOuids
                    FROM
                        MCODEDEFINE cd
                        LEFT JOIN MENTITY e
                            ON cd.ENTITYID = e.ENTITYID 
                        LEFT JOIN MFORMULA f
                            ON cd.FORMULAID = f.FORMULAID
                    WHERE
                        cd.ENTITYID = @entityid
                        AND cd.CODEDEFINEID = @codedefineid";

        // Simple (non-category) MCODEDEFINE lookup — used when the entity's ISMULTICATEGORY = 0,
        // so there is no per-category CODEDEFINEID to filter by, unlike
        // LOAD_CODE_DEFINE_BY_PARAMS_BASED_ON_CATEGORY_ID_SQL above.
        public const string GET_CODEDEFINE_BY_ENTITYID = @"
                    SELECT
                        cd.CODEDEFINEID         AS CodeDefineId,
                        cd.ENTITYID              AS EntityId,
                        cd.SLNO                   AS CodeDefineSlNo,
                        cd.CATEGORYCODE          AS CodeDefineCategoryCode,
                        cd.CATEGORYNAME          AS CodeDefineCategoryName,
                        cd.GENERATIONTYPE        AS CodeDefineGenerationType,
                        cd.CATPREFIX              AS CodeDefineCatPrefix,
                        cd.CATSUFFIX              AS CodeDefineCatSuffix,
                        cd.FORMULAID              AS CodeDefineFormulaId,
                        f.FORMULACODE             AS CodeDefineFormulaCode,
                        f.FORMULANAME             AS CodeDefineFormulaName,
                        cd.FORMULABASEDFIELDS     AS CodeDefineFormulaBasedFields,
                        cd.CATSTARTNO             AS CodeDefineCatStartNo,
                        cd.CATENDNO               AS CodeDefineCatEndNo,
                        cd.CATLASTNO              AS CodeDefineCatLastNo,
                        cd.AUTOLOADID             AS CodeDefineAutoLoadId,
                        cd.REMARKS                 AS CodeDefineRemarks,
                        cd.CODESIZE                AS CodeDefineCodeSize,
                        cd.TEMPLATEID              AS CodeDefineTemplateId,
                        cd.CREATEDBYID             AS CodeDefineCreatedById,
                        cd.CREATEDON                AS CodeDefineCreatedOn,
                        cd.MODIFIEDBYID             AS CodeDefineModifiedById,
                        cd.MODIFIEDON                AS CodeDefineModifiedOn,
                        cd.SORTORDER                 AS CodeDefineSortOrder,
                        cd.STATUS                     AS CodeDefineStatus,
                        cd.VERSION                     AS CodeDefineVersion,
                        cd.SOURCETYPE                  AS CodeDefineSourceType,
                        e.ENTITYCODE                   AS EntityCode,
                        e.ENTITYNAME                   AS EntityName,
                        e.ISMULTICATEGORY               AS EntityIsMultiCategory,
                        cd.APPLICABLEOUIDS              AS CodeDefineApplicableOuids
                    FROM
                        MCODEDEFINE cd
                        LEFT JOIN MENTITY e
                            ON cd.ENTITYID = e.ENTITYID
                        LEFT JOIN MFORMULA f
                            ON cd.FORMULAID = f.FORMULAID
                    WHERE
                        cd.ENTITYID = @entityid";

        public const string UPDATE_CATLASTNO =
            "UPDATE MCODEDEFINE SET CATLASTNO = @catlastno WHERE CODEDEFINEID = @codedefineid";

        // Atomic increment-and-return in a single round trip. Reading CATLASTNO into C#, adding 1,
        // then writing it back in a separate statement (the old UPDATE_CATLASTNO usage) is a
        // read-then-write race: generating codes for multiple records in one request (e.g. saving
        // several PartyBranch rows in one PartyDTO.PartyBranchList) can read the same starting value
        // twice before either write commits, producing duplicate codes.
        public const string INCREMENT_AND_GET_CATLASTNO = @"
UPDATE MCODEDEFINE SET CATLASTNO = CATLASTNO + 1
OUTPUT INSERTED.CATLASTNO
WHERE CODEDEFINEID = @codedefineid;";

        // Wraps the OUTPUT parameter in a SELECT so the result is returned as a normal result set.
        // This avoids DynamicParameters and works with standard IQueryExecutor.QuerySingleAsync.
        public const string EXECUTE_PROC_GENERATE_CODE = @"
            DECLARE @GeneratedCode NVARCHAR(500);
            EXEC dbo.PROC_GENERATE_CODE
                @CategoryCode  = @categorycode,
                @InputJson     = @inputjson,
                @GeneratedCode = @GeneratedCode OUTPUT;
            SELECT @GeneratedCode AS GeneratedCode;";

        public const string GET_ENTITYMEMBERS = @"
                SELECT
                    ENTITYMEMBERID                      AS EntityMemberId,
                    SLNO                               AS EntityMemberSlNo,
                    MEMBERNAME                         AS EntityMemberMemberName,
                    DATATYPE                           AS EntityMemberDataType,
                    PARTICULARS                        AS EntityMemberParticulars,
                    ISPERSIST                          AS EntityMemberIsPersist,
                    INPUTTYPE                          AS EntityMemberInputType,
                    ISPRIMARY                          AS EntityMemberIsPrimary,
                    ISLOGICAL                          AS EntityMemberIsLogical,
                    ISCRITERIA                         AS EntityMemberIsCriteria,
                    ISSELECTLIST                       AS EntityMemberIsSelectList,
                    ISUSERIDENTIFYING                  AS EntityMemberIsUseridentifying,
                    ISDATARESTRICTIONCRITERIA          AS EntityMemberIsDataRestrictionCriteria,
                    SECTION                            AS EntityMemberSection,
                    DBFIELDID                          AS EntityMemberDbOjectFieldId,
                    ISINDEXED                          AS EntityMemberIsIndexed,
                    OBJECTTYPEID                       AS EntityMemberObjectTypeId,
                    ISVERSIONING                       AS EntityMemberIsVersioning,
                    ISIDENTITY                         AS EntityMemberIsIdentity,
                    ISSETREQUIRED                      AS EntityMemberIsSetRequired,
                    ISCHECK                            AS EntityMemberIsCheck,
                    ISFORMULAFORCODE                   AS EntityMemberIsFormulaForCode,
                    ISFORMULABASEDFIELD                AS EntityMemberIsFormulaBasedField,
                    CODEFORMULAID                      AS EntityMemberCodeFormulaId,
                    ISCODEFIELD                        AS EntityMemberIsCodeField,
                    ISCODECATEGORY                     AS EntityMemberIsCodeCategory,
                    ISEXPANDREQ                        AS EntityMemberIsExpandReq,
                    ENUMVALUES                         AS EntityMemberEnumValues,
                    CRITERIAFIELDNAME                  AS EntityMemberCriteriaFieldName,
                    CRITERIAATTRIBUTEID                AS CriteriaAttributeId,
                    ISICEFIELD                         AS EntityMemberIsIceField,
                    SAMPLEDATA                         AS EntityMemberSampleData,
                    ENTITYID                           AS EntityMemberEntityId,
                    ISUNIQUEKEY                        AS EntityMemberIsUniquekey,
                    ISCOMMENTREQUIRED                  AS EntityMemberIsCommnetRequired
                FROM MENTITYMEMBERS
                WHERE ENTITYMEMBERID = @entitymemberid";
    }
}