"""Phase D addon: pgvector kb_obsidian_vectors table

Revision ID: b8c9d0e1f2a3
Revises: a7b8c9d0e1f2
Create Date: 2026-03-27

Prerequisites: sudo apt-get install postgresql-16-pgvector
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID, JSONB

revision = "b8c9d0e1f2a3"
down_revision = "a7b8c9d0e1f2"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("CREATE EXTENSION IF NOT EXISTS vector")
    op.create_table(
        "kb_obsidian_vectors",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("company_id", UUID(as_uuid=True), sa.ForeignKey("sys_companies.id"), nullable=False, index=True),
        sa.Column("source_path", sa.String(500), nullable=False, comment="源文件路径"),
        sa.Column("chunk_index", sa.SmallInteger, server_default="0"),
        sa.Column("content", sa.Text, nullable=False),
        sa.Column("metadata", JSONB, nullable=True),
        sa.Column("created_at", sa.DateTime, server_default=sa.func.now()),
        sa.Column("is_deleted", sa.Boolean, server_default="false"),
    )
    op.execute("ALTER TABLE kb_obsidian_vectors ADD COLUMN embedding vector(1536)")
    op.execute("""
        CREATE INDEX ix_kb_obsidian_vectors_embedding 
        ON kb_obsidian_vectors 
        USING hnsw (embedding vector_cosine_ops)
    """)


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_kb_obsidian_vectors_embedding")
    op.drop_table("kb_obsidian_vectors")
    op.execute("DROP EXTENSION IF EXISTS vector")