-- ============================================================================
-- Genesis Knowledge Graph Schema Migration
-- ============================================================================
-- Version: 001
-- Created: 2026-01-24
-- Description: Initial knowledge graph schema with 6 entity types
--
-- Entity Types:
--   - kg_items: Physical/conceptual objects
--   - kg_skills: Capabilities, functions, tools
--   - kg_knowledge: Facts, documentation, insights
--   - kg_learnings: Experiences, outcomes, reflections
--   - kg_axioms: Validated truths, principles, rules
--   - kg_relationships: Directed edges connecting entities
--
-- Features:
--   ✓ UUID primary keys
--   ✓ JSONB metadata fields
--   ✓ Vector embedding references (Qdrant)
--   ✓ Soft delete support
--   ✓ Versioning
--   ✓ Comprehensive indexes
--   ✓ Audit timestamps
--
-- RULE 6 COMPLIANT: PostgreSQL only, no SQLite
-- ============================================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================================
-- ENUM TYPES
-- ============================================================================

-- Entity types
CREATE TYPE entity_type AS ENUM (
    'item',
    'skill',
    'knowledge',
    'relationship',
    'learning',
    'axiom'
);

-- Relationship types
CREATE TYPE relationship_type AS ENUM (
    -- Hierarchical
    'parent_of',
    'child_of',
    'part_of',
    -- Semantic
    'related_to',
    'similar_to',
    'opposite_of',
    -- Functional
    'depends_on',
    'enables',
    'requires',
    -- Knowledge
    'supports',
    'contradicts',
    'derived_from',
    -- Skill
    'uses',
    'implements',
    'extends',
    -- Learning
    'learned_from',
    'applies_to',
    'validates'
);

-- Axiom status
CREATE TYPE axiom_status AS ENUM (
    'proposed',
    'validated',
    'deprecated',
    'superseded'
);

-- ============================================================================
-- TABLE: kg_items
-- ============================================================================

CREATE TABLE kg_items (
    -- Primary key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(50) NOT NULL DEFAULT 'item',

    -- Core fields
    name VARCHAR(500) NOT NULL,
    description TEXT,

    -- Metadata
    metadata JSONB NOT NULL DEFAULT '{}',
    tags JSONB NOT NULL DEFAULT '[]',

    -- Vector embedding reference (stored in Qdrant)
    qdrant_collection VARCHAR(100),
    qdrant_point_id VARCHAR(100),
    embedding_model VARCHAR(100),
    embedding_dimensions INTEGER,

    -- Item-specific fields
    item_type VARCHAR(100),
    location VARCHAR(500),
    status VARCHAR(50),

    -- Value/cost tracking
    value DOUBLE PRECISION,
    cost DOUBLE PRECISION,
    currency VARCHAR(10),

    -- Versioning
    version INTEGER NOT NULL DEFAULT 1,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,

    -- Soft delete
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    -- Audit
    created_by VARCHAR(100),
    updated_by VARCHAR(100)
);

-- Indexes for kg_items
CREATE INDEX idx_items_entity_type ON kg_items(entity_type);
CREATE INDEX idx_items_name ON kg_items(name);
CREATE INDEX idx_items_qdrant_collection ON kg_items(qdrant_collection);
CREATE INDEX idx_items_qdrant_point_id ON kg_items(qdrant_point_id);
CREATE INDEX idx_items_type_status ON kg_items(item_type, status);
CREATE INDEX idx_items_deleted ON kg_items(is_deleted, deleted_at);
CREATE INDEX idx_items_created_at ON kg_items(created_at DESC);
CREATE INDEX idx_items_tags ON kg_items USING GIN(tags);
CREATE INDEX idx_items_metadata ON kg_items USING GIN(metadata);

-- Trigger for updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER items_updated_at
    BEFORE UPDATE ON kg_items
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- TABLE: kg_skills
-- ============================================================================

