import subprocess
import sys
import json
import os

from app.database import engine
from app.models import Base, User, Producer, Client
from sqlalchemy.orm import Session

print("Initializing database...")
Base.metadata.create_all(bind=engine)

# ── Migrate: add new columns if missing ──────────────────
import sqlite3

def add_column_if_missing(db_path, table, column, col_type):
    conn = sqlite3.connect(db_path)
    cursor = conn.execute(f"PRAGMA table_info({table})")
    columns = [row[1] for row in cursor.fetchall()]
    if column not in columns:
        conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {col_type}")
        conn.commit()
        print(f"  Added column {table}.{column}")
    conn.close()

db_path = "/data/app.db"
print("Checking for schema migrations...")
add_column_if_missing(db_path, "producers", "email", "VARCHAR(255)")
add_column_if_missing(db_path, "approvals", "approval_token", "VARCHAR(64)")
# Add unique index separately (SQLite doesn't support UNIQUE in ALTER TABLE)
conn = sqlite3.connect(db_path)
try:
    conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS ix_approvals_approval_token ON approvals(approval_token)")
    conn.commit()
    print("  Added unique index on approvals.approval_token")
except Exception as e:
    print(f"  Index already exists or skipped: {e}")
conn.close()
print("Schema migrations complete.")


s = Session(engine)

# Seed config tables on first run
if not s.query(User).first():
    print("First run - seeding database...")
    from app.seed_db import seed
    seed(s)
else:
    print("Database exists.")

# ── Data migration: fix commission tier labels ───────────
from app.models import CommissionAgreement, CommissionTier
print("Checking commission data migrations...")

# Fix Referral/Origination Fee: "Year 1-2" → "Year 1 & Year 2"
ref_agr = s.query(CommissionAgreement).filter(
    CommissionAgreement.name == "Referral / Origination Fee Agreement"
).first()
if ref_agr:
    updated = 0
    for tier in ref_agr.tiers:
        if tier.years_label == "Year 1-2":
            tier.years_label = "Year 1 & Year 2"
            updated += 1
    if updated:
        s.commit()
        print(f"  Fixed Referral/Origination Fee: {updated} tier labels updated to 'Year 1 & Year 2'")

# Fix Life Origination Fee: "All Years" at 20% → "Year 1" at 20% + "Year 2+" Ended
life_agr = s.query(CommissionAgreement).filter(
    CommissionAgreement.name == "Life Origination Fee Agreement"
).first()
if life_agr:
    all_years_tiers = [t for t in life_agr.tiers if t.years_label == "All Years"]
    if all_years_tiers:
        # Update existing tier to Year 1
        all_years_tiers[0].years_label = "Year 1"
        # Add Year 2+ Ended tier
        max_sort = max((t.sort_order for t in life_agr.tiers), default=0)
        ended_tier = CommissionTier(
            agreement_id=life_agr.id,
            producer_role="P1",
            years_label="Year 2+",
            commission=None,
            credit=None,
            agreement_label="Ended",
            note="Life Referral is 20% Year 1 only. Year 2 it is removed.",
            sort_order=max_sort + 1,
        )
        s.add(ended_tier)
        s.commit()
        print("  Fixed Life Origination Fee: Year 1 at 20%, Year 2+ Ended")

print("Commission data migrations complete.")

# ── Auto-assign producer emails (first initial + last name @snellingswalters.com) ──
import re
print("Checking producer email assignments...")
from app.models import Producer as ProdModel
producers_without_email = s.query(ProdModel).filter(
    ProdModel.is_active == True,
    (ProdModel.email == None) | (ProdModel.email == "")
).all()
email_count = 0
for prod in producers_without_email:
    name = prod.name.strip()
    parts = name.split()
    if len(parts) < 2:
        # Single-word or unclear name — use fallback
        prod.email = "ai@snellingswalters.com"
        email_count += 1
        continue
    # Non-person entries get fallback email
    lower_name = name.lower()
    if any(kw in lower_name for kw in ["uac", "house", "account"]):
        prod.email = "ai@snellingswalters.com"
        email_count += 1
        continue
    first_clean = re.sub(r'[^a-zA-Z]', '', parts[0]).lower()
    last_clean = re.sub(r'[^a-zA-Z]', '', parts[-1]).lower()
    if first_clean and last_clean:
        prod.email = first_clean[0] + last_clean + "@snellingswalters.com"
    else:
        prod.email = "ai@snellingswalters.com"
    email_count += 1
if email_count:
    s.commit()
    print(f"  Auto-assigned {email_count} producer emails.")
else:
    print("  All producers already have emails.")

# Import refresh data if present (uploaded by Colab daily refresh)
REFRESH_FILE = "refresh_data.json"
if os.path.exists(REFRESH_FILE):
    print(f"Found {REFRESH_FILE} - importing producers & clients...")
    try:
        with open(REFRESH_FILE) as f:
            data = json.load(f)

        # Upsert Producers
        s.query(Producer).update({Producer.is_active: False})
        prod_count = 0
        for p in data.get("producers", []):
            existing = s.query(Producer).filter(Producer.code == p["code"]).first()
            if existing:
                existing.prefix = p["prefix"]
                existing.suffix = p["suffix"]
                existing.name = p["name"]
                existing.is_active = True
            else:
                s.add(Producer(
                    code=p["code"],
                    prefix=p["prefix"],
                    suffix=p["suffix"],
                    name=p["name"],
                    is_active=True,
                ))
            prod_count += 1

        # Upsert Clients
        s.query(Client).update({Client.is_active: False})
        cl_count = 0
        for c in data.get("clients", []):
            existing = s.query(Client).filter(Client.lookup_code == c["lookup_code"]).first()
            if existing:
                existing.name = c["name"]
                existing.is_active = True
            else:
                s.add(Client(
                    lookup_code=c["lookup_code"],
                    name=c["name"],
                    is_active=True,
                ))
            cl_count += 1

        s.commit()
        print(f"Imported {prod_count} producers, {cl_count} clients.")
    except Exception as e:
        print(f"Error importing refresh data: {e}")
        s.rollback()
else:
    print("No refresh_data.json found - skipping data import.")

s.close()

print("Starting server...")
subprocess.run([
    sys.executable, "-m", "uvicorn",
    "app.main:app",
    "--host", "0.0.0.0",
    "--port", "7860"
])