-- ============================================================================
-- Multi-Tenant Backstage Entity Database Schema (PostgreSQL 14+)
-- ============================================================================
-- Architecture: Row-Level Security (RLS) with tenant partitioning
-- Scalability: 100-500 clients, 1M-50M entities
-- Performance: <50ms entity lookups, <100ms searches
-- ============================================================================

-- Prerequisites
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For full-text search
CREATE EXTENSION IF NOT EXISTS "pg_cron"; -- For scheduled jobs

-- ============================================================================
-- 1. TENANT MANAGEMENT
-- ============================================================================

CREATE TABLE tenants (
    tenant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_slug TEXT UNIQUE NOT NULL, -- e.g., "acme", "widgets-inc"
    tenant_name TEXT NOT NULL,

    -- Configuration
    max_entities INT DEFAULT 50000,
    entity_ttl_days INT DEFAULT 365, -- Auto-delete after 1 year

    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ, -- Soft delete

    CHECK (tenant_slug ~ '^[a-z0-9-]+$') -- URL-safe slugs
);

CREATE INDEX idx_tenants_slug ON tenants (tenant_slug) WHERE deleted_at IS NULL;

-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tenants_updated_at
    BEFORE UPDATE ON tenants
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- ============================================================================
-- 2. ENTITIES TABLE (Core Backstage Entities)
-- ============================================================================

CREATE TABLE entities (
    -- Identity
    tenant_id UUID NOT NULL,
    entity_id UUID NOT NULL DEFAULT gen_random_uuid(),
    entity_ref TEXT NOT NULL, -- Backstage format: "kind:namespace/name"

    -- Entity components (parsed from entity_ref)
    entity_kind TEXT NOT NULL, -- component, api, system, resource, etc.
    entity_namespace TEXT NOT NULL DEFAULT 'default',
    entity_name TEXT NOT NULL,

    -- Backstage entity data
    final_entity JSONB NOT NULL, -- Complete entity YAML as JSON
    hash TEXT NOT NULL, -- Content hash for change detection (SHA-256)
    stitch_ticket TEXT, -- For entity stitching during catalog refresh

    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ, -- Soft delete (NULL = active)

    -- Constraints
    PRIMARY KEY (tenant_id, entity_id),
    UNIQUE (tenant_id, entity_ref),
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,

    -- Validation
    CHECK (entity_ref = entity_kind || ':' || entity_namespace || '/' || entity_name),
    CHECK (jsonb_typeof(final_entity) = 'object')
) PARTITION BY LIST (tenant_id);

-- Default partition for new tenants
CREATE TABLE entities_default PARTITION OF entities DEFAULT;

-- Row-Level Security (RLS)
ALTER TABLE entities ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_entities ON entities
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

-- Bypass RLS for admin operations
CREATE ROLE backstage_admin;
GRANT BYPASS RLS ON entities TO backstage_admin;

-- Trigger to update updated_at
CREATE TRIGGER entities_updated_at
    BEFORE UPDATE ON entities
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- ============================================================================
-- 3. INDEXES FOR ENTITIES (Performance-Critical)
-- ============================================================================

-- Entity lookup by ref (most common query: 90% of traffic)
CREATE INDEX idx_entities_ref ON entities (tenant_id, entity_ref)
    WHERE deleted_at IS NULL;

-- Entity kind filtering (list all components, APIs, etc.)
CREATE INDEX idx_entities_kind ON entities (tenant_id, entity_kind, created_at DESC)
    WHERE deleted_at IS NULL;

-- Entity namespace filtering
CREATE INDEX idx_entities_namespace ON entities (tenant_id, entity_namespace, entity_kind)
    WHERE deleted_at IS NULL;

-- Full-text search on entity names (trigram matching)
CREATE INDEX idx_entities_name_trgm ON entities
    USING gin (entity_name gin_trgm_ops)
    WHERE deleted_at IS NULL;

-- JSONB search on entity metadata
CREATE INDEX idx_entities_json ON entities
    USING gin (final_entity jsonb_path_ops);

-- Specific JSONB paths (commonly queried fields)
CREATE INDEX idx_entities_metadata_title ON entities
    USING gin ((final_entity -> 'metadata' -> 'title') gin_trgm_ops);

CREATE INDEX idx_entities_metadata_tags ON entities
    USING gin ((final_entity -> 'metadata' -> 'tags'));

CREATE INDEX idx_entities_spec_type ON entities
    USING btree ((final_entity -> 'spec' ->> 'type'));

-- Stale entity detection (for cleanup jobs)
CREATE INDEX idx_entities_stale ON entities (tenant_id, updated_at)
    WHERE deleted_at IS NULL;

