-- ============================================================================
-- Multi-Tenant Backstage Entity Database - Query Examples
-- ============================================================================
-- Performance targets:
-- - Entity lookup by ref: <20ms
-- - List queries: <50ms
-- - Full-text search: <100ms
-- ============================================================================

-- Set tenant context (REQUIRED for all queries with RLS enabled)
SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

-- ============================================================================
-- 1. BASIC ENTITY QUERIES
-- ============================================================================

-- Get entity by ref (most common query: 90% of traffic)
-- Expected: 5-15ms with idx_entities_ref
SELECT
    entity_ref,
    entity_kind,
    entity_name,
    final_entity,
    created_at,
    updated_at
FROM entities
WHERE entity_ref = 'component:default/payment-service'
  AND deleted_at IS NULL;

-- Get entity with function (same performance)
SELECT get_entity(
    '550e8400-e29b-41d4-a716-446655440000',
    'component:default/payment-service'
);

-- List all components
-- Expected: 20-40ms with idx_entities_kind
SELECT
    entity_ref,
    entity_name,
    final_entity ->> 'metadata' ->> 'description' AS description,
    created_at
FROM entities
WHERE entity_kind = 'component'
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 100;

-- List entities by kind (with function)
SELECT * FROM list_entities_by_kind(
    '550e8400-e29b-41d4-a716-446655440000',
    'component',
    100
);

-- List all APIs in production lifecycle
-- Expected: 30-50ms with idx_entities_json
SELECT
    entity_ref,
    entity_name,
    final_entity -> 'spec' ->> 'type' AS api_type
FROM entities
WHERE entity_kind = 'api'
  AND final_entity -> 'spec' ->> 'lifecycle' = 'production'
  AND deleted_at IS NULL
ORDER BY entity_name;

-- ============================================================================
-- 2. RELATIONSHIP QUERIES
-- ============================================================================

-- Get all relations for an entity (forward)
-- Expected: 10-20ms with idx_relations_source
SELECT
    type,
    target_entity_ref,
    created_at
FROM relations
WHERE source_entity_ref = 'component:default/payment-service'
ORDER BY type, target_entity_ref;

-- Get all entities that depend on a specific entity (backward)
-- Expected: 10-20ms with idx_relations_target
SELECT
    type,
    source_entity_ref,
    created_at
FROM relations
WHERE target_entity_ref = 'api:default/payment-api'
  AND type = 'dependsOn'
ORDER BY source_entity_ref;

-- Get entity with all its relations (using function)
-- Expected: 30-80ms
SELECT get_entity_with_relations(
    '550e8400-e29b-41d4-a716-446655440000',
    'component:default/payment-service'
);

-- Get entity with all relations (manual join)
SELECT
    e.entity_ref,
    e.entity_name,
    e.final_entity,
    jsonb_agg(
        jsonb_build_object(
            'type', r.type,
            'target', r.target_entity_ref
        )
    ) FILTER (WHERE r.relation_id IS NOT NULL) AS relations
FROM entities e
LEFT JOIN relations r ON r.source_entity_ref = e.entity_ref
WHERE e.entity_ref = 'component:default/payment-service'
  AND e.deleted_at IS NULL
GROUP BY e.entity_id, e.entity_ref, e.entity_name, e.final_entity;

-- Find all components owned by a specific team
-- Expected: 40-80ms
SELECT
    e.entity_ref,
    e.entity_name
FROM entities e
JOIN relations r ON r.source_entity_ref = e.entity_ref
WHERE e.entity_kind = 'component'
  AND r.type = 'ownedBy'
  AND r.target_entity_ref = 'group:default/team-backend'
  AND e.deleted_at IS NULL;

-- ============================================================================
-- 3. FULL-TEXT SEARCH
-- ============================================================================

-- Search entities by name (trigram similarity)
-- Expected: 50-100ms with idx_entities_name_trgm
SELECT
    entity_ref,
    entity_name,
    similarity(entity_name, 'payment') AS sim