CREATE TABLE kg_skills (
    -- Primary key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(50) NOT NULL DEFAULT 'skill',

    -- Core fields
    name VARCHAR(500) NOT NULL,
    description TEXT,

    -- Metadata
    metadata JSONB NOT NULL DEFAULT '{}',
    tags JSONB NOT NULL DEFAULT '[]',

    -- Vector embedding reference
    qdrant_collection VARCHAR(100),
    qdrant_point_id VARCHAR(100),
    embedding_model VARCHAR(100),
    embedding_dimensions INTEGER,

    -- Skill-specific fields
    skill_type VARCHAR(100),
    capability VARCHAR(200),
    implementation_path VARCHAR(500),

    -- Usage tracking
    usage_count INTEGER NOT NULL DEFAULT 0,
    success_count INTEGER NOT NULL DEFAULT 0,
    failure_count INTEGER NOT NULL DEFAULT 0,
    last_used_at TIMESTAMP WITH TIME ZONE,

    -- Performance
    avg_execution_time_ms DOUBLE PRECISION,

    -- Versioning
    version INTEGER NOT NULL DEFAULT 1,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,

    -- Soft delete
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    -- Audit
    created_by VARCHAR(100),
    updated_by VARCHAR(100)
);

-- Indexes for kg_skills
CREATE INDEX idx_skills_entity_type ON kg_skills(entity_type);
CREATE INDEX idx_skills_name ON kg_skills(name);
CREATE INDEX idx_skills_qdrant_collection ON kg_skills(qdrant_collection);
CREATE INDEX idx_skills_qdrant_point_id ON kg_skills(qdrant_point_id);
CREATE INDEX idx_skills_type_capability ON kg_skills(skill_type, capability);
CREATE INDEX idx_skills_usage ON kg_skills(usage_count, last_used_at);
CREATE INDEX idx_skills_deleted ON kg_skills(is_deleted, deleted_at);
CREATE INDEX idx_skills_created_at ON kg_skills(created_at DESC);
CREATE INDEX idx_skills_tags ON kg_skills USING GIN(tags);
CREATE INDEX idx_skills_metadata ON kg_skills USING GIN(metadata);

-- Trigger for updated_at
CREATE TRIGGER skills_updated_at
    BEFORE UPDATE ON kg_skills
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- TABLE: kg_knowledge
-- ============================================================================

CREATE TABLE kg_knowledge (
    -- Primary key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(50) NOT NULL DEFAULT 'knowledge',

    -- Core fields
    name VARCHAR(500) NOT NULL,
    description TEXT,

    -- Metadata
    metadata JSONB NOT NULL DEFAULT '{}',
    tags JSONB NOT NULL DEFAULT '[]',

    -- Vector embedding reference
    qdrant_collection VARCHAR(100),
    qdrant_point_id VARCHAR(100),
    embedding_model VARCHAR(100),
    embedding_dimensions INTEGER,

    -- Knowledge-specific fields
    knowledge_type VARCHAR(100),
    source VARCHAR(500),
    content TEXT,

    -- Quality metrics
    confidence_score DOUBLE PRECISION,
    validation_status VARCHAR(50),
    validated_at TIMESTAMP WITH TIME ZONE,
    validated_by VARCHAR(100),

    -- Usage tracking
    access_count INTEGER NOT NULL DEFAULT 0,
    last_accessed_at TIMESTAMP WITH TIME ZONE,

    -- Versioning
    version INTEGER NOT NULL DEFAULT 1,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,

    -- Soft delete
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    -- Audit
    created_by VARCHAR(100),
    updated_by VARCHAR(100)
);