-- Hash lookup (for duplicate detection during ingestion)
CREATE INDEX idx_entities_hash ON entities (tenant_id, hash)
    WHERE deleted_at IS NULL;

-- ============================================================================
-- 4. RELATIONS TABLE (Entity Relationships)
-- ============================================================================

CREATE TABLE relations (
    -- Identity
    tenant_id UUID NOT NULL,
    relation_id UUID NOT NULL DEFAULT gen_random_uuid(),

    -- Relationship
    originating_entity_id UUID NOT NULL,
    source_entity_ref TEXT NOT NULL,
    target_entity_ref TEXT NOT NULL,
    type TEXT NOT NULL, -- ownedBy, dependsOn, apiProvidedBy, partOf, etc.

    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    PRIMARY KEY (tenant_id, relation_id),
    FOREIGN KEY (tenant_id, originating_entity_id)
        REFERENCES entities(tenant_id, entity_id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,

    -- Prevent duplicate relations
    UNIQUE (tenant_id, source_entity_ref, target_entity_ref, type)
) PARTITION BY LIST (tenant_id);

-- Default partition
CREATE TABLE relations_default PARTITION OF relations DEFAULT;

-- Row-Level Security
ALTER TABLE relations ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_relations ON relations
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

GRANT BYPASS RLS ON relations TO backstage_admin;

-- ============================================================================
-- 5. INDEXES FOR RELATIONS
-- ============================================================================

-- Forward relationships (source → target)
CREATE INDEX idx_relations_source ON relations (tenant_id, source_entity_ref);

-- Backward relationships (target ← source)
CREATE INDEX idx_relations_target ON relations (tenant_id, target_entity_ref);

-- Relation type filtering
CREATE INDEX idx_relations_type ON relations (tenant_id, type);

-- Composite index for common queries
CREATE INDEX idx_relations_source_type ON relations (tenant_id, source_entity_ref, type);

-- ============================================================================
-- 6. REFRESH_STATE TABLE (Catalog Ingestion State)
-- ============================================================================

CREATE TABLE refresh_state (
    -- Identity
    tenant_id UUID NOT NULL,
    entity_id UUID NOT NULL,
    entity_ref TEXT NOT NULL,

    -- Refresh state
    unprocessed_entity JSONB, -- Raw YAML from source (before validation)
    processed_entity JSONB, -- After validation and processing
    cache JSONB, -- Location cache (URLs, git commits)
    errors TEXT, -- Ingestion errors (NULL = success)

    -- Scheduling
    last_refresh TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    next_refresh TIMESTAMPTZ, -- When to refresh again (NULL = disabled)
    refresh_interval_sec INT DEFAULT 300, -- 5 minutes default

    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    PRIMARY KEY (tenant_id, entity_id),
    FOREIGN KEY (tenant_id, entity_id)
        REFERENCES entities(tenant_id, entity_id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
) PARTITION BY LIST (tenant_id);

-- Default partition
CREATE TABLE refresh_state_default PARTITION OF refresh_state DEFAULT;

-- Row-Level Security
ALTER TABLE refresh_state ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_refresh ON refresh_state
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

GRANT BYPASS RLS ON refresh_state TO backstage_admin;

-- ============================================================================
-- 7. INDEXES FOR REFRESH_STATE
-- ============================================================================

-- Find entities that need refreshing
CREATE INDEX idx_refresh_next ON refresh_state (tenant_id, next_refresh)
    WHERE next_refresh IS NOT NULL AND next_refresh <= NOW();

-- Lookup refresh state by entity ref
CREATE INDEX idx_refresh_ref ON refresh_state (tenant_id, entity_ref);

-- Find failed refreshes
CREATE INDEX idx_refresh_errors ON refresh_state (tenant_id, last_refresh)
    WHERE errors IS NOT NULL;

-- ============================================================================
-- 8. ENTITY_HISTORY TABLE (Audit Log)
-- ============================================================================

CREATE TABLE entity_history (
    history_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Entity identity
    tenant_id UUID NOT NULL,
    entity_id UUID NOT NULL,
    entity_ref TEXT NOT NULL,

    -- Change tracking
    operation TEXT NOT NULL, -- INSERT, UPDATE, DELETE
    old_entity JSONB, -- NULL for INSERT
    new_entity JSONB, -- NULL for DELETE
    changed_by TEXT, -- User or service account
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Optional: Change reason
    change_reason TEXT,

    -- Constraints
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE'))
) PARTITION BY RANGE (changed_at);

-- Create monthly partitions (example for 2024)
CREATE TABLE entity_history_2024_01 PARTITION OF entity_history
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE entity_history_2024_02 PARTITION OF entity_history
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- ... (create more partitions as needed)

-- Default partition for future months
CREATE TABLE entity_history_default PARTITION OF entity_history DEFAULT;

-- Row-Level Security
ALTER TABLE entity_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_history ON entity_history
    FOR SELECT
    USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

GRANT BYPASS RLS ON entity_history TO backstage_admin;

-- ============================================================================
-- 9. INDEXES FOR ENTITY_HISTORY
-- ============================================================================

-- Lookup history for specific entity
CREATE INDEX idx_history_entity ON entity_history (tenant_id, entity_id, changed_at DESC);

-- Lookup history by entity ref
CREATE INDEX idx_history_ref ON entity_history (tenant_id, entity_ref, changed_at DESC);

-- Audit log queries (who changed what when)
CREATE INDEX idx_history_user ON entity_history (tenant_id, changed_by, changed_at DESC);

-- Operation filtering
CREATE INDEX idx_history_operation ON entity_history (tenant_id, operation, changed_at DESC);

-- ============================================================================
-- 10. TRIGGERS FOR AUDIT LOGGING
-- ============================================================================

CREATE OR REPLACE FUNCTION log_entity_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO entity_history (tenant_id, entity_id, entity_ref, operation, new_entity, changed_by)
        VALUES (NEW.tenant_id, NEW.entity_id, NEW.entity_ref, 'INSERT', NEW.final_entity, current_user);

    ELSIF TG_OP = 'UPDATE' THEN
        -- Only log if entity content changed (not just updated_at)
        IF OLD.final_entity IS DISTINCT FROM NEW.final_entity THEN
            INSERT INTO entity_history (tenant_id, entity_id, entity_ref, operation, old_entity, new_entity, changed_by)
            VALUES (NEW.tenant_id, NEW.entity_id, NEW.entity_ref, 'UPDATE', OLD.final_entity, NEW.final_entity, current_user);
        END IF;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO entity_history (tenant_id, entity_id, entity_ref, operation, old_entity, changed_by)
        VALUES (OLD.tenant_id, OLD.entity_id, OLD.entity_ref, 'DELETE', OLD.final_entity, current_user);
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER entity_changes_trigger
    AFTER INSERT OR UPDATE OR DELETE ON entities
    FOR EACH ROW
    EXECUTE FUNCTION log_entity_changes();

-- ============================================================================
-- 11. AUTOMATED PARTITION CREATION FOR TENANTS
-- ============================================================================

CREATE OR REPLACE FUNCTION create_tenant_partitions()
RETURNS TRIGGER AS $$
DECLARE
    tenant_slug_safe TEXT;
BEGIN
    -- Sanitize tenant slug for table names
    tenant_slug_safe := regexp_replace(NEW.tenant_slug, '[^a-z0-9_]', '_', 'g');

    -- Create entities partition
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS entities_tenant_%s PARTITION OF entities FOR VALUES IN (%L)',
        tenant_slug_safe,
        NEW.tenant_id
    );

    -- Create relations partition
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS relations_tenant_%s PARTITION OF relations FOR VALUES IN (%L)',
        tenant_slug_safe,
        NEW.tenant_id
    );

    -- Create refresh_state partition
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS refresh_state_tenant_%s PARTITION OF refresh_state FOR VALUES IN (%L)',
        tenant_slug_safe,
        NEW.tenant_id
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tenant_partition_trigger
    AFTER INSERT ON tenants
    FOR EACH ROW
    EXECUTE FUNCTION create_tenant_partitions();

