"""Drop SEO columns from platform_settings and tighten contact columns to NOT NULL

Revision ID: 0028
Revises: 0027
Create Date: 2026-06-29
"""
import sqlalchemy as sa
from alembic import op

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


def upgrade() -> None:
    # ── 1. Drop SEO / announcement columns ──────────────────────────────────
    op.drop_column("platform_settings", "meta_title")
    op.drop_column("platform_settings", "meta_description")
    op.drop_column("platform_settings", "announcement")

    # ── 2. Coerce any NULL contact fields to '' before adding NOT NULL ────────
    op.execute(
        """
        UPDATE platform_settings
        SET
            email   = COALESCE(email,   ''),
            phone   = COALESCE(phone,   ''),
            office  = COALESCE(office,  ''),
            hours   = COALESCE(hours,   ''),
            linkedin = COALESCE(linkedin, ''),
            twitter  = COALESCE(twitter,  '')
        WHERE
            email    IS NULL
            OR phone   IS NULL
            OR office  IS NULL
            OR hours   IS NULL
            OR linkedin IS NULL
            OR twitter  IS NULL
        """
    )

    # ── 3. Add NOT NULL constraints with an empty-string server_default ───────
    #       server_default guards against future INSERT statements that omit the
    #       column; the UPDATE above handles all pre-existing rows.
    for col, col_type in [
        ("email",    sa.String(255)),
        ("phone",    sa.String(50)),
        ("office",   sa.String(255)),
        ("hours",    sa.String(255)),
        ("linkedin", sa.String(500)),
        ("twitter",  sa.String(500)),
    ]:
        op.alter_column(
            "platform_settings",
            col,
            existing_type=col_type,
            nullable=False,
            server_default=sa.text("''"),
        )


def downgrade() -> None:
    # ── 1. Restore contact columns to nullable (remove server_default too) ────
    for col, col_type in [
        ("twitter",  sa.String(500)),
        ("linkedin", sa.String(500)),
        ("hours",    sa.String(255)),
        ("office",   sa.String(255)),
        ("phone",    sa.String(50)),
        ("email",    sa.String(255)),
    ]:
        op.alter_column(
            "platform_settings",
            col,
            existing_type=col_type,
            nullable=True,
            server_default=None,
        )

    # ── 2. Re-add the three dropped SEO / announcement columns ───────────────
    op.add_column(
        "platform_settings",
        sa.Column("meta_title", sa.String(255), nullable=True),
    )
    op.add_column(
        "platform_settings",
        sa.Column("meta_description", sa.Text, nullable=True),
    )
    op.add_column(
        "platform_settings",
        sa.Column("announcement", sa.Text, nullable=True),
    )