"""add org timezone + autoresponse_translations

Public-schema change:
  - organizations.timezone VARCHAR(50) NOT NULL DEFAULT 'UTC'

Tenant-schema change:
  - autoresponse_translations: per-language body_html variants for a template.
    id PK, template_id FK→autoresponse_templates.id ON DELETE CASCADE,
    lang VARCHAR(10), body_html TEXT, created_at/updated_at,
    UNIQUE(template_id, lang).

Public-schema run is idempotent (existence-guarded). Tenant-schema run
no-ops in public schema (mirrors ar002 pattern).

Revision ID: ar003
Revises: ar002
Create Date: 2026-07-15
"""
from alembic import op
import sqlalchemy as sa

revision = 'ar003'
down_revision = 'ar002'
branch_labels = None
depends_on = None


def _public_column_exists(conn, table, column):
    return conn.execute(sa.text(
        "SELECT 1 FROM information_schema.columns "
        "WHERE table_schema='public' AND table_name=:t AND column_name=:c"
    ), {"t": table, "c": column}).fetchone() is not None


def _in_public_schema(conn):
    """True when this migration is running against the public schema."""
    return conn.execute(sa.text("SELECT current_schema()")).scalar() == 'public'


def upgrade():
    conn = op.get_bind()

    # ── Public-schema changes ──────────────────────────────────────────────
    if not _public_column_exists(conn, 'organizations', 'timezone'):
        op.add_column(
            'organizations',
            sa.Column('timezone', sa.String(50), nullable=False, server_default='UTC'),
            schema='public',
        )

    # ── Tenant-schema changes ──────────────────────────────────────────────
    if _in_public_schema(conn):
        return  # no tenant tables in public

    insp = sa.inspect(conn)
    existing = set(insp.get_table_names())

    if 'autoresponse_translations' not in existing:
        op.create_table(
            'autoresponse_translations',
            sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
            sa.Column('template_id', sa.Integer,
                      sa.ForeignKey('autoresponse_templates.id', ondelete='CASCADE'),
                      nullable=False),
            sa.Column('lang', sa.String(10), nullable=False),
            sa.Column('body_html', sa.Text, nullable=False),
            sa.Column('created_at', sa.DateTime, nullable=False,
                      server_default=sa.func.now()),
            sa.Column('updated_at', sa.DateTime, nullable=False,
                      server_default=sa.func.now()),
            sa.UniqueConstraint('template_id', 'lang', name='uq_autoresponse_translation_lang'),
        )
        op.create_index(
            'ix_autoresponse_translations_template_id',
            'autoresponse_translations',
            ['template_id'],
        )


def downgrade():
    conn = op.get_bind()

    if not _in_public_schema(conn):
        op.drop_index('ix_autoresponse_translations_template_id',
                      table_name='autoresponse_translations')
        op.drop_table('autoresponse_translations')

    op.drop_column('organizations', 'timezone', schema='public')