-- ============================================================================
-- 12. STALE ENTITY CLEANUP (Scheduled Job)
-- ============================================================================

CREATE OR REPLACE FUNCTION cleanup_stale_entities()
RETURNS TABLE(tenant_id UUID, deleted_count INT) AS $$
DECLARE
    tenant_rec RECORD;
    deleted INT;
BEGIN
    FOR tenant_rec IN SELECT t.tenant_id, t.entity_ttl_days FROM tenants t WHERE t.deleted_at IS NULL LOOP
        -- Soft delete entities not updated within TTL
        UPDATE entities
        SET deleted_at = NOW()
        WHERE tenant_id = tenant_rec.tenant_id
          AND updated_at < NOW() - (tenant_rec.entity_ttl_days || ' days')::INTERVAL
          AND deleted_at IS NULL;

        GET DIAGNOSTICS deleted = ROW_COUNT;

        tenant_id := tenant_rec.tenant_id;
        deleted_count := deleted;

        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Schedule cleanup daily at 2 AM UTC (requires pg_cron extension)
-- SELECT cron.schedule('cleanup-stale-entities', '0 2 * * *', 'SELECT cleanup_stale_entities()');

-- ============================================================================
-- 13. HARD DELETE SOFT-DELETED ENTITIES (Scheduled Job)
-- ============================================================================

