"""Add new columns to services table (scheduling enhancements)

Revision ID: 0017
Revises: 0016
Create Date: 2026-06-25 00:00:00.000000

Changes:
  1. ADD section_id, created_by FKs
  2. ADD officiant, family_contact_name, family_contact_phone, expected_attendees
  3. ADD equipment_needed, run_sheet_milestones (JSONB), email_family, add_to_briefing
  4. ADD reminder_24h, reminder_sent, pdf_s3_key
  5. RENAME briefing_sheet_s3_key → kept as-is (pdf_s3_key is new col, old stays for safety)
  6. UPDATE indexes on services
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import JSONB, UUID as PG_UUID


revision: str = "0017"
down_revision: Union[str, None] = "0016"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Add new FK columns
    op.add_column("services", sa.Column("section_id", PG_UUID(as_uuid=True), nullable=True))
    op.add_column("services", sa.Column("created_by", PG_UUID(as_uuid=True), nullable=True))

    # Add contact / logistics columns
    op.add_column("services", sa.Column("officiant", sa.String(255), nullable=True))
    op.add_column("services", sa.Column("family_contact_name", sa.String(255), nullable=True))
    op.add_column("services", sa.Column("family_contact_phone", sa.String(50), nullable=True))
    op.add_column("services", sa.Column("expected_attendees", sa.Integer(), nullable=True))
    op.add_column("services", sa.Column("equipment_needed", sa.Text(), nullable=True))
    op.add_column("services", sa.Column("run_sheet_milestones", JSONB(), nullable=True))

    # Add boolean flags (default False)
    op.add_column("services", sa.Column("email_family", sa.Boolean(), nullable=False, server_default="false"))
    op.add_column("services", sa.Column("add_to_briefing", sa.Boolean(), nullable=False, server_default="false"))
    op.add_column("services", sa.Column("reminder_24h", sa.Boolean(), nullable=False, server_default="false"))
    op.add_column("services", sa.Column("reminder_sent", sa.Boolean(), nullable=False, server_default="false"))

    # Add pdf key
    op.add_column("services", sa.Column("pdf_s3_key", sa.Text(), nullable=True))

    # Foreign keys
    op.create_foreign_key("fk_services_section_id", "services", "sections", ["section_id"], ["id"], ondelete="SET NULL")
    op.create_foreign_key("fk_services_created_by", "services", "users", ["created_by"], ["id"], ondelete="SET NULL")

    # New indexes
    op.create_index("ix_services_section_id", "services", ["section_id"])
    op.create_index("ix_services_created_by", "services", ["created_by"])
    op.create_index("ix_services_assigned_to", "services", ["assigned_to"])
    op.create_index("ix_services_record_id", "services", ["record_id"])
    op.create_index("ix_services_plot_id", "services", ["plot_id"])

    # Drop old index (renamed in model)
    op.drop_index("ix_services_service_date", table_name="services")
    op.create_index("ix_services_service_date", "services", ["service_date"])


def downgrade() -> None:
    op.drop_index("ix_services_plot_id", table_name="services")
    op.drop_index("ix_services_record_id", table_name="services")
    op.drop_index("ix_services_assigned_to", table_name="services")
    op.drop_index("ix_services_created_by", table_name="services")
    op.drop_index("ix_services_section_id", table_name="services")
    op.drop_constraint("fk_services_created_by", "services", type_="foreignkey")
    op.drop_constraint("fk_services_section_id", "services", type_="foreignkey")
    op.drop_column("services", "pdf_s3_key")
    op.drop_column("services", "reminder_sent")
    op.drop_column("services", "reminder_24h")
    op.drop_column("services", "add_to_briefing")
    op.drop_column("services", "email_family")
    op.drop_column("services", "run_sheet_milestones")
    op.drop_column("services", "equipment_needed")
    op.drop_column("services", "expected_attendees")
    op.drop_column("services", "family_contact_phone")
    op.drop_column("services", "family_contact_name")
    op.drop_column("services", "officiant")
    op.drop_column("services", "created_by")
    op.drop_column("services", "section_id")