namespace AnalyticsDAL.Query.BICatalog
{
    public static class BICatalogQB
    {
        // Unscoped by TenantId in the WHERE clause (only PK) — DatasetResolver needs to see a
        // shared (TENANTID = -1) catalog entry regardless of caller tenant, then compare
        // TenantId in code (same rationale as WorkspaceQB.GET_WORKSPACE_TENANTID).
        // Index: PK_MBICATALOG_BICATALOGID
        public const string GET_CATALOG_ENTRY_BY_ID = @"
            SELECT  c.BICATALOGID          AS BICatalogId,
                    c.CATALOGCODE          AS CatalogCode,
                    c.CATALOGNAME          AS CatalogName,
                    c.DATASETKIND          AS DatasetKind,
                    c.SOURCEREFID          AS SourceRefId,
                    c.REPORTVIEWID         AS ReportViewId,
                    c.APIRESOURCEPATH      AS ApiResourcePath,
                    c.WORKSPACEID          AS WorkspaceId,
                    c.TENANTID             AS TenantId,
                    c.BUSINESSDESCRIPTION  AS BusinessDescription,
                    c.SYNONYMS             AS Synonyms,
                    c.STATUS               AS Status
            FROM    MBICATALOG c
            WHERE   c.BICATALOGID = @BICatalogId
            AND     c.STATUS     <> 2";

        // List version of GET_CATALOG_ENTRY_BY_ID, for the config-UI dataset picker. Filtered to
        // Warehouse-kind (0), AnalysisQuery-kind (1), and ApiService-kind (2) — all three now
        // have a real dimension/measure picker wired in BICatalogBLL (AnalysisQuery-kind as of
        // Phase 2's MANALYSISQUERYFIELDS translation), so all three belong in the dropdown.
        // AnalysisQuery-kind rows still only exist if something creates an MBICATALOG entry
        // pointing at a MANALYSISQUERY (SourceRefId) — nothing does that automatically; the
        // legacy ad-hoc designer itself is untouched and doesn't populate MBICATALOG.
        // TenantId filtered here (unlike the single-entry lookup above) since a LIST naturally
        // excludes other tenants' private rows up front; per-row workspace visibility is still
        // checked in BLL code, same as DatasetResolver does for a single entry.
        // Index: IX_MBICATALOG_TENANT_KIND
        public const string GET_CATALOG_LIST = @"
            SELECT  c.BICATALOGID          AS BICatalogId,
                    c.CATALOGCODE          AS CatalogCode,
                    c.CATALOGNAME          AS CatalogName,
                    c.DATASETKIND          AS DatasetKind,
                    c.SOURCEREFID          AS SourceRefId,
                    c.REPORTVIEWID         AS ReportViewId,
                    c.APIRESOURCEPATH      AS ApiResourcePath,
                    c.WORKSPACEID          AS WorkspaceId,
                    c.TENANTID             AS TenantId,
                    c.BUSINESSDESCRIPTION  AS BusinessDescription,
                    c.SYNONYMS             AS Synonyms,
                    c.STATUS               AS Status
            FROM    MBICATALOG c
            WHERE   c.TENANTID IN (@TenantId, -1)
            AND     c.DATASETKIND IN (0, 1, 2)
            AND     c.STATUS     <> 2";

        public const string INSERT_CATALOG = @"
            INSERT INTO MBICATALOG
                (BICATALOGID, CATALOGCODE, CATALOGNAME, DATASETKIND, SOURCEREFID,
                 REPORTVIEWID, APIRESOURCEPATH, WORKSPACEID, TENANTID,
                 BUSINESSDESCRIPTION, SYNONYMS,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BICatalogId, @CatalogCode, @CatalogName, @DatasetKind, @SourceRefId,
                 @ReportViewId, @ApiResourcePath, @WorkspaceId, @TenantId,
                 @BusinessDescription, @Synonyms,
                 0, 1, 9999, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        // Phase 2's "create a BI catalog from any existing report" flow — looks up the report
        // view + its owning report's calling URI in one shot, so BICatalogBLL.CreateFromReportView
        // can gate on ISDATAPIVOT before ever touching MBICATALOG. Deliberately lean (not the
        // full ReportViewQB.GET_REPORTVIEW projection, which carries ~30 unrelated columns this
        // flow doesn't need) — a new, narrow query, not a modification of the shared, heavily-
        // used Framework ReportViewQB.cs.
        // LEFT JOIN MWEBSERVICE: most real reports leave MREPORT.REPORTURI = 'NONE' and carry
        // their actual backing-service address on WEBSERVICEID instead (confirmed live on
        // GB5DEMO — 1080/1083 reports have REPORTURI='NONE', but 1055/1085 have a real
        // WEBSERVICEID). BICatalogBLL.CreateFromReportView falls back to WebServiceUriTemplate
        // when ReportUri is empty, but only accepts it if it isn't a legacy/templated GB4
        // endpoint (see that method's own comment) — most WEBSERVICEID rows on GB5DEMO are.
        public const string GET_REPORTVIEW_FOR_CATALOG_CREATION = @"
            SELECT  rv.REPORTVIEWID       AS ReportViewId,
                    rv.ISDATAPIVOT        AS IsDataPivot,
                    rv.REPORTVIEWNAME     AS ReportViewName,
                    r.REPORTID            AS ReportId,
                    r.REPORTNAME          AS ReportName,
                    r.REPORTURI           AS ReportUri,
                    r.WEBSERVICEID        AS WebServiceId,
                    ws.WEBSERVICENAME     AS WebServiceName,
                    ws.URITEMPLATE        AS WebServiceUriTemplate,
                    ws.SECONDURITEMPLATE  AS WebServiceSecondUriTemplate
            FROM    MREPORTVIEW rv
            JOIN    MREPORT r ON r.REPORTID = rv.REPORTID
            LEFT JOIN MWEBSERVICE ws ON ws.WEBSERVICEID = r.WEBSERVICEID
            WHERE   rv.REPORTVIEWID = @ReportViewId
            AND     rv.STATUS      <> 2";
    }
}