CREATE OR REPLACE FUNCTION purge_deleted_entities()
RETURNS INT AS $$
DECLARE
    deleted INT;
BEGIN
    -- Hard delete entities that have been soft-deleted for 365 days
    DELETE FROM entities
    WHERE deleted_at < NOW() - INTERVAL '365 days';

    GET DIAGNOSTICS deleted = ROW_COUNT;
    RETURN deleted;
END;
$$ LANGUAGE plpgsql;

-- Schedule purge weekly on Sundays at 3 AM UTC
-- SELECT cron.schedule('purge-deleted-entities', '0 3 * * 0', 'SELECT purge_deleted_entities()');

-- ============================================================================
-- 14. UTILITY FUNCTIONS
-- ============================================================================

-- Get entity by ref (with tenant context)
CREATE OR REPLACE FUNCTION get_entity(p_tenant_id UUID, p_entity_ref TEXT)
RETURNS JSONB AS $$
DECLARE
    entity_data JSONB;
BEGIN
    SELECT final_entity INTO entity_data
    FROM entities
    WHERE tenant_id = p_tenant_id
      AND entity_ref = p_entity_ref
      AND deleted_at IS NULL;

    RETURN entity_data;
END;
$$ LANGUAGE plpgsql STABLE;

-- List entities by kind
CREATE OR REPLACE FUNCTION list_entities_by_kind(p_tenant_id UUID, p_kind TEXT, p_limit INT DEFAULT 100)
RETURNS TABLE(entity_ref TEXT, entity_name TEXT, created_at TIMESTAMPTZ) AS $$
BEGIN
    RETURN QUERY
    SELECT e.entity_ref, e.entity_name, e.created_at
    FROM entities e
    WHERE e.tenant_id = p_tenant_id
      AND e.entity_kind = p_kind
      AND e.deleted_at IS NULL
    ORDER BY e.created_at DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql STABLE;

-- Get entity with all relations
CREATE OR REPLACE FUNCTION get_entity_with_relations(p_tenant_id UUID, p_entity_ref TEXT)
RETURNS JSONB AS $$
DECLARE
    result JSONB;
BEGIN
    SELECT jsonb_build_object(
        'entity', e.final_entity,
        'relations', COALESCE(
            jsonb_agg(
                jsonb_build_object(
                    'type', r.type,
                    'target', r.target_entity_ref
                )
            ) FILTER (WHERE r.relation_id IS NOT NULL),
            '[]'::jsonb
        )
    ) INTO result
    FROM entities e
    LEFT JOIN relations r ON r.tenant_id = e.tenant_id AND r.source_entity_ref = e.entity_ref
    WHERE e.tenant_id = p_tenant_id
      AND e.entity_ref = p_entity_ref
      AND e.deleted_at IS NULL
    GROUP BY e.entity_id, e.final_entity;

    RETURN result;
END;
$$ LANGUAGE plpgsql STABLE;

-- Search entities by name (full-text search)
CREATE OR REPLACE FUNCTION search_entities(p_tenant_id UUID, p_query TEXT, p_limit INT DEFAULT 50)
RETURNS TABLE(entity_ref TEXT, entity_name TEXT, similarity REAL) AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.entity_ref,
        e.entity_name,
        similarity(e.entity_name, p_query) AS sim
    FROM entities e
    WHERE e.tenant_id = p_tenant_id
      AND e.deleted_at IS NULL
      AND e.entity_name % p_query -- Trigram similarity operator
    ORDER BY sim DESC, e.entity_name
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql STABLE;

-- Count entities by tenant
CREATE OR REPLACE FUNCTION count_entities_by_tenant(p_tenant_id UUID)
RETURNS TABLE(entity_kind TEXT, entity_count BIGINT) AS $$
BEGIN
    RETURN QUERY
    SELECT e.entity_kind, COUNT(*) AS entity_count
    FROM entities e
    WHERE e.tenant_id = p_tenant_id
      AND e.deleted_at IS NULL
    GROUP BY e.entity_kind
    ORDER BY entity_count DESC;
END;
$$ LANGUAGE plpgsql STABLE;

-- ============================================================================
-- 15. PERFORMANCE MONITORING VIEWS
-- ============================================================================