-- Indexes for kg_knowledge
CREATE INDEX idx_knowledge_entity_type ON kg_knowledge(entity_type);
CREATE INDEX idx_knowledge_name ON kg_knowledge(name);
CREATE INDEX idx_knowledge_qdrant_collection ON kg_knowledge(qdrant_collection);
CREATE INDEX idx_knowledge_qdrant_point_id ON kg_knowledge(qdrant_point_id);
CREATE INDEX idx_knowledge_type_status ON kg_knowledge(knowledge_type, validation_status);
CREATE INDEX idx_knowledge_confidence ON kg_knowledge(confidence_score);
CREATE INDEX idx_knowledge_deleted ON kg_knowledge(is_deleted, deleted_at);
CREATE INDEX idx_knowledge_created_at ON kg_knowledge(created_at DESC);
CREATE INDEX idx_knowledge_tags ON kg_knowledge USING GIN(tags);
CREATE INDEX idx_knowledge_metadata ON kg_knowledge USING GIN(metadata);
CREATE INDEX idx_knowledge_content_fts ON kg_knowledge USING GIN(to_tsvector('english', content));

-- Trigger for updated_at
CREATE TRIGGER knowledge_updated_at
    BEFORE UPDATE ON kg_knowledge
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- TABLE: kg_learnings
-- ============================================================================

CREATE TABLE kg_learnings (
    -- Primary key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(50) NOT NULL DEFAULT 'learning',

    -- Core fields
    name VARCHAR(500) NOT NULL,
    description TEXT,

    -- Metadata
    metadata JSONB NOT NULL DEFAULT '{}',
    tags JSONB NOT NULL DEFAULT '[]',

    -- Vector embedding reference
    qdrant_collection VARCHAR(100),
    qdrant_point_id VARCHAR(100),
    embedding_model VARCHAR(100),
    embedding_dimensions INTEGER,

    -- Learning-specific fields
    learning_type VARCHAR(100),
    context TEXT,
    outcome TEXT,

    -- Impact
    impact_score DOUBLE PRECISION,
    surprise_score DOUBLE PRECISION,

    -- Application
    applied_count INTEGER NOT NULL DEFAULT 0,
    last_applied_at TIMESTAMP WITH TIME ZONE,

    -- Versioning
    version INTEGER NOT NULL DEFAULT 1,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,

    -- Soft delete
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    -- Audit
    created_by VARCHAR(100),
    updated_by VARCHAR(100)
);

-- Indexes for kg_learnings
CREATE INDEX idx_learnings_entity_type ON kg_learnings(entity_type);
CREATE INDEX idx_learnings_name ON kg_learnings(name);
CREATE INDEX idx_learnings_qdrant_collection ON kg_learnings(qdrant_collection);
CREATE INDEX idx_learnings_qdrant_point_id ON kg_learnings(qdrant_point_id);
CREATE INDEX idx_learnings_type_impact ON kg_learnings(learning_type, impact_score);
CREATE INDEX idx_learnings_surprise ON kg_learnings(surprise_score);
CREATE INDEX idx_learnings_deleted ON kg_learnings(is_deleted, deleted_at);
CREATE INDEX idx_learnings_created_at ON kg_learnings(created_at DESC);
CREATE INDEX idx_learnings_tags ON kg_learnings USING GIN(tags);
CREATE INDEX idx_learnings_metadata ON kg_learnings USING GIN(metadata);

-- Trigger for updated_at
CREATE TRIGGER learnings_updated_at
    BEFORE UPDATE ON kg_learnings
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- TABLE: kg_axioms
-- ============================================================================

CREATE TABLE kg_axioms (
    -- Primary key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(50) NOT NULL DEFAULT 'axiom',

    -- Core fields
    name VARCHAR(500) NOT NULL,
    description TEXT,

    -- Metadata
    metadata JSONB NOT NULL DEFAULT '{}',
    tags JSONB NOT NULL DEFAULT '[]',

    -- Vector embedding reference
    qdrant_collection VARCHAR(100),
    qdrant_point_id VARCHAR(100),
    embedding_model VARCHAR(100),
    embedding_dimensions INTEGER,

    -- Axiom-specific fields
    axiom_type VARCHAR(100),
    statement TEXT NOT NULL,

    -- Validation
    status VARCHAR(50) NOT NULL DEFAULT 'proposed',
    confidence_score DOUBLE PRECISION,
    evidence JSONB NOT NULL DEFAULT '[]',

    -- Provenance
    source VARCHAR(500),
    validated_at TIMESTAMP WITH TIME ZONE,
    validated_by VARCHAR(100),

    -- Usage
    applied_count INTEGER NOT NULL DEFAULT 0,
    violation_count INTEGER NOT NULL DEFAULT 0,
    last_applied_at TIMESTAMP WITH TIME ZONE,

    -- Supersession
    superseded_by_id UUID,
    superseded_at TIMESTAMP WITH TIME ZONE,

    -- Versioning
    version INTEGER NOT NULL DEFAULT 1,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,

    -- Soft delete
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    -- Audit
    created_by VARCHAR(100),
    updated_by VARCHAR(100)
);

