app/database/init_db.py

from app.database.base import engine, Base
from app.models.trading_symbol import TradingSymbol
import app.models
import sqlalchemy as sa
from app.models.email_settings import EmailSettings
from app.models.pending_registration import PendingRegistration

def init_db():
    print("Creating / updating database tables...")
    Base.metadata.create_all(bind=engine)
    print("Tables created (if they didn't exist).")

    # ── SAFE MIGRATIONS ──────────────────────────────────────
    # SQLite ALTER TABLE sirf column add karta hai.
    # Har column ke liye: check karo exist karta hai ya nahi,
    # agar nahi to add karo — error nahi aayega kabhi.
    migrations = [
        # (table_name, column_name, column_definition)
        ("challenges",      "profit_target_pct", "FLOAT DEFAULT 10.0"),
        ("user_challenges", "failure_reason",     "TEXT"),
        ("trades",          "take_profit",        "FLOAT"),
        ("trades",          "stop_loss",          "FLOAT"),
        ("users",           "referred_by_affiliate_id", "INTEGER"),   # ← AFFILIATE SYSTEM (NEW)
        ("users",           "email",              "VARCHAR"),         # ← EMAIL SYSTEM (NEW)
        ("users",           "drawdown_email_enabled", "BOOLEAN DEFAULT 1"),
        ("payout_requests", "account_size",       "FLOAT DEFAULT 0"), # ← PAYOUT BANNER (NEW)
        ("users",           "payout_cycle_start", "DATETIME"),                  # ← MIN TRADING DAYS (NEW)
        ("users",           "payout_days_exempt", "BOOLEAN DEFAULT 0"),         # ← MIN TRADING DAYS (NEW)
        ("payout_settings", "min_trading_days",    "INTEGER DEFAULT 0"),        # ← MIN TRADING DAYS (NEW)
        ("users",           "full_name",           "VARCHAR"),                  # ← FULL NAME (NEW)
        ("pending_registrations", "full_name",      "VARCHAR"),                 # ← FULL NAME (NEW)
        ("challenges",      "profit_split_pct",    "FLOAT DEFAULT 80.0"),       # ← PROFIT SPLIT (NEW)
        ("payout_requests", "received_amount",     "FLOAT DEFAULT 0.0"),        # ← PROFIT SPLIT (NEW)
        ("payout_requests", "split_pct_used",       "FLOAT DEFAULT 100.0"),     # ← PROFIT SPLIT (NEW)
        ("challenges",      "is_hidden",            "BOOLEAN DEFAULT 0"),       # ← HIDDEN PLAN (NEW)

        # ── MULTI-ACCOUNT SUPPORT — NEW ─────────────────────────
        ("user_challenges", "account_size",          "FLOAT"),
        ("user_challenges", "current_balance",        "FLOAT"),
        ("user_challenges", "max_balance_achieved",   "FLOAT"),
        ("user_challenges", "account_id",             "INTEGER"),
        ("trades",          "user_challenge_id",       "INTEGER"),
        ("daily_stats",     "user_challenge_id",       "INTEGER"),
        ("pending_orders",  "user_challenge_id",       "INTEGER"),
        ("users",           "active_account_id",       "INTEGER"),
        ("payout_requests", "user_challenge_id",       "INTEGER"),
    ]

    inspector = sa.inspect(engine)
    with engine.connect() as conn:
        for table, col, col_def in migrations:
            try:
                existing = [c["name"] for c in inspector.get_columns(table)]
                if col not in existing:
                    conn.execute(sa.text(
                        f"ALTER TABLE {table} ADD COLUMN {col} {col_def}"
                    ))
                    conn.commit()
                    print(f"  ✅ Added column '{col}' to '{table}'")
                else:
                    print(f"  — Column '{col}' in '{table}' already exists, skipping.")
            except Exception as e:
                print(f"  ⚠️  Migration skipped ({table}.{col}): {e}")

    print("\n✅ Database ready!")

if __name__ == "__main__":
    init_db()