"""Create musehub_proposal_dependencies and musehub_proposal_simulations tables (issue #37 Phase 1c)

Revision ID: 0047
Revises: 0046
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import JSONB

revision: str = "0047"
down_revision: str = "0046"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "musehub_proposal_dependencies",
        sa.Column("dep_id", sa.String(128), primary_key=True, nullable=False),
        sa.Column("dependent_proposal_id", sa.String(128), sa.ForeignKey("musehub_proposals.proposal_id", ondelete="CASCADE"), nullable=False),
        sa.Column("dependency_proposal_id", sa.String(128), sa.ForeignKey("musehub_proposals.proposal_id", ondelete="CASCADE"), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("NOW()")),
        sa.UniqueConstraint("dependent_proposal_id", "dependency_proposal_id", name="uq_musehub_prop_dep_edge"),
    )
    op.create_index("ix_musehub_prop_deps_dependent", "musehub_proposal_dependencies", ["dependent_proposal_id"])
    op.create_index("ix_musehub_prop_deps_dependency", "musehub_proposal_dependencies", ["dependency_proposal_id"])

    op.create_table(
        "musehub_proposal_simulations",
        sa.Column("simulation_id", sa.String(128), primary_key=True, nullable=False),
        sa.Column("proposal_id", sa.String(128), sa.ForeignKey("musehub_proposals.proposal_id", ondelete="CASCADE"), nullable=False),
        sa.Column("simulation_type", sa.String(50), nullable=False),
        sa.Column("from_branch_commit_id", sa.String(128), nullable=False, server_default=""),
        sa.Column("result", JSONB, nullable=False, server_default="{}"),
        sa.Column("duration_ms", sa.Integer, nullable=False, server_default="0"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("NOW()")),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
        sa.UniqueConstraint("proposal_id", "simulation_type", name="uq_musehub_prop_sim"),
    )
    op.create_index("ix_musehub_prop_sim_proposal", "musehub_proposal_simulations", ["proposal_id"])


def downgrade() -> None:
    op.drop_table("musehub_proposal_simulations")
    op.drop_table("musehub_proposal_dependencies")