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

namespace FrameworkDAL.Query.CodeDefine
{
    public static class CodeDefineQB
    {
        // Returns the CATEGORYCODE of the segment-based (GENERATIONTYPE=4) CodeDefine for this entity, or NULL if none
        public const string GET_SEGMENT_BASED_CATEGORYCODE = @"
            SELECT TOP 1 CATEGORYCODE
            FROM MCODEDEFINE
            WHERE ENTITYID       = @entityid
              AND GENERATIONTYPE = 4
              AND STATUS         = 1
            ORDER BY VERSION DESC;";

        // Resolves CategoryCode → EntityId (for fallback to regular logic when CategoryCode is given)
        public const string GET_ENTITYID_BY_CATEGORYCODE = @"
            SELECT TOP 1 ENTITYID
            FROM MCODEDEFINE
            WHERE CATEGORYCODE = @categorycode
              AND STATUS       = 1
            ORDER BY VERSION DESC;";

        // Safely calls PROC_GENERATE_CODE inside TRY/CATCH so that RAISERROR
        // ('Code definition not found' / 'not segment-based') returns NULL
        // instead of throwing — allowing the caller to fall through to regular logic.
        public const string TRY_EXECUTE_PROC_GENERATE_CODE = @"
            DECLARE @GeneratedCode NVARCHAR(500) = NULL;
            BEGIN TRY
                EXEC dbo.PROC_GENERATE_CODE
                    @CategoryCode  = @categorycode,
                    @InputJson     = @inputjson,
                    @GeneratedCode = @GeneratedCode OUTPUT;
            END TRY
            BEGIN CATCH
                SET @GeneratedCode = NULL;
            END CATCH
            SELECT @GeneratedCode AS GeneratedCode;";

        // Resolves EntityCode → EntityId + metadata
        public const string GET_ENTITY_BY_CODE = @"
        SELECT
            AA.ENTITYID   AS EntityId,
            AA.ENTITYCODE AS EntityCode,
            AA.ENTITYNAME AS EntityName
        FROM MENTITY AA
        WHERE AA.ENTITYCODE = @entitycode;";

        // Fetches all CodeDefine rows for an entity, enriched with Formula + Entity fields
        // NOTE: ISMULTICATEGORY added — was present in old GB4 enrichment loop but missing in GB5
        public const string GET_LOAD_CODE_DEFINE_BY_PARAMS = @"
        SELECT
            C.CODEDEFINEID          AS CodeDefineId,
            C.VERSION               AS CodeDefineVersion,
            C.ENTITYID              AS EntityId,
            E.ENTITYCODE            AS EntityCode,
            E.ENTITYNAME            AS EntityName,
            E.ISMULTICATEGORY       AS EntityIsMultiCategory,
            C.SLNO                  AS CodeDefineSlNo,
            C.CATEGORYCODE          AS CodeDefineCategoryCode,
            C.CATEGORYNAME          AS CodeDefineCategoryName,
            C.GENERATIONTYPE        AS CodeDefineGenerationType,
            C.CATPREFIX             AS CodeDefineCatPrefix,
            C.CATSUFFIX             AS CodeDefineCatSuffix,
            C.FORMULAID             AS CodeDefineFormulaId,
            F.FORMULACODE           AS CodeDefineFormulaCode,
            F.FORMULANAME           AS CodeDefineFormulaName,
            C.FORMULABASEDFIELDS    AS CodeDefineFormulaBasedFields,
            C.CATSTARTNO            AS CodeDefineCatStartNo,
            C.CATENDNO              AS CodeDefineCatEndNo,
            C.CATLASTNO             AS CodeDefineCatLastNo,
            C.AUTOLOADID            AS CodeDefineAutoLoadId,
            C.REMARKS               AS CodeDefineRemarks,
            C.CODESIZE              AS CodeDefineCodeSize,
            C.SORTORDER             AS CodeDefineSortOrder,
            C.STATUS                AS CodeDefineStatus,
            C.SOURCETYPE            AS CodeDefineSourceType,
            C.CREATEDBYID           AS CodeDefineCreatedById,
            C.CREATEDON             AS CodeDefineCreatedOn,
            C.MODIFIEDBYID          AS CodeDefineModifiedById,
            C.MODIFIEDON            AS CodeDefineModifiedOn,
            C.TEMPLATEID            AS CodeDefineTemplateId,
            C.APPLICABLEOUIDS       AS CodeDefineApplicableOuids
        FROM MCODEDEFINE C
            INNER JOIN MENTITY E  ON C.ENTITYID  = E.ENTITYID
            LEFT  JOIN MFORMULA F ON C.FORMULAID = F.FORMULAID
        WHERE C.ENTITYID = @entityid;";


        public const string SAVE_CODEDEFINE = @"INSERT INTO MCODEDEFINE
(
    CODEDEFINEID,
    ENTITYID,
    SLNO,
    CATEGORYCODE,
    CATEGORYNAME,
    GENERATIONTYPE,
    CATPREFIX,
    CATSUFFIX,
    FORMULAID,
    FORMULABASEDFIELDS,
    CATSTARTNO,
    CATENDNO,
    CATLASTNO,
    REMARKS,
    CODESIZE,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    AUTOLOADID,
    TEMPLATEID,
    APPLICABLEOUIDS
)
VALUES
(
    @CodeDefineId,
    @EntityId,
    @CodeDefineSlNo,
    @CodeDefineCategoryCode,
    @CodeDefineCategoryName,
    @CodeDefineGenerationType,
    @CodeDefineCatPrefix,
    @CodeDefineCatSuffix,
    @CodeDefineFormulaId,
    @CodeDefineFormulaBasedFields,
    @CodeDefineCatStartNo,
    @CodeDefineCatEndNo,
    @CodeDefineCatLastNo,
    @CodeDefineRemarks,
    @CodeDefineCodeSize,
    @CodeDefineSortOrder,
    @CodeDefineStatus,
    @CodeDefineVersion,
    @CodeDefineSourceType,
    @CodeDefineCreatedById,      
    @CodeDefineCreatedOn,      
    @CodeDefineModifiedById,  
    @CodeDefineModifiedOn,     
    @CodeDefineAutoLoadId,
    @CodeDefineTemplateId,
    @CodeDefineApplicableOuids
);
";

        public const string UPDATE_CODEDEFINE = @"UPDATE MCODEDEFINE
SET
    ENTITYID               = @EntityId,
    SLNO                   = @CodeDefineSlNo,
    CATEGORYCODE           = @CodeDefineCategoryCode,
    CATEGORYNAME           = @CodeDefineCategoryName,
    GENERATIONTYPE         = @CodeDefineGenerationType,
    CATPREFIX              = @CodeDefineCatPrefix,
    CATSUFFIX              = @CodeDefineCatSuffix,
    FORMULAID              = @CodeDefineFormulaId,
    FORMULABASEDFIELDS     = @CodeDefineFormulaBasedFields,
    CATSTARTNO             = @CodeDefineCatStartNo,
    CATENDNO               = @CodeDefineCatEndNo,
    CATLASTNO              = @CodeDefineCatLastNo,
    REMARKS                = @CodeDefineRemarks,
    CODESIZE               = @CodeDefineCodeSize,
    SORTORDER              = @CodeDefineSortOrder,
    STATUS                 = @CodeDefineStatus,
    VERSION                = @CodeDefineVersion,
    SOURCETYPE             = @CodeDefineSourceType,
    MODIFIEDBYID           = @CodeDefineModifiedById,
    MODIFIEDON             = @CodeDefineModifiedOn,   
    AUTOLOADID             = @CodeDefineAutoLoadId,
    TEMPLATEID             = @CodeDefineTemplateId,
    APPLICABLEOUIDS        = @CodeDefineApplicableOuids
WHERE 
    CODEDEFINEID           = @CodeDefineId;";

        public const string DELETE_CODEDEFINE = @"DELETE FROM MCODEDEFINE
                                            WHERE CODEDEFINEID = @CodeDefineId;";

        public const string GET_SELECTLIST_CODEDEFINE = @"WITH CodeDefine AS (
                                            SELECT
                                                A.CODEDEFINEID      AS Id, 
                                                A.CATEGORYCODE      AS CategoryCode,
                                                A.CATEGORYNAME      AS CategoryName,
                                                A.GENERATIONTYPE    AS GenerationType,
                                                ROW_NUMBER() OVER (ORDER BY A.CODEDEFINEID) AS RowNum
                                            FROM MCODEDEFINE A
                                        )
                                        SELECT Id, CategoryCode, CategoryName, GenerationType
                                        FROM CodeDefine
                                        WHERE 
                                             (@firstnumber = -1 AND @maxresult = -1)
                                             OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}