-- Indexes for kg_axioms
CREATE INDEX idx_axioms_entity_type ON kg_axioms(entity_type);
CREATE INDEX idx_axioms_name ON kg_axioms(name);
CREATE INDEX idx_axioms_qdrant_collection ON kg_axioms(qdrant_collection);
CREATE INDEX idx_axioms_qdrant_point_id ON kg_axioms(qdrant_point_id);
CREATE INDEX idx_axioms_type_status ON kg_axioms(axiom_type, status);
CREATE INDEX idx_axioms_confidence ON kg_axioms(confidence_score);
CREATE INDEX idx_axioms_applied ON kg_axioms(applied_count, last_applied_at);
CREATE INDEX idx_axioms_deleted ON kg_axioms(is_deleted, deleted_at);
CREATE INDEX idx_axioms_created_at ON kg_axioms(created_at DESC);
CREATE INDEX idx_axioms_tags ON kg_axioms USING GIN(tags);
CREATE INDEX idx_axioms_metadata ON kg_axioms USING GIN(metadata);
CREATE INDEX idx_axioms_evidence ON kg_axioms USING GIN(evidence);
CREATE INDEX idx_axioms_statement_fts ON kg_axioms USING GIN(to_tsvector('english', statement));

-- Trigger for updated_at
CREATE TRIGGER axioms_updated_at
    BEFORE UPDATE ON kg_axioms
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- TABLE: kg_relationships (Edge Table)
-- ============================================================================

CREATE TABLE kg_relationships (
    -- Primary key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Edge definition (directed: source → target)
    source_id UUID NOT NULL,
    source_type VARCHAR(50) NOT NULL,

    target_id UUID NOT NULL,
    target_type VARCHAR(50) NOT NULL,

    -- Relationship type
    relationship_type VARCHAR(100) NOT NULL,

    -- Metadata
    metadata JSONB NOT NULL DEFAULT '{}',

    -- Weight/confidence
    weight DOUBLE PRECISION,
    confidence_score DOUBLE PRECISION,

    -- Provenance
    created_by VARCHAR(100),
    evidence JSONB NOT NULL DEFAULT '[]',

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,

    -- Soft delete
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE
);

-- Indexes for kg_relationships (critical for graph traversal performance)
CREATE INDEX idx_rel_source_type ON kg_relationships(source_id, source_type, relationship_type);
CREATE INDEX idx_rel_target_type ON kg_relationships(target_id, target_type, relationship_type);
CREATE INDEX idx_rel_bidirectional ON kg_relationships(source_id, target_id, relationship_type);
CREATE INDEX idx_rel_type_weight ON kg_relationships(relationship_type, weight);
CREATE INDEX idx_rel_deleted ON kg_relationships(is_deleted, deleted_at);
CREATE INDEX idx_rel_created_at ON kg_relationships(created_at DESC);
CREATE INDEX idx_rel_metadata ON kg_relationships USING GIN(metadata);
CREATE INDEX idx_rel_evidence ON kg_relationships USING GIN(evidence);

-- Unique constraint: prevent duplicate relationships (same source, target, type)
CREATE UNIQUE INDEX idx_rel_unique_active
    ON kg_relationships(source_id, target_id, relationship_type)
    WHERE is_deleted = FALSE;

-- Trigger for updated_at
CREATE TRIGGER relationships_updated_at
    BEFORE UPDATE ON kg_relationships
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- VIEWS
-- ============================================================================

