namespace EAIAdminDAL.Query
{
    // INDEX required on EAI.MEAICAPABILITY:
    //   IX_MEAICAPABILITY_TENANTID_STATUS on (TENANTID, STATUS) INCLUDE (CAPABILITYCODE, CAPABILITYNAME)
    //
    // Dialect-neutral by construction (GB5's ConvertSqlToPostgres only rewrites TotalCountSql, not
    // these paths — see QueryExecutor.cs): no GETDATE()/NOW() in SQL text (CreatedOn/ModifiedOn are
    // DAL-computed DateTime.UtcNow parameters), no T-SQL '+' string concatenation for the search
    // wildcard (built in C# as a plain @SearchPattern parameter, plain LIKE on both dialects).
    public static class EAICapabilityAdminQB
    {
        public const string GET_CAPABILITY_LIST = @"
            SELECT
                A.AICAPABILITYID    AS CapabilityId,
                A.TENANTID          AS TenantId,
                A.CAPABILITYCODE    AS CapabilityCode,
                A.CAPABILITYNAME    AS CapabilityName,
                A.MAXMATURITYLEVEL  AS MaxMaturityLevel,
                A.KEYWORDS          AS Keywords,
                A.EAIREMOTEID       AS EAIRemoteId,
                A.VERSION           AS Version,
                A.STATUS            AS Status,
                A.CREATEDBYID       AS CreatedById,
                CB.EMPLOYEENAME     AS CreatedByName,
                A.CREATEDON         AS CreatedOn,
                A.MODIFIEDBYID      AS ModifiedById,
                MB.EMPLOYEENAME     AS ModifiedByName,
                A.MODIFIEDON        AS ModifiedOn
            FROM EAI.MEAICAPABILITY A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.TENANTID = @TenantId
              AND A.STATUS   = 1
              AND (
                    @SearchPattern IS NULL
                    OR A.CAPABILITYCODE LIKE @SearchPattern
                    OR A.CAPABILITYNAME LIKE @SearchPattern
                  )
            ORDER BY A.CAPABILITYCODE ASC";

        public const string GET_CAPABILITY = @"
            SELECT
                A.AICAPABILITYID    AS CapabilityId,
                A.TENANTID          AS TenantId,
                A.CAPABILITYCODE    AS CapabilityCode,
                A.CAPABILITYNAME    AS CapabilityName,
                A.MAXMATURITYLEVEL  AS MaxMaturityLevel,
                A.KEYWORDS          AS Keywords,
                A.EAIREMOTEID       AS EAIRemoteId,
                A.VERSION           AS Version,
                A.STATUS            AS Status,
                A.CREATEDBYID       AS CreatedById,
                CB.EMPLOYEENAME     AS CreatedByName,
                A.CREATEDON         AS CreatedOn,
                A.MODIFIEDBYID      AS ModifiedById,
                MB.EMPLOYEENAME     AS ModifiedByName,
                A.MODIFIEDON        AS ModifiedOn
            FROM EAI.MEAICAPABILITY A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.AICAPABILITYID = @CapabilityId
              AND A.TENANTID       = @TenantId
              AND A.STATUS         = 1";

        // AICAPABILITYID is a plain INT PK (NOT IDENTITY) — generated via AutoNumber, same
        // convention as MEAIINITIATIVE/AIINITIATIVEID.
        public const string INSERT_CAPABILITY = @"
            INSERT INTO EAI.MEAICAPABILITY (
                AICAPABILITYID, TENANTID, CAPABILITYCODE, CAPABILITYNAME, MAXMATURITYLEVEL,
                KEYWORDS, EAIREMOTEID, VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE
            )
            VALUES (
                @CapabilityId, @TenantId, @CapabilityCode, @CapabilityName, @MaxMaturityLevel,
                @Keywords, @EAIRemoteId, @Version, 1, 9999,
                @CreatedById, @CreatedOn, @CreatedById, @CreatedOn, 1
            )";

        public const string UPDATE_CAPABILITY = @"
            UPDATE EAI.MEAICAPABILITY
            SET
                CAPABILITYNAME   = @CapabilityName,
                MAXMATURITYLEVEL = @MaxMaturityLevel,
                KEYWORDS         = @Keywords,
                EAIREMOTEID      = @EAIRemoteId,
                VERSION          = @Version,
                MODIFIEDBYID     = @ModifiedById,
                MODIFIEDON       = @ModifiedOn
            WHERE AICAPABILITYID = @CapabilityId
              AND TENANTID       = @TenantId
              AND STATUS         = 1";
        // NOTE: CAPABILITYCODE is deliberately not in the SET list — code is immutable once
        // created, same convention as gbEAI's own admin routes will use.

        public const string UPDATE_EAIREMOTEID = @"
            UPDATE EAI.MEAICAPABILITY
            SET EAIREMOTEID = @EAIRemoteId
            WHERE AICAPABILITYID = @CapabilityId
              AND TENANTID       = @TenantId";

        public const string DELETE_CAPABILITY = @"
            UPDATE EAI.MEAICAPABILITY
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE AICAPABILITYID = @CapabilityId
              AND TENANTID       = @TenantId
              AND STATUS         = 1";

        // Duplicate-check before insert — CAPABILITYCODE is globally unique (matches gbEAI's
        // unique=True on aicapabilitycode), not per-tenant, so this deliberately does NOT filter
        // by TenantId.
        public const string EXISTS_CAPABILITYCODE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1
                FROM   EAI.MEAICAPABILITY
                WHERE  CAPABILITYCODE = @CapabilityCode
                  AND  STATUS         = 1
                  AND  (@CapabilityId = 0 OR AICAPABILITYID <> @CapabilityId)
            ) THEN 1 ELSE 0 END";
    }
}