namespace EAIAdminDAL.Query
{
    // INDEX required on EAI.MEAIMODELCONFIG:
    //   UK_MEAIMODELCONFIG_TENANT_MODELCODE ON (TENANTID, MODELCODE) WHERE STATUS = 1 (unique)
    //
    // 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 EAIModelConfigAdminQB
    {
        public const string GET_MODELCONFIG_LIST = @"
            SELECT
                A.AIMODELID         AS ModelId,
                A.TENANTID          AS TenantId,
                A.MODELCODE         AS ModelCode,
                A.MODELNAME         AS ModelName,
                A.PROVIDER          AS Provider,
                A.ENDPOINT          AS Endpoint,
                A.APIKEYVAULTKEY    AS ApiKeyVaultKey,
                A.ISACTIVE          AS IsActive,
                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.MEAIMODELCONFIG 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.MODELCODE LIKE @SearchPattern
                    OR A.MODELNAME LIKE @SearchPattern
                  )
            ORDER BY A.MODELCODE ASC";

        public const string GET_MODELCONFIG = @"
            SELECT
                A.AIMODELID         AS ModelId,
                A.TENANTID          AS TenantId,
                A.MODELCODE         AS ModelCode,
                A.MODELNAME         AS ModelName,
                A.PROVIDER          AS Provider,
                A.ENDPOINT          AS Endpoint,
                A.APIKEYVAULTKEY    AS ApiKeyVaultKey,
                A.ISACTIVE          AS IsActive,
                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.MEAIMODELCONFIG A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.AIMODELID = @ModelId
              AND A.TENANTID  = @TenantId
              AND A.STATUS    = 1";

        // AIMODELID is a plain INT PK (NOT IDENTITY) — generated via AutoNumber, same convention as
        // MEAIINITIATIVE/AIINITIATIVEID.
        public const string INSERT_MODELCONFIG = @"
            INSERT INTO EAI.MEAIMODELCONFIG (
                AIMODELID, TENANTID, MODELCODE, MODELNAME, PROVIDER, ENDPOINT, APIKEYVAULTKEY,
                ISACTIVE, EAIREMOTEID, VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE
            )
            VALUES (
                @ModelId, @TenantId, @ModelCode, @ModelName, @Provider, @Endpoint, @ApiKeyVaultKey,
                @IsActive, @EAIRemoteId, @Version, 1, 9999,
                @CreatedById, @CreatedOn, @CreatedById, @CreatedOn, 1
            )";

        // APIKEYVAULTKEY is deliberately not in the SET list — it is never overwritten by this
        // table's own Save endpoint once set; changed only via the separate Vault "Set API Key" UI
        // action. MODELCODE is also excluded — code is immutable once created, same convention as
        // MEAIINITIATIVE/INITIATIVECODE.
        public const string UPDATE_MODELCONFIG = @"
            UPDATE EAI.MEAIMODELCONFIG
            SET
                MODELNAME    = @ModelName,
                PROVIDER     = @Provider,
                ENDPOINT     = @Endpoint,
                ISACTIVE     = @IsActive,
                EAIREMOTEID  = @EAIRemoteId,
                VERSION      = @Version,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE AIMODELID = @ModelId
              AND TENANTID  = @TenantId
              AND STATUS    = 1";

        public const string UPDATE_EAIREMOTEID = @"
            UPDATE EAI.MEAIMODELCONFIG
            SET EAIREMOTEID = @EAIRemoteId
            WHERE AIMODELID = @ModelId
              AND TENANTID  = @TenantId";

        public const string DELETE_MODELCONFIG = @"
            UPDATE EAI.MEAIMODELCONFIG
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE AIMODELID = @ModelId
              AND TENANTID  = @TenantId
              AND STATUS    = 1";

        // Duplicate-check before insert — MODELCODE is unique per (TenantId, ModelCode), matching
        // UK_MEAIMODELCONFIG_TENANT_MODELCODE — unlike MEAIINITIATIVE this DOES filter by TenantId.
        public const string EXISTS_MODELCODE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1
                FROM   EAI.MEAIMODELCONFIG
                WHERE  TENANTID  = @TenantId
                  AND  MODELCODE = @ModelCode
                  AND  STATUS    = 1
                  AND  (@ModelId = 0 OR AIMODELID <> @ModelId)
            ) THEN 1 ELSE 0 END";
    }
}