"""Add enquiry workflow timestamp columns and audit log table.

Revision ID: 0029
Revises: 0028
Create Date: 2026-06-29
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "0029"
down_revision = "0028"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Use IF NOT EXISTS to handle dev DBs that may already have some columns
    op.execute(
        "ALTER TABLE website_enquiries ADD COLUMN IF NOT EXISTS "
        "contacted_at TIMESTAMP WITH TIME ZONE"
    )
    op.execute(
        "ALTER TABLE website_enquiries ADD COLUMN IF NOT EXISTS "
        "contact_method VARCHAR(20)"
    )
    op.execute(
        "ALTER TABLE website_enquiries ADD COLUMN IF NOT EXISTS "
        "qualified_at TIMESTAMP WITH TIME ZONE"
    )
    op.execute(
        "ALTER TABLE website_enquiries ADD COLUMN IF NOT EXISTS "
        "qualified_email_sent_at TIMESTAMP WITH TIME ZONE"
    )
    op.execute(
        "ALTER TABLE website_enquiries ADD COLUMN IF NOT EXISTS "
        "signup_completed_at TIMESTAMP WITH TIME ZONE"
    )
    op.execute(
        "ALTER TABLE website_enquiries ADD COLUMN IF NOT EXISTS "
        "auto_closed_at TIMESTAMP WITH TIME ZONE"
    )

    # Partial index — use IF NOT EXISTS via raw SQL
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_website_enquiries_qualified_email_sent_at "
        "ON website_enquiries (qualified_email_sent_at) WHERE status = 'qualified'"
    )

    # Create audit log table
    op.execute("""
        CREATE TABLE IF NOT EXISTS enquiry_audit_logs (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            enquiry_id UUID NOT NULL REFERENCES website_enquiries(id) ON DELETE CASCADE,
            from_status VARCHAR(50) NOT NULL,
            to_status VARCHAR(50) NOT NULL,
            trigger_source VARCHAR(50) NOT NULL,
            actor_id UUID,
            actor_email VARCHAR(255),
            notes TEXT,
            created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
        )
    """)
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_enquiry_audit_logs_enquiry_id "
        "ON enquiry_audit_logs (enquiry_id)"
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_enquiry_audit_logs_created_at "
        "ON enquiry_audit_logs (created_at)"
    )


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS enquiry_audit_logs")
    op.execute(
        "DROP INDEX IF EXISTS ix_website_enquiries_qualified_email_sent_at"
    )
    op.execute(
        "ALTER TABLE website_enquiries DROP COLUMN IF EXISTS auto_closed_at"
    )
    op.execute(
        "ALTER TABLE website_enquiries DROP COLUMN IF EXISTS signup_completed_at"
    )
    op.execute(
        "ALTER TABLE website_enquiries DROP COLUMN IF EXISTS qualified_email_sent_at"
    )
    op.execute(
        "ALTER TABLE website_enquiries DROP COLUMN IF EXISTS qualified_at"
    )
    op.execute(
        "ALTER TABLE website_enquiries DROP COLUMN IF EXISTS contact_method"
    )
    op.execute(
        "ALTER TABLE website_enquiries DROP COLUMN IF EXISTS contacted_at"
    )