-- View: Entity counts per tenant
CREATE OR REPLACE VIEW v_tenant_entity_counts AS
SELECT
    t.tenant_id,
    t.tenant_slug,
    t.tenant_name,
    COUNT(e.entity_id) AS total_entities,
    COUNT(e.entity_id) FILTER (WHERE e.deleted_at IS NOT NULL) AS deleted_entities,
    COUNT(e.entity_id) FILTER (WHERE e.deleted_at IS NULL) AS active_entities,
    t.max_entities,
    ROUND(100.0 * COUNT(e.entity_id) FILTER (WHERE e.deleted_at IS NULL) / t.max_entities, 2) AS usage_percent
FROM tenants t
LEFT JOIN entities e ON e.tenant_id = t.tenant_id
WHERE t.deleted_at IS NULL
GROUP BY t.tenant_id, t.tenant_slug, t.tenant_name, t.max_entities;

-- View: Stale entities (not updated in 90 days)
CREATE OR REPLACE VIEW v_stale_entities AS
SELECT
    t.tenant_slug,
    e.entity_ref,
    e.entity_kind,
    e.updated_at,
    NOW() - e.updated_at AS stale_duration
FROM entities e
JOIN tenants t ON t.tenant_id = e.tenant_id
WHERE e.deleted_at IS NULL
  AND e.updated_at < NOW() - INTERVAL '90 days'
ORDER BY e.updated_at ASC;

-- View: Entity refresh schedule
CREATE OR REPLACE VIEW v_refresh_schedule AS
SELECT
    t.tenant_slug,
    r.entity_ref,
    r.last_refresh,
    r.next_refresh,
    r.refresh_interval_sec,
    r.errors
FROM refresh_state r
JOIN tenants t ON t.tenant_id = r.tenant_id
WHERE r.next_refresh IS NOT NULL
ORDER BY r.next_refresh ASC;

-- ============================================================================
-- 16. SAMPLE DATA (For Testing)
-- ============================================================================

-- Create sample tenant
INSERT INTO tenants (tenant_id, tenant_slug, tenant_name)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'acme', 'ACME Corporation');

-- Create partitions for sample tenant
SELECT create_tenant_partitions() FROM tenants WHERE tenant_slug = 'acme';

-- Insert sample entities
INSERT INTO entities (tenant_id, entity_ref, entity_kind, entity_namespace, entity_name, final_entity, hash) VALUES
(
    '550e8400-e29b-41d4-a716-446655440000',
    'component:default/payment-service',
    'component',
    'default',
    'payment-service',
    '{
        "apiVersion": "backstage.io/v1alpha1",
        "kind": "Component",
        "metadata": {
            "name": "payment-service",
            "namespace": "default",
            "title": "Payment Service",
            "description": "Handles payment processing",
            "tags": ["payment", "backend", "critical"]
        },
        "spec": {
            "type": "service",
            "lifecycle": "production",
            "owner": "team-backend"
        }
    }'::jsonb,
    'abc123hash'
),
(
    '550e8400-e29b-41d4-a716-446655440000',
    'api:default/payment-api',
    'api',
    'default',
    'payment-api',
    '{
        "apiVersion": "backstage.io/v1alpha1",
        "kind": "API",
        "metadata": {
            "name": "payment-api",
            "namespace": "default",
            "title": "Payment API",
            "description": "RESTful API for payments"
        },
        "spec": {
            "type": "openapi",
            "lifecycle": "production",
            "owner": "team-backend",
            "definition": "..."
        }
    }'::jsonb,
    'def456hash'
);

-- Insert sample relations
INSERT INTO relations (tenant_id, originating_entity_id, source_entity_ref, target_entity_ref, type)
SELECT
    '550e8400-e29b-41d4-a716-446655440000',
    e.entity_id,
    'component:default/payment-service',
    'api:default/payment-api',
    'apiProvidedBy'
FROM entities e
WHERE e.tenant_id = '550e8400-e29b-41d4-a716-446655440000'
  AND e.entity_ref = 'component:default/payment-service';

-- ============================================================================
-- 17. GRANTS AND PERMISSIONS
-- ============================================================================

-- Create application role (used by Backstage API)
CREATE ROLE backstage_app;

-- Grant read/write access to tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO backstage_app;

-- Grant usage on sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO backstage_app;

-- Grant execute on functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO backstage_app;

-- Read-only role (for analytics/reporting)
CREATE ROLE backstage_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backstage_readonly;
GRANT EXECUTE ON FUNCTION get_entity(UUID, TEXT) TO backstage_readonly;
GRANT EXECUTE ON FUNCTION list_entities_by_kind(UUID, TEXT, INT) TO backstage_readonly;

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================
