"""Seed default dropdown options, agencies, and brand guidelines

Revision ID: 002_seed_dropdown
Revises: 001_initial
Create Date: 2024-12-18

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

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

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


def upgrade() -> None:
    """Seed default channel/sub-channel/proof-type options, agencies, and brand guidelines."""

    conn = op.get_bind()

    # ===========================================
    # 1. Seed Agencies
    # ===========================================
    agencies = ["OLIVER Agency", "Barclays", "Mindshare", "Zenith", "Unassigned"]

    for agency_name in agencies:
        agency_id = str(uuid4())
        conn.execute(
            sa.text("""
                INSERT INTO agencies (id, name)
                VALUES (:id, :name)
                ON CONFLICT (name) DO NOTHING
            """),
            {"id": agency_id, "name": agency_name}
        )

    # ===========================================
    # 2. Seed Brand Guidelines (using dropdown_options table)
    # ===========================================
    brand_guidelines = ["Barclays", "Barclaycard"]

    for idx, brand in enumerate(brand_guidelines):
        brand_id = str(uuid4())
        conn.execute(
            sa.text("""
                INSERT INTO dropdown_options (id, option_type, parent_id, value, display_order)
                VALUES (:id, 'brand_guideline', NULL, :value, :order)
            """),
            {"id": brand_id, "value": brand, "order": idx}
        )

    # ===========================================
    # 3. Seed Channel/Sub-Channel/Proof-Type Hierarchy
    # ===========================================
    dropdown_data = {
        "Social": {
            "Meta": ["In-feed 1x1", "In-feed 4x5", "In-feed 9x16", "Stories"],
            "X (Twitter)": ["In-feed 1x1", "In-feed 16x9"],
            "LinkedIn": ["In-feed 1x1", "In-feed 1.91x1"],
            "TikTok": ["In-feed 9x16"],
            "YouTube": ["Pre-roll", "Bumper", "Display"],
        },
        "Display": {
            "Programmatic": ["300x250", "300x600", "728x90", "160x600", "320x50"],
            "Direct Buy": ["300x250", "300x600", "728x90", "970x250"],
            "Rich Media": ["Expandable", "Interstitial", "Floating"],
        },
        "Email": {
            "Marketing": ["Newsletter", "Promotional", "Product Update"],
            "Transactional": ["Confirmation", "Receipt", "Notification"],
        },
        "Print": {
            "Magazine": ["Full Page", "Half Page", "Quarter Page"],
            "Newspaper": ["Full Page", "Half Page", "Quarter Page"],
            "Direct Mail": ["Letter", "Postcard", "Brochure"],
        },
        "OOH": {
            "Billboard": ["Standard", "Digital", "Spectacular"],
            "Transit": ["Bus Shelter", "Rail", "Airport"],
            "Street Furniture": ["Kiosk", "Bench", "Phone Box"],
        },
    }

    for channel_name, sub_channels in dropdown_data.items():
        # Insert channel
        channel_id = str(uuid4())
        conn.execute(
            sa.text("""
                INSERT INTO dropdown_options (id, option_type, parent_id, value, display_order)
                VALUES (:id, 'channel', NULL, :value, 0)
            """),
            {"id": channel_id, "value": channel_name}
        )

        for sub_channel_name, proof_types in sub_channels.items():
            # Insert sub-channel
            sub_channel_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, :value, 0)
                """),
                {"id": sub_channel_id, "parent_id": channel_id, "value": sub_channel_name}
            )

            for proof_type_name in proof_types:
                # Insert proof type
                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, 0)
                    """),
                    {"id": proof_type_id, "parent_id": sub_channel_id, "value": proof_type_name}
                )


def downgrade() -> None:
    """Remove all seeded data."""
    op.execute("DELETE FROM dropdown_options WHERE option_type IN ('channel', 'sub_channel', 'proof_type', 'brand_guideline')")
    op.execute("DELETE FROM agencies WHERE name IN ('OLIVER Agency', 'Barclays', 'Mindshare', 'Zenith', 'Unassigned')")