FROM entities
WHERE entity_name % 'payment' -- Trigram similarity operator
  AND deleted_at IS NULL
ORDER BY sim DESC, entity_name
LIMIT 50;

-- Search using function
SELECT * FROM search_entities(
    '550e8400-e29b-41d4-a716-446655440000',
    'payment',
    50
);

-- Search entities by title in metadata
-- Expected: 60-120ms
SELECT
    entity_ref,
    final_entity -> 'metadata' ->> 'title' AS title,
    final_entity -> 'metadata' ->> 'description' AS description
FROM entities
WHERE final_entity -> 'metadata' ->> 'title' ILIKE '%payment%'
  AND deleted_at IS NULL
ORDER BY entity_name;

-- Search by tags (JSONB array contains)
-- Expected: 40-80ms with idx_entities_json
SELECT
    entity_ref,
    entity_name,
    final_entity -> 'metadata' -> 'tags' AS tags
FROM entities
WHERE final_entity -> 'metadata' -> 'tags' ? 'payment' -- Tag exists
  AND deleted_at IS NULL;

-- Search by multiple tags (all must match)
SELECT
    entity_ref,
    entity_name,
    final_entity -> 'metadata' -> 'tags' AS tags
FROM entities
WHERE final_entity -> 'metadata' -> 'tags' ?& ARRAY['payment', 'critical']
  AND deleted_at IS NULL;

-- ============================================================================
-- 4. AGGREGATION QUERIES
-- ============================================================================

-- Count entities by kind
-- Expected: 20-40ms
SELECT
    entity_kind,
    COUNT(*) AS entity_count
FROM entities
WHERE deleted_at IS NULL
GROUP BY entity_kind
ORDER BY entity_count DESC;

-- Count entities by kind (using function)
SELECT * FROM count_entities_by_tenant(
    '550e8400-e29b-41d4-a716-446655440000'
);

-- Count entities by lifecycle
SELECT
    final_entity -> 'spec' ->> 'lifecycle' AS lifecycle,
    COUNT(*) AS count
FROM entities
WHERE entity_kind IN ('component', 'api')
  AND deleted_at IS NULL
GROUP BY lifecycle
ORDER BY count DESC;

-- Count entities by owner
SELECT
    final_entity -> 'spec' ->> 'owner' AS owner,
    entity_kind,
    COUNT(*) AS count
FROM entities
WHERE deleted_at IS NULL
GROUP BY owner, entity_kind
ORDER BY count DESC;

-- Count relations by type
SELECT
    type,
    COUNT(*) AS relation_count
FROM relations
GROUP BY type
ORDER BY relation_count DESC;

-- ============================================================================
-- 5. TENANT MANAGEMENT QUERIES
-- ============================================================================

-- Get all tenants with entity counts
SELECT * FROM v_tenant_entity_counts;

-- Get tenant usage statistics
SELECT
    tenant_slug,
    total_entities,
    active_entities,
    deleted_entities,
    max_entities,
    usage_percent
FROM v_tenant_entity_counts
ORDER BY usage_percent DESC;

-- Find tenants exceeding 80% capacity
SELECT
    tenant_slug,
    active_entities,
    max_entities,
    usage_percent
FROM v_tenant_entity_counts
WHERE usage_percent > 80
ORDER BY usage_percent DESC;

-- ============================================================================
-- 6. DATA QUALITY QUERIES
-- ============================================================================

-- Find stale entities (not updated in 90 days)
SELECT * FROM v_stale_entities
LIMIT 100;

-- Find entities with missing required fields
SELECT
    entity_ref,
    entity_kind,
    CASE
        WHEN final_entity -> 'metadata' ->> 'title' IS NULL THEN 'Missing title'
        WHEN final_entity -> 'metadata' ->> 'description' IS NULL THEN 'Missing description'
        WHEN final_entity -> 'spec' ->> 'owner' IS NULL THEN 'Missing owner'
        ELSE 'OK'
    END AS issue
