import sqlite3
import datetime as dt
from passlib.context import CryptContext
from ..database import _conn, _table_columns

# We need pwd_context for create_user, but pwd_context is usually in deps.
# Ideally crud shouldn't depend on deps.
# Let's instantiate a local context or minimal one, OR pass the hash in.
# Better: Pass the hashed password to create_user or move pwd_context to database or utils? 
# pwd_context is simple config. Let's put it in a small utils or just keep it here for now if widely used.
# But deps uses it for verification.
# Let's import it from a new common place 'security.py' or just duplicate the config line?
# Duplicating the line is safe-ish but bad practice.
# Let's create `app/security.py` or just put it in `app/crud.py` since it's used for creation?
# Actually main.py used it for login (verification).
# Let's define it here or in database? No, database is infra.
# I'll put it in deps.py and import it? No, circular.
# I'll put it in `app/security.py`? That wasn't in the plan.
# I'll just instantiate it in both or put it in database.py? Use database.py for "shared config".
# Let's put it in database for simplicity as it's a singleton config.
# Wait, I didn't put it in database.py.
# I will initialize it locally in crud.py for now to avoid editing database.py again immediately.
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

def get_user_by_email(email: str):
    with _conn() as conn:
        cur = conn.execute("""
            SELECT u.id,u.name,u.email,u.password_hash,u.created_at,u.role,u.first_name,u.last_name,
                   p.phone,p.avatar,p.city,p.dni,p.birth_date,
                   s.team,s.category,s.position,s.dominant_foot,s.strengths,s.weaknesses,s.injury_history,s.training_type,
                   h.physical_work,h.physical_work_details,h.smoking,h.alcohol,h.recovery,h.chest_pain,h.discomfort,
                   c.whatsapp_content,c.video_permission,c.privacy_acceptance,c.data_confirmation,c.agreement,
                   g.occupation,g.study_place,g.parent_name,g.parent_email,
                   m.found_us,m.enjoyment,m.nerves_confidence,m.additional_comments,
                   COALESCE(f.matricula_eur,0.0) AS matricula_eur,
                   CAST(ROUND(COALESCE(f.matricula_eur,0.0)*100) AS INTEGER) AS matricula_cents
            FROM users u
            LEFT JOIN user_profile p   ON p.user_id=u.id
            LEFT JOIN user_sports s    ON s.user_id=u.id
            LEFT JOIN user_health h    ON h.user_id=u.id
            LEFT JOIN user_consent c   ON c.user_id=u.id
            LEFT JOIN user_guardians g ON g.user_id=u.id
            LEFT JOIN user_marketing m ON m.user_id=u.id
            LEFT JOIN user_finance f   ON f.user_id=u.id
            WHERE LOWER(u.email)=LOWER(?)
        """, (email,))
        return cur.fetchone()

def get_user_by_id(user_id: int):
    with _conn() as conn:
        cur = conn.execute("""
            SELECT u.id,u.name,u.email,u.password_hash,u.created_at,u.role,u.first_name,u.last_name,
                   p.phone,p.avatar,p.city,p.dni,p.birth_date,
                   s.team,s.category,s.position,s.dominant_foot,s.strengths,s.weaknesses,s.injury_history,s.training_type,
                   h.physical_work,h.physical_work_details,h.smoking,h.alcohol,h.recovery,h.chest_pain,h.discomfort,
                   c.whatsapp_content,c.video_permission,c.privacy_acceptance,c.data_confirmation,c.agreement,
                   g.occupation,g.study_place,g.parent_name,g.parent_email,
                   m.found_us,m.enjoyment,m.nerves_confidence,m.additional_comments,
                   COALESCE(f.matricula_eur,0.0) AS matricula_eur,
                   CAST(ROUND(COALESCE(f.matricula_eur,0.0)*100) AS INTEGER) AS matricula_cents
            FROM users u
            LEFT JOIN user_profile p   ON p.user_id=u.id
            LEFT JOIN user_sports s    ON s.user_id=u.id
            LEFT JOIN user_health h    ON h.user_id=u.id
            LEFT JOIN user_consent c   ON c.user_id=u.id
            LEFT JOIN user_guardians g ON g.user_id=u.id
            LEFT JOIN user_marketing m ON m.user_id=u.id
            LEFT JOIN user_finance f   ON f.user_id=u.id
            WHERE u.id=?
        """, (user_id,))
        return cur.fetchone()

def _upsert_satellites_empty(conn, user_id: int):
    conn.execute("INSERT OR IGNORE INTO user_profile(user_id) VALUES (?)", (user_id,))
    conn.execute("INSERT OR IGNORE INTO user_sports(user_id) VALUES (?)", (user_id,))
    conn.execute("INSERT OR IGNORE INTO user_health(user_id) VALUES (?)", (user_id,))
    conn.execute("INSERT OR IGNORE INTO user_consent(user_id) VALUES (?)", (user_id,))
    conn.execute("INSERT OR IGNORE INTO user_guardians(user_id) VALUES (?)", (user_id,))
    conn.execute("INSERT OR IGNORE INTO user_marketing(user_id) VALUES (?)", (user_id,))
    conn.execute("INSERT OR IGNORE INTO user_finance(user_id, matricula_eur) VALUES (?, 0.0)", (user_id,))

def create_user(name: str, email: str, password: str, role: str = "alumno",
                first_name: str | None = None, last_name: str | None = None):
    password_hash = pwd_context.hash(password)
    with _conn() as conn:
        cols = _table_columns(conn, "users")
        created_at = dt.datetime.utcnow().isoformat()
        if "first_name" in cols and "last_name" in cols:
            cur = conn.execute(
                "INSERT INTO users (name, email, password_hash, created_at, role, first_name, last_name) "
                "VALUES (?,?,?,?,?,?,?)",
                (name, email, password_hash, created_at, role, first_name, last_name),
            )
        else:
            cur = conn.execute(
                "INSERT INTO users (name, email, password_hash, created_at, role) VALUES (?,?,?,?,?)",
                (name, email, password_hash, created_at, role),
            )
        uid = cur.lastrowid
        _upsert_satellites_empty(conn, uid)
        conn.commit()

def count_admins() -> int:
    with _conn() as conn:
        row = conn.execute("SELECT COUNT(1) AS c FROM users WHERE role='admin'").fetchone()
        return int(row["c"] or 0)

def is_admin(me) -> bool:
    return str(me["role"]) == "admin"

def set_user_role(user_id: int, role: str):
    with _conn() as conn:
        conn.execute("UPDATE users SET role = ? WHERE id = ?", (role, user_id))
        conn.commit()