app/core/startup/db.py

"""core/startup/db.py — Database initialization on startup.

Runs on application startup (called from lifespan).
Uses safe ALTER TABLE ADD COLUMN (errors swallowed = column already exists).
New columns MUST be added here when added to any model.
"""

import structlog
from sqlalchemy import text

logger = structlog.get_logger(__name__)

# ── All incremental column migrations ────────────────────────────────────────
# Format: (table, column, sqlite_type)
# Safe: if column already exists the ALTER TABLE fails silently.
_COLUMN_MIGRATIONS: list[tuple[str, str, str]] = [
    # book_platform_listings (original migrations)
    ("book_platform_listings", "list_price",      "VARCHAR(32)"),
    ("book_platform_listings", "price_currency",   "VARCHAR(8)"),

    # users — Phase 1 billing fields
    ("users", "stripe_customer_id",      "VARCHAR(255)"),
    ("users", "stripe_subscription_id",  "VARCHAR(255)"),
    ("users", "stripe_plan_id",          "VARCHAR(100)"),
    ("users", "payment_grace_until",     "DATETIME"),
    ("users", "remove_branding",         "BOOLEAN DEFAULT 0"),
    ("users", "custom_widget_css",       "TEXT"),

    # users — Phase 3 white-label
    ("users", "custom_domain",           "VARCHAR(255)"),
    ("users", "domain_verified",         "BOOLEAN DEFAULT 0"),

    # users — Phase 4 agency / SSO / GDPR
    ("users", "stripe_connect_account_id", "VARCHAR(255)"),
    ("users", "notes",                   "TEXT"),

    # referral_links (Phase 3)
    ("referral_links", "stripe_coupon_id",  "VARCHAR(255)"),
    ("referral_links", "clicks",            "INTEGER DEFAULT 0"),
    ("referral_links", "signups",           "INTEGER DEFAULT 0"),
    ("referral_links", "is_active",         "BOOLEAN DEFAULT 1"),

    # referral_commissions (Phase 3)
    ("referral_commissions", "paid_at",     "DATETIME"),

    # client_access — SuperAdmin grants (notes column used by grants.py)
    ("client_access", "notes",           "TEXT"),
    ("client_access", "auto_renew",      "BOOLEAN DEFAULT 0"),
    ("client_access", "bonus_tokens",    "INTEGER DEFAULT 0"),
    ("client_access", "plan_id",         "VARCHAR(50)"),
    ("client_access", "stripe_subscription_id", "VARCHAR(255)"),
]


async def init_db() -> None:
    """Create all tables then apply incremental column migrations.

    Safe to run on every startup:
    - create_all is idempotent (skips existing tables)
    - ALTER TABLE errors are swallowed (column already exists)
    """
    from app.models.base import Base
    from app.dependencies import get_engine

    engine = get_engine()

    async with engine.begin() as conn:
        # Create any brand-new tables (new models from this deploy)
        await conn.run_sync(Base.metadata.create_all)

        # Add new columns to existing tables
        await _run_column_migrations(conn)

    logger.info("Database tables ready")


async def _run_column_migrations(conn) -> None:
    """Execute ALTER TABLE ADD COLUMN statements; silently skip if column exists."""
    added, skipped = 0, 0
    for table, column, col_type in _COLUMN_MIGRATIONS:
        try:
            await conn.execute(
                text(f"ALTER TABLE {table} ADD COLUMN {column} {col_type}")
            )
            added += 1
        except Exception:
            skipped += 1  # column already exists — normal on every restart after first

    if added:
        logger.info("Column migrations applied", added=added, skipped=skipped)