"""Sales pipeline: extend opportunities, add proposals and proposal_line_items

Revision ID: 0009
Revises: 0008
Create Date: 2026-06-25 00:00:00.000000

Changes:
  1. ALTER opportunities — add family_name, care_type, contact_phone,
     contact_email, section_id (FK), next_action, lost_reason,
     stage_entered_at, deleted_at; add indexes on care_type and deleted_at.
  2. Backfill OpportunityStage values:
       proposal  -> proposal_sent
       signed    -> contract_signed
       complete  -> fully_paid
  3. CREATE proposals table.
  4. CREATE proposal_line_items table.
  5. Enable RLS on both new tables.
"""
from typing import Sequence, Union

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

revision: str = "0009"
down_revision: Union[str, None] = "0008"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

_RLS_ROLE = "indelis_app"


def _enable_rls(table: str) -> None:
    op.execute(sa.text(f"ALTER TABLE {table} ENABLE ROW LEVEL SECURITY"))
    op.execute(sa.text(f"ALTER TABLE {table} FORCE ROW LEVEL SECURITY"))
    op.execute(
        sa.text(
            f"""
            CREATE POLICY tenant_isolation ON {table}
            USING (tenant_id = NULLIF(current_setting('app.tenant', TRUE), '')::uuid)
            """
        )
    )
    op.execute(
        sa.text(f"GRANT SELECT, INSERT, UPDATE, DELETE ON {table} TO {_RLS_ROLE}")
    )


def _disable_rls(table: str) -> None:
    op.execute(sa.text(f"DROP POLICY IF EXISTS tenant_isolation ON {table}"))
    op.execute(sa.text(f"ALTER TABLE {table} DISABLE ROW LEVEL SECURITY"))


# ---------------------------------------------------------------------------
# upgrade
# ---------------------------------------------------------------------------


def upgrade() -> None:

    # ── 1. Extend opportunities ───────────────────────────────────────────────

    op.add_column(
        "opportunities",
        sa.Column("family_name", sa.String(255), nullable=True),
    )
    op.add_column(
        "opportunities",
        sa.Column("care_type", sa.String(50), nullable=True),
    )
    op.add_column(
        "opportunities",
        sa.Column("contact_phone", sa.String(50), nullable=True),
    )
    op.add_column(
        "opportunities",
        sa.Column("contact_email", sa.String(255), nullable=True),
    )
    op.add_column(
        "opportunities",
        sa.Column(
            "section_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("sections.id", ondelete="SET NULL"),
            nullable=True,
        ),
    )
    op.add_column(
        "opportunities",
        sa.Column("next_action", sa.Text, nullable=True),
    )
    op.add_column(
        "opportunities",
        sa.Column("lost_reason", sa.Text, nullable=True),
    )
    op.add_column(
        "opportunities",
        sa.Column(
            "stage_entered_at",
            sa.DateTime(timezone=True),
            nullable=True,
            server_default=sa.text("NOW()"),
        ),
    )
    op.add_column(
        "opportunities",
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
    )

    # Indexes on new columns
    op.create_index("ix_opportunities_care_type", "opportunities", ["care_type"])
    op.create_index("ix_opportunities_deleted_at", "opportunities", ["deleted_at"])
    op.create_index("ix_opportunities_section_id", "opportunities", ["section_id"])

    # ── 2. Backfill OpportunityStage enum values ──────────────────────────────
    #
    # The stage column is VARCHAR(50) — no native PG enum to ALTER, just UPDATE.
    # Order matters: do 'proposal' before 'proposal_sent' exists as a target to
    # avoid double-touching rows if the migration is re-run partially. We guard
    # with the exact old value so re-runs are idempotent.

    op.execute(
        sa.text(
            "UPDATE opportunities SET stage = 'proposal_sent' WHERE stage = 'proposal'"
        )
    )
    op.execute(
        sa.text(
            "UPDATE opportunities SET stage = 'contract_signed' WHERE stage = 'signed'"
        )
    )
    op.execute(
        sa.text(
            "UPDATE opportunities SET stage = 'fully_paid' WHERE stage = 'complete'"
        )
    )

    # ── 3. Create proposals ───────────────────────────────────────────────────

    op.create_table(
        "proposals",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "tenant_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("accounts.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "opportunity_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("opportunities.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("quote_number", sa.String(50), nullable=False),
        sa.Column("to_email", sa.String(255), nullable=False),
        sa.Column("cc_email", sa.String(255), nullable=True),
        sa.Column("subject", sa.String(500), nullable=False),
        sa.Column("cover_note", sa.Text, nullable=True),
        sa.Column(
            "valid_days",
            sa.Integer,
            nullable=False,
            server_default=sa.text("30"),
        ),
        sa.Column(
            "sales_owner_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "status",
            sa.String(50),
            nullable=False,
            server_default=sa.text("'draft'"),
        ),
        sa.Column(
            "subtotal",
            sa.Numeric(12, 2),
            nullable=False,
            server_default=sa.text("0"),
        ),
        sa.Column(
            "tax_rate",
            sa.Numeric(5, 4),
            nullable=False,
            server_default=sa.text("0.1300"),
        ),
        sa.Column(
            "tax_amount",
            sa.Numeric(12, 2),
            nullable=False,
            server_default=sa.text("0"),
        ),
        sa.Column(
            "total_amount",
            sa.Numeric(12, 2),
            nullable=False,
            server_default=sa.text("0"),
        ),
        sa.Column("sent_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("accepted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("pdf_s3_key", sa.Text, nullable=True),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        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()"),
        ),
        sa.UniqueConstraint("tenant_id", "quote_number", name="uq_proposals_tenant_quote_number"),
    )

    op.create_index("ix_proposals_tenant_id", "proposals", ["tenant_id"])
    op.create_index("ix_proposals_opportunity_id", "proposals", ["opportunity_id"])
    op.create_index("ix_proposals_status", "proposals", ["status"])
    op.create_index("ix_proposals_deleted_at", "proposals", ["deleted_at"])

    # ── 4. Create proposal_line_items ─────────────────────────────────────────

    op.create_table(
        "proposal_line_items",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "proposal_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("proposals.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "tenant_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("accounts.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "sort_order",
            sa.Integer,
            nullable=False,
            server_default=sa.text("0"),
        ),
        sa.Column("description", sa.Text, nullable=False),
        sa.Column(
            "quantity",
            sa.Numeric(10, 2),
            nullable=False,
            server_default=sa.text("1"),
        ),
        sa.Column("unit_price", sa.Numeric(12, 2), nullable=False),
        sa.Column("line_total", sa.Numeric(12, 2), nullable=False),
        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_proposal_line_items_proposal_id",
        "proposal_line_items",
        ["proposal_id"],
    )
    op.create_index(
        "ix_proposal_line_items_tenant_id",
        "proposal_line_items",
        ["tenant_id"],
    )

    # ── 5. RLS on new tables ──────────────────────────────────────────────────

    _enable_rls("proposals")
    _enable_rls("proposal_line_items")


