namespace AnalyticsDAL.Query.DataSource
{
    public static class DataSourceQB
    {
        // Index: PK_MDATASOURCE_DATASOURCEID, UK_MDATASOURCE_DATASOURCECODE_TENANTID
        public const string GET_DATASOURCE = @"
            SELECT  d.DATASOURCEID          AS DataSourceId,
                    d.DATASOURCECODE        AS DataSourceCode,
                    d.DATASOURCENAME        AS DataSourceName,
                    d.DATASOURCETYPE        AS DataSourceType,
                    d.CONNECTIONTYPE        AS ConnectionType,
                    d.HOSTNAME              AS HostName,
                    d.PORT                  AS Port,
                    d.DATABASENAME          AS DatabaseName,
                    d.SCHEMANAME            AS SchemaName,
                    d.CREDENTIALREF         AS CredentialRef,
                    d.REFRESHTYPE           AS RefreshType,
                    d.ACCESSMODE            AS AccessMode,
                    d.ISVALIDATED           AS IsValidated,
                    d.LASTVALIDATEDON       AS LastValidatedOn,
                    d.WORKSPACEID           AS WorkspaceId,
                    d.REMARKS               AS Remarks,
                    d.SWCLIENTDATABASEID    AS SwClientDatabaseId,
                    d.VERSION               AS Version,
                    d.STATUS                AS Status,
                    d.SORTORDER             AS SortOrder,
                    d.SOURCETYPE            AS SourceType,
                    d.TENANTID              AS TenantId,
                    d.CREATEDBYID           AS CreatedById,
                    d.CREATEDON             AS CreatedOn,
                    cb.USERNAME             AS CreatedByName,
                    d.MODIFIEDBYID          AS ModifiedById,
                    d.MODIFIEDON            AS ModifiedOn,
                    mb.USERNAME             AS ModifiedByName
            FROM    MDATASOURCE d
            LEFT JOIN MUSER cb ON cb.USERID = d.CREATEDBYID
            LEFT JOIN MUSER mb ON mb.USERID = d.MODIFIEDBYID
            WHERE   d.DATASOURCEID = @DataSourceId
            AND     d.TENANTID     IN (@TenantId, -1)
            AND     d.STATUS      <> 2";

        // Index: IX_MDATASOURCE_CONNECTIONTYPE_TENANTID
        public const string GET_SELECTLIST_DATASOURCE = @"
            SELECT  d.DATASOURCEID      AS DataSourceId,
                    d.DATASOURCECODE    AS DataSourceCode,
                    d.DATASOURCENAME    AS DataSourceName,
                    d.DATASOURCETYPE    AS DataSourceType,
                    d.CONNECTIONTYPE    AS ConnectionType
            FROM    MDATASOURCE d
            WHERE   d.TENANTID  = @TenantId
            AND     d.STATUS   <> 2
            ORDER BY d.SORTORDER, d.DATASOURCENAME";

        public const string CHECK_DUPLICATE_CODE = @"
            SELECT COUNT(1)
            FROM   MDATASOURCE
            WHERE  DATASOURCECODE = @DataSourceCode
            AND    TENANTID       = @TenantId
            AND    STATUS        <> 2
            AND    DATASOURCEID  <> @DataSourceId";

        public const string SAVE_DATASOURCE = @"
            INSERT INTO MDATASOURCE
                (DATASOURCEID, DATASOURCECODE, DATASOURCENAME, DATASOURCETYPE,
                 CONNECTIONTYPE, HOSTNAME, PORT, DATABASENAME, SCHEMANAME,
                 CREDENTIALREF, REFRESHTYPE, ACCESSMODE, ISVALIDATED,
                 WORKSPACEID, REMARKS, SWCLIENTDATABASEID,
                 VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@DataSourceId, @DataSourceCode, @DataSourceName, @DataSourceType,
                 @ConnectionType, @HostName, @Port, @DatabaseName, @SchemaName,
                 @CredentialRef, @RefreshType, @AccessMode, 1,
                 @WorkspaceId, @Remarks, @SwClientDatabaseId,
                 0, 1, @SortOrder, @SourceType, @TenantId,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        public const string UPDATE_DATASOURCE = @"
            UPDATE MDATASOURCE
            SET    DATASOURCECODE  = @DataSourceCode,
                   DATASOURCENAME  = @DataSourceName,
                   DATASOURCETYPE  = @DataSourceType,
                   CONNECTIONTYPE  = @ConnectionType,
                   HOSTNAME        = @HostName,
                   PORT            = @Port,
                   DATABASENAME    = @DatabaseName,
                   SCHEMANAME      = @SchemaName,
                   CREDENTIALREF   = @CredentialRef,
                   REFRESHTYPE     = @RefreshType,
                   ACCESSMODE      = @AccessMode,
                   WORKSPACEID     = @WorkspaceId,
                   REMARKS         = @Remarks,
                   SWCLIENTDATABASEID = @SwClientDatabaseId,
                   SORTORDER       = @SortOrder,
                   STATUS          = @Status,
                   VERSION         = VERSION + 1,
                   MODIFIEDBYID    = @ModifiedById,
                   MODIFIEDON      = GETDATE()
            WHERE  DATASOURCEID    = @DataSourceId
            AND    TENANTID        = @TenantId";

        public const string UPDATE_VALIDATION_STATUS = @"
            UPDATE MDATASOURCE
            SET    ISVALIDATED     = @IsValidated,
                   LASTVALIDATEDON = GETDATE(),
                   MODIFIEDBYID    = @ModifiedById,
                   MODIFIEDON      = GETDATE()
            WHERE  DATASOURCEID    = @DataSourceId
            AND    TENANTID        = @TenantId";

        public const string DELETE_DATASOURCE = @"
            UPDATE MDATASOURCE
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  DATASOURCEID = @DataSourceId
            AND    TENANTID     = @TenantId";

        // Unscoped by design (no TENANTID filter) — used only by the ownership visibility
        // guard; see WorkspaceQB.GET_WORKSPACE_TENANTID for why GET_DATASOURCE can't be reused here.
        public const string GET_DATASOURCE_TENANTID = @"
            SELECT  d.TENANTID
            FROM    MDATASOURCE d
            WHERE   d.DATASOURCEID = @DataSourceId
            AND     d.STATUS      <> 2";

        // Code-based lookup rather than a hardcoded id — used by BICatalogBLL.CreateFromReportView
        // to find the shared, generic "this GB5 host, reachable for any of its own reports"
        // MDATASOURCE row (DATASOURCECODE = 'GB5_SELF_HOST') without assuming a fixed id, since
        // the seeded id can differ per environment. Unscoped by TenantId — this row is shared
        // (TENANTID = -1) by design, same sentinel as everywhere else in this module.
        public const string GET_DATASOURCE_ID_BY_CODE = @"
            SELECT  d.DATASOURCEID
            FROM    MDATASOURCE d
            WHERE   d.DATASOURCECODE = @DataSourceCode
            AND     d.STATUS        <> 2";
    }
}