-- Unified entity view (all entities)
CREATE VIEW v_kg_entities AS
    SELECT id, entity_type, name, description, created_at, updated_at, is_deleted
    FROM kg_items
    UNION ALL
    SELECT id, entity_type, name, description, created_at, updated_at, is_deleted
    FROM kg_skills
    UNION ALL
    SELECT id, entity_type, name, description, created_at, updated_at, is_deleted
    FROM kg_knowledge
    UNION ALL
    SELECT id, entity_type, name, description, created_at, updated_at, is_deleted
    FROM kg_learnings
    UNION ALL
    SELECT id, entity_type, name, description, created_at, updated_at, is_deleted
    FROM kg_axioms;

-- Active entities only (not soft-deleted)
CREATE VIEW v_kg_entities_active AS
    SELECT * FROM v_kg_entities
    WHERE is_deleted = FALSE;

-- Relationship statistics
CREATE VIEW v_kg_relationship_stats AS
    SELECT
        relationship_type,
        COUNT(*) as total_count,
        COUNT(*) FILTER (WHERE is_deleted = FALSE) as active_count,
        AVG(weight) as avg_weight,
        AVG(confidence_score) as avg_confidence
    FROM kg_relationships
    GROUP BY relationship_type;

-- ============================================================================
-- HELPER FUNCTIONS
-- ============================================================================

-- Soft delete function
CREATE OR REPLACE FUNCTION soft_delete_entity(
    p_table_name TEXT,
    p_entity_id UUID
)
RETURNS BOOLEAN AS $$
BEGIN
    EXECUTE format(
        'UPDATE %I SET is_deleted = TRUE, deleted_at = NOW() WHERE id = $1',
        p_table_name
    ) USING p_entity_id;

    RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

-- Restore soft-deleted entity
CREATE OR REPLACE FUNCTION restore_entity(
    p_table_name TEXT,
    p_entity_id UUID
)
RETURNS BOOLEAN AS $$
BEGIN
    EXECUTE format(
        'UPDATE %I SET is_deleted = FALSE, deleted_at = NULL WHERE id = $1',
        p_table_name
    ) USING p_entity_id;

    RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

-- Get entity relationships (outgoing)
CREATE OR REPLACE FUNCTION get_entity_relationships(
    p_entity_id UUID,
    p_relationship_type VARCHAR DEFAULT NULL
)
RETURNS TABLE (
    relationship_id UUID,
    target_id UUID,
    target_type VARCHAR,
    relationship_type VARCHAR,
    weight DOUBLE PRECISION,
    confidence_score DOUBLE PRECISION
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        r.id,
        r.target_id,
        r.target_type,
        r.relationship_type,
        r.weight,
        r.confidence_score
    FROM kg_relationships r
    WHERE r.source_id = p_entity_id
      AND r.is_deleted = FALSE
      AND (p_relationship_type IS NULL OR r.relationship_type = p_relationship_type);
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- GRANTS (Adjust as needed for your user)
-- ============================================================================

-- Grant permissions to genesis user (if exists)
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO genesis;
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO genesis;
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO genesis;

-- ============================================================================
-- MIGRATION METADATA
-- ============================================================================

CREATE TABLE IF NOT EXISTS schema_migrations (
    version VARCHAR(50) PRIMARY KEY,
    applied_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    description TEXT
);

INSERT INTO schema_migrations (version, description)
VALUES ('001', 'Initial knowledge graph schema with 6 entity types');

-- ============================================================================
-- VERIFICATION_STAMP
-- ============================================================================
-- Story: KG-001
-- Verified By: Claude (Opus 4.5)
-- Verified At: 2026-01-24
-- Migration: 001_knowledge_graph.sql
-- Database: PostgreSQL (Elestio Core)
-- Features: UUID PKs, JSONB metadata, soft delete, versioning, indexes
-- Rule 6: No SQLite - PostgreSQL only
-- ============================================================================

COMMIT;