FROM entities
WHERE deleted_at IS NULL
  AND (
      final_entity -> 'metadata' ->> 'title' IS NULL OR
      final_entity -> 'metadata' ->> 'description' IS NULL OR
      final_entity -> 'spec' ->> 'owner' IS NULL
  );

-- Find orphaned entities (no incoming or outgoing relations)
SELECT
    e.entity_ref,
    e.entity_kind,
    e.entity_name
FROM entities e
WHERE e.deleted_at IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM relations r
      WHERE r.source_entity_ref = e.entity_ref OR r.target_entity_ref = e.entity_ref
  )
ORDER BY e.created_at DESC;

-- Find broken relations (target entity doesn't exist)
SELECT
    r.source_entity_ref,
    r.target_entity_ref,
    r.type
FROM relations r
WHERE NOT EXISTS (
    SELECT 1 FROM entities e
    WHERE e.entity_ref = r.target_entity_ref
      AND e.deleted_at IS NULL
);

-- ============================================================================
-- 7. REFRESH STATE QUERIES
-- ============================================================================

-- Get refresh schedule for next hour
SELECT * FROM v_refresh_schedule
WHERE next_refresh <= NOW() + INTERVAL '1 hour'
ORDER BY next_refresh;

-- Find entities with refresh errors
SELECT
    entity_ref,
    errors,
    last_refresh,
    next_refresh
FROM refresh_state
WHERE errors IS NOT NULL
ORDER BY last_refresh DESC;

-- Get entities that haven't been refreshed in 24 hours
SELECT
    entity_ref,
    last_refresh,
    NOW() - last_refresh AS overdue_duration
FROM refresh_state
WHERE last_refresh < NOW() - INTERVAL '24 hours'
ORDER BY last_refresh ASC;

-- ============================================================================
-- 8. AUDIT LOG QUERIES
-- ============================================================================

-- Get recent changes (last 7 days)
SELECT
    entity_ref,
    operation,
    changed_by,
    changed_at
FROM entity_history
WHERE changed_at >= NOW() - INTERVAL '7 days'
ORDER BY changed_at DESC
LIMIT 100;

-- Get change history for specific entity
SELECT
    operation,
    changed_by,
    changed_at,
    CASE
        WHEN operation = 'UPDATE' THEN
            jsonb_pretty(
                (SELECT jsonb_object_agg(key, value)
                 FROM jsonb_each(new_entity)
                 WHERE new_entity -> key <> old_entity -> key)
            )
        ELSE NULL
    END AS changes
FROM entity_history
WHERE entity_ref = 'component:default/payment-service'
ORDER BY changed_at DESC;

-- Find who changed an entity
SELECT
    changed_by,
    COUNT(*) AS change_count,
    MIN(changed_at) AS first_change,
    MAX(changed_at) AS last_change
FROM entity_history
WHERE entity_ref = 'component:default/payment-service'
GROUP BY changed_by
ORDER BY change_count DESC;

-- Get entities deleted in last 30 days
SELECT
    entity_ref,
    changed_by,
    changed_at,
    old_entity
FROM entity_history
WHERE operation = 'DELETE'
  AND changed_at >= NOW() - INTERVAL '30 days'
ORDER BY changed_at DESC;

-- ============================================================================
-- 9. PERFORMANCE ANALYSIS QUERIES
-- ============================================================================

-- Enable query statistics (requires pg_stat_statements extension)
-- CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    LEFT(query, 80) AS query_preview
FROM pg_stat_statements
WHERE query LIKE '%entities%'
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Check index usage
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE tablename LIKE 'entities%'
ORDER BY idx_scan DESC;

-- Find missing indexes (sequential scans on large tables)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    ROUND(100.0 * seq_tup_read / NULLIF(seq_scan, 0), 2) AS avg_seq_read
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND seq_scan > 0
  AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC;

-- Check table sizes
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename LIKE 'entities%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- ============================================================================
-- 10. BULK OPERATIONS
-- ============================================================================

-- Bulk insert entities (example: 1000 entities)
-- Use COPY or multi-row INSERT for best performance
INSERT INTO entities (tenant_id, entity_ref, entity_kind, entity_namespace, entity_name, final_entity, hash)
SELECT
    '550e8400-e29b-41d4-a716-446655440000',
    'component:default/service-' || i::TEXT,
    'component',
    'default',
    'service-' || i::TEXT,
    jsonb_build_object(
        'apiVersion', 'backstage.io/v1alpha1',
        'kind', 'Component',
        'metadata', jsonb_build_object(
            'name', 'service-' || i::TEXT,
            'namespace', 'default'
        )
    ),
    md5('service-' || i::TEXT)
FROM generate_series(1, 1000) AS i;

-- Bulk update entities (example: set lifecycle to 'production')
UPDATE entities
SET
    final_entity = jsonb_set(final_entity, '{spec,lifecycle}', '"production"'),
    updated_at = NOW()
WHERE entity_kind = 'component'
  AND final_entity -> 'spec' ->> 'lifecycle' = 'experimental'
  AND deleted_at IS NULL;

-- Bulk soft delete entities
UPDATE entities
SET deleted_at = NOW()
WHERE entity_kind = 'component'
  AND updated_at < NOW() - INTERVAL '365 days'
  AND deleted_at IS NULL;

-- ============================================================================
-- 11. CROSS-TENANT QUERIES (Admin Only - Bypasses RLS)
-- ============================================================================

-- IMPORTANT: These queries require BYPASS RLS permission
-- SET ROLE backstage_admin;

-- Global entity counts
SELECT
    t.tenant_slug,
    COUNT(e.entity_id) AS total_entities
FROM tenants t
LEFT JOIN entities e ON e.tenant_id = t.tenant_id AND e.deleted_at IS NULL
GROUP BY t.tenant_id, t.tenant_slug
ORDER BY total_entities DESC;

-- Global search across all tenants
SELECT
    t.tenant_slug,
    e.entity_ref,
    e.entity_name
FROM entities e
JOIN tenants t ON t.tenant_id = e.tenant_id
WHERE e.entity_name ILIKE '%payment%'
  AND e.deleted_at IS NULL
ORDER BY t.tenant_slug, e.entity_name
LIMIT 100;

-- RESET ROLE;

-- ============================================================================
-- 12. EXPLAIN ANALYZE EXAMPLES (Performance Testing)
-- ============================================================================

-- Test entity lookup performance
EXPLAIN (ANALYZE, BUFFERS)
SELECT final_entity
FROM entities
WHERE entity_ref = 'component:default/payment-service'
  AND deleted_at IS NULL;

-- Expected output:
-- Index Scan using idx_entities_ref on entities (cost=0.42..8.44 rows=1 width=...)
-- Execution Time: 5-15ms

-- Test list query performance
EXPLAIN (ANALYZE, BUFFERS)
SELECT entity_ref, entity_name, created_at
FROM entities
WHERE entity_kind = 'component'
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 100;

-- Expected output:
-- Index Scan using idx_entities_kind on entities (cost=0.42..100.00 rows=100 width=...)
-- Execution Time: 20-40ms

-- Test relationship query performance
EXPLAIN (ANALYZE, BUFFERS)
SELECT type, target_entity_ref
FROM relations
WHERE source_entity_ref = 'component:default/payment-service';

-- Expected output:
-- Index Scan using idx_relations_source on relations (cost=0.42..8.44 rows=5 width=...)
-- Execution Time: 10-20ms

-- ============================================================================
-- END OF QUERY EXAMPLES
-- ============================================================================
