"""Add proof types for Meta sub-channel

Revision ID: 003_add_meta_proof_types
Revises: 002_seed_dropdown
Create Date: 2025-01-08

"""
from typing import Sequence, Union
from uuid import uuid4

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = '003_add_meta_proof_types'
down_revision: Union[str, None] = '002_seed_dropdown'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Add proof types for Meta sub-channel if they don't exist."""

    conn = op.get_bind()

    # Find the Meta sub-channel ID
    result = conn.execute(
        sa.text("""
            SELECT id FROM dropdown_options
            WHERE option_type = 'sub_channel' AND value = 'Meta'
        """)
    )
    row = result.fetchone()

    if not row:
        # Meta sub-channel doesn't exist - need to create it first
        # Find the Social channel
        social_result = conn.execute(
            sa.text("""
                SELECT id FROM dropdown_options
                WHERE option_type = 'channel' AND value = 'Social'
            """)
        )
        social_row = social_result.fetchone()

        if social_row:
            meta_id = str(uuid4())
            conn.execute(
                sa.text("""
                    INSERT INTO dropdown_options (id, option_type, parent_id, value, display_order)
                    VALUES (:id, 'sub_channel', :parent_id, 'Meta', 0)
                """),
                {"id": meta_id, "parent_id": social_row[0]}
            )
            meta_sub_channel_id = meta_id
        else:
            return  # Can't proceed without Social channel
    else:
        meta_sub_channel_id = row[0]

    # Meta proof types for Facebook/Instagram ads
    meta_proof_types = [
        "In-feed 1x1",       # Square post format
        "In-feed 4x5",       # Portrait post format (recommended for feed)
        "In-feed 9x16",      # Full vertical (Stories/Reels)
        "In-feed 16x9",      # Landscape video
        "Stories",           # Facebook/Instagram Stories
        "Reels",             # Instagram/Facebook Reels
        "Carousel",          # Multi-image/video carousel
        "Collection",        # Collection ad format
    ]

    for idx, proof_type_name in enumerate(meta_proof_types):
        # Check if proof type already exists
        existing = conn.execute(
            sa.text("""
                SELECT id FROM dropdown_options
                WHERE option_type = 'proof_type'
                AND parent_id = :parent_id
                AND value = :value
            """),
            {"parent_id": meta_sub_channel_id, "value": proof_type_name}
        )

        if not existing.fetchone():
            proof_type_id = str(uuid4())
            conn.execute(
                sa.text("""
                    INSERT INTO dropdown_options (id, option_type, parent_id, value, display_order)
                    VALUES (:id, 'proof_type', :parent_id, :value, :order)
                """),
                {"id": proof_type_id, "parent_id": meta_sub_channel_id, "value": proof_type_name, "order": idx}
            )


def downgrade() -> None:
    """Remove Meta proof types added by this migration."""
    conn = op.get_bind()

    # Find the Meta sub-channel ID
    result = conn.execute(
        sa.text("""
            SELECT id FROM dropdown_options
            WHERE option_type = 'sub_channel' AND value = 'Meta'
        """)
    )
    row = result.fetchone()

    if row:
        meta_sub_channel_id = row[0]
        # Only remove the proof types we added
        proof_types_to_remove = [
            "In-feed 16x9",
            "Reels",
            "Carousel",
            "Collection",
        ]
        for proof_type in proof_types_to_remove:
            conn.execute(
                sa.text("""
                    DELETE FROM dropdown_options
                    WHERE option_type = 'proof_type'
                    AND parent_id = :parent_id
                    AND value = :value
                """),
                {"parent_id": meta_sub_channel_id, "value": proof_type}
            )
