"""self-improvement tables: signals, patterns, proposals, impact measurements

Revision ID: 0132
Revises: 0131
Create Date: 2026-08-21
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID, JSONB

revision = "0132"
down_revision = "0131"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 1. improvement_patterns (created first because signals has FK to it)
    op.create_table(
        "improvement_patterns",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("tenant_id", UUID(as_uuid=True), sa.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False),
        sa.Column("pattern_kind", sa.String(40), nullable=False),
        sa.Column("title", sa.String(300), nullable=False),
        sa.Column("description", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("target_type", sa.String(30), nullable=False),
        sa.Column("target_name", sa.String(200), nullable=False, server_default=sa.text("'unknown'")),
        sa.Column("evidence_refs", JSONB, nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("occurrence_count", sa.Integer, nullable=False, server_default=sa.text("1")),
        sa.Column("confidence", sa.Float, nullable=False, server_default=sa.text("0.5")),
        sa.Column("status", sa.String(20), nullable=False, server_default=sa.text("'detected'")),
        sa.Column("proposed_action", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index("ix_impr_patterns_tenant_status", "improvement_patterns", ["tenant_id", "status"])
    op.create_index("ix_impr_patterns_tenant_target", "improvement_patterns", ["tenant_id", "target_type"])

    # 2. improvement_signals
    op.create_table(
        "improvement_signals",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("tenant_id", UUID(as_uuid=True), sa.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False),
        sa.Column("source_type", sa.String(50), nullable=False),
        sa.Column("source_ref_id", UUID(as_uuid=True), nullable=True),
        sa.Column("source_metadata", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("summary", sa.Text, nullable=False),
        sa.Column("signal_kind", sa.String(30), nullable=False),
        sa.Column("severity", sa.String(20), nullable=False, server_default=sa.text("'info'")),
        sa.Column("confidence", sa.Float, nullable=False, server_default=sa.text("0.5")),
        sa.Column("pattern_id", UUID(as_uuid=True), sa.ForeignKey("improvement_patterns.id", ondelete="SET NULL"), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index("ix_impr_signals_tenant_kind", "improvement_signals", ["tenant_id", "signal_kind"])
    op.create_index("ix_impr_signals_tenant_source", "improvement_signals", ["tenant_id", "source_type"])
    op.create_index("ix_impr_signals_tenant_pattern", "improvement_signals", ["tenant_id", "pattern_id"])

    # 3. improvement_proposals
    op.create_table(
        "improvement_proposals",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("tenant_id", UUID(as_uuid=True), sa.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False),
        sa.Column("owner_id", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("pattern_id", UUID(as_uuid=True), sa.ForeignKey("improvement_patterns.id", ondelete="SET NULL"), nullable=True),
        sa.Column("title", sa.String(300), nullable=False),
        sa.Column("description", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("target_type", sa.String(30), nullable=False),
        sa.Column("target_ref_id", UUID(as_uuid=True), nullable=True),
        sa.Column("target_name", sa.String(200), nullable=True),
        sa.Column("version_number", sa.Integer, nullable=False, server_default=sa.text("1")),
        sa.Column("proposed_config", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("previous_config", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("evidence_refs", JSONB, nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("rationale", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("expected_benefit", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("risk_assessment", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("evaluation_result", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("evaluated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("approval_request_id", UUID(as_uuid=True), nullable=True),
        sa.Column("approved_by", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("approved_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("activated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("rolled_back_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("rollback_reason", sa.Text, nullable=True),
        sa.Column("status", sa.String(20), nullable=False, server_default=sa.text("'draft'")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index("ix_impr_proposals_tenant_status", "improvement_proposals", ["tenant_id", "status"])
    op.create_index("ix_impr_proposals_tenant_target", "improvement_proposals", ["tenant_id", "target_type"])
    op.create_index("ix_impr_proposals_tenant_pattern", "improvement_proposals", ["tenant_id", "pattern_id"])

    # 4. improvement_impact_measurements
    op.create_table(
        "improvement_impact_measurements",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("tenant_id", UUID(as_uuid=True), sa.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False),
        sa.Column("proposal_id", UUID(as_uuid=True), sa.ForeignKey("improvement_proposals.id", ondelete="CASCADE"), nullable=False),
        sa.Column("pre_metrics", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("post_metrics", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("delta", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("assessment", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("is_positive", sa.String(20), nullable=False, server_default=sa.text("'neutral'")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index("ix_impr_impact_tenant_proposal", "improvement_impact_measurements", ["tenant_id", "proposal_id"])

    # RLS for all 4 tables
    for table in ["improvement_patterns", "improvement_signals", "improvement_proposals", "improvement_impact_measurements"]:
        op.execute(f"ALTER TABLE {table} ENABLE ROW LEVEL SECURITY;")
        op.execute(f"CREATE POLICY {table}_tenant_isolation ON {table} USING (tenant_id::text = current_setting('app.current_tenant_id', true));")


def downgrade() -> None:
    for table in ["improvement_impact_measurements", "improvement_proposals", "improvement_signals", "improvement_patterns"]:
        op.drop_table(table)