namespace FrameworkDAL.Query.EIPConversation.EIPFlowAdmin
{
    // INDEX required on MEIPFLOWDEFINITION:
    //   IX_MEIPFLOWDEFINITION_TENANT_STATUS on (TENANTID, STATUS) INCLUDE (FLOWCODE, FLOWSTATUS, MODIFIEDON)
    //   IX_MEIPFLOWDEFINITION_FLOWCODE on (FLOWCODE, TENANTID)
    public static class EIPFlowAdminQB
    {
        // =====================================================
        // List — filterable by FlowStatus and free-text search
        // Excludes soft-deleted rows (STATUS = 0)
        // =====================================================
        public const string GET_FLOW_LIST = @"
            SELECT
                F.FLOWDEFINITIONID    AS FlowDefinitionId,
                F.TENANTID            AS TenantId,
                F.FLOWCODE            AS FlowCode,
                F.FLOWVERSION         AS FlowVersion,
                F.FLOWSTATUS          AS FlowStatus,
                F.VERSION             AS Version,
                F.STATUS              AS Status,
                ISNULL(
                    TRY_CAST(JSON_VALUE(F.JSONDEFINITION, '$[0]') AS INT),
                    0
                )                     AS StepCount,
                F.CREATEDBYID         AS CreatedById,
                CB.EMPLOYEENAME       AS CreatedByName,
                F.CREATEDON           AS CreatedOn,
                F.MODIFIEDBYID        AS ModifiedById,
                MB.EMPLOYEENAME       AS ModifiedByName,
                F.MODIFIEDON          AS ModifiedOn
            FROM MEIPFLOWDEFINITION F
            LEFT JOIN MEMPLOYEE CB ON F.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON F.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE F.TENANTID = @TenantId
              AND F.STATUS   = 1
              AND (@FlowStatus IS NULL OR F.FLOWSTATUS = @FlowStatus)
              AND (
                    @Search IS NULL
                    OR F.FLOWCODE LIKE '%' + @Search + '%'
                  )
            ORDER BY F.FLOWCODE ASC";

        // =====================================================
        // Single record — full detail including JsonDefinition
        // =====================================================
        public const string GET_FLOW = @"
            SELECT
                F.FLOWDEFINITIONID    AS FlowDefinitionId,
                F.TENANTID            AS TenantId,
                F.FLOWCODE            AS FlowCode,
                F.FLOWVERSION         AS FlowVersion,
                F.FLOWSTATUS          AS FlowStatus,
                F.JSONDEFINITION      AS JsonDefinition,
                F.VERSION             AS Version,
                F.STATUS              AS Status,
                F.CREATEDBYID         AS CreatedById,
                CB.EMPLOYEENAME       AS CreatedByName,
                F.CREATEDON           AS CreatedOn,
                F.MODIFIEDBYID        AS ModifiedById,
                MB.EMPLOYEENAME       AS ModifiedByName,
                F.MODIFIEDON          AS ModifiedOn
            FROM MEIPFLOWDEFINITION F
            LEFT JOIN MEMPLOYEE CB ON F.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON F.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE F.FLOWDEFINITIONID = @FlowDefinitionId
              AND F.TENANTID         = @TenantId
              AND F.STATUS           = 1";

        // =====================================================
        // Insert — DB auto-generates FLOWDEFINITIONID (IDENTITY)
        // =====================================================
        // FLOWDEFINITIONID is a plain INT PK (not IDENTITY) — the caller must supply it via
        // AUTONUMBERCONSTANT.EIPFLOWDEFINITION before calling this, same convention as every
        // other AutoNumber-based PK in this module (TEIPINTERACTIONSESSION, TEIPSTEPEXECUTION, ...).
        public const string INSERT_FLOW = @"
            INSERT INTO MEIPFLOWDEFINITION (
                FLOWDEFINITIONID,
                TENANTID,
                FLOWCODE,
                FLOWVERSION,
                FLOWSTATUS,
                JSONDEFINITION,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE
            )
            VALUES (
                @FlowDefinitionId,
                @TenantId,
                @FlowCode,
                @FlowVersion,
                @FlowStatus,
                @JsonDefinition,
                @Version,
                1,
                9999,
                @CreatedById,
                GETDATE(),
                @CreatedById,
                GETDATE(),
                1
            )";

        // =====================================================
        // Update — FlowCode immutable after creation (use DELETE + INSERT to rename)
        // =====================================================
        public const string UPDATE_FLOW = @"
            UPDATE MEIPFLOWDEFINITION
            SET
                FLOWVERSION    = @FlowVersion,
                FLOWSTATUS     = @FlowStatus,
                JSONDEFINITION = @JsonDefinition,
                VERSION        = @Version,
                MODIFIEDBYID   = @ModifiedById,
                MODIFIEDON     = GETDATE()
            WHERE FLOWDEFINITIONID = @FlowDefinitionId
              AND TENANTID         = @TenantId
              AND STATUS           = 1";

        // =====================================================
        // Soft-delete — sets STATUS = 0 (preserves audit trail)
        // =====================================================
        public const string DELETE_FLOW = @"
            UPDATE MEIPFLOWDEFINITION
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETDATE()
            WHERE FLOWDEFINITIONID = @FlowDefinitionId
              AND TENANTID         = @TenantId
              AND STATUS           = 1";

        // =====================================================
        // Duplicate-check before insert
        // =====================================================
        public const string EXISTS_FLOWCODE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1
                FROM   MEIPFLOWDEFINITION
                WHERE  FLOWCODE  = @FlowCode
                  AND  TENANTID  = @TenantId
                  AND  STATUS    = 1
                  AND  (@FlowDefinitionId = 0 OR FLOWDEFINITIONID <> @FlowDefinitionId)
            ) THEN 1 ELSE 0 END";
    }
}