# ---------------------------------------------------------------------------
# downgrade
# ---------------------------------------------------------------------------


def downgrade() -> None:

    # ── RLS off first ─────────────────────────────────────────────────────────
    _disable_rls("proposal_line_items")
    _disable_rls("proposals")

    # ── Drop new tables ───────────────────────────────────────────────────────
    op.drop_table("proposal_line_items")
    op.drop_table("proposals")

    # ── Reverse stage backfill ────────────────────────────────────────────────
    op.execute(
        sa.text(
            "UPDATE opportunities SET stage = 'complete' WHERE stage = 'fully_paid'"
        )
    )
    op.execute(
        sa.text(
            "UPDATE opportunities SET stage = 'signed' WHERE stage = 'contract_signed'"
        )
    )
    op.execute(
        sa.text(
            "UPDATE opportunities SET stage = 'proposal' WHERE stage = 'proposal_sent'"
        )
    )
    # Rows with new-only stages (site_visit, deposit_received, lost) have no
    # valid legacy equivalent — set them back to 'inquiry' to keep the column
    # constraint-safe.
    op.execute(
        sa.text(
            """
            UPDATE opportunities
               SET stage = 'inquiry'
             WHERE stage IN ('site_visit', 'deposit_received', 'lost')
            """
        )
    )

    # ── Remove added indexes ──────────────────────────────────────────────────
    op.drop_index("ix_opportunities_section_id", table_name="opportunities")
    op.drop_index("ix_opportunities_deleted_at", table_name="opportunities")
    op.drop_index("ix_opportunities_care_type", table_name="opportunities")

    # ── Remove added columns (reverse order of addition) ─────────────────────
    op.drop_column("opportunities", "deleted_at")
    op.drop_column("opportunities", "stage_entered_at")
    op.drop_column("opportunities", "lost_reason")
    op.drop_column("opportunities", "next_action")
    op.drop_column("opportunities", "section_id")
    op.drop_column("opportunities", "contact_email")
    op.drop_column("opportunities", "contact_phone")
    op.drop_column("opportunities", "care_type")
    op.drop_column("opportunities", "family_name")