import sqlite3
from datetime import datetime, date, timedelta
from typing import List, Optional, Sequence
from lic_webapp.database import get_db_connection
from lic_webapp.schemas import RecruitCreate, RecruitUpdate, ExamAttemptCreate

def calculate_booking_countdown(qual_exam_status: str, qual_exam_date_str: Optional[str], booked: bool) -> Optional[int]:
    """
    Calculate how many days are left to book the provincial exam.
    The rule: Must be booked within 5 days of passing the Qualification Exam.
    """
    if qual_exam_status != "Passed" or not qual_exam_date_str:
        return None
    
    if booked:
        return None # Already booked, countdown no longer applies
        
    try:
        qual_date = date.fromisoformat(qual_exam_date_str)
        today = date.today()
        # Deadline is 5 days from passing (inclusive or exclusive? Usually within 5 days of passing means: pass_date + 5 days)
        deadline = qual_date + timedelta(days=5)
        delta = (deadline - today).days
        return delta
    except Exception:
        return None

def row_to_dict(row: sqlite3.Row) -> dict:
    return {key: row[key] for key in row.keys()}

def get_exam_attempts(recruit_id: int) -> List[dict]:
    """
    Fetch all exam attempts for a specific recruit.
    """
    conn = get_db_connection()
    try:
        cursor = conn.execute(
            "SELECT * FROM exam_history WHERE recruit_id = ? ORDER BY exam_date DESC, id DESC;",
            (recruit_id,)
        )
        return [row_to_dict(r) for r in cursor.fetchall()]
    finally:
        conn.close()

def get_all_exam_attempts(conn: sqlite3.Connection) -> dict:
    histories = {}
    for row in conn.execute('SELECT * FROM exam_history ORDER BY exam_date DESC, id DESC'):
        histories.setdefault(row['recruit_id'], []).append(row_to_dict(row))
    return histories

def get_recruits(rvp: Optional[str] = None) -> List[dict]:
    """
    Fetch all recruits and compute countdown indicators.
    """
    conn = get_db_connection()
    try:
        query = "SELECT * FROM recruits"
        params = []
        if rvp:
            query += " WHERE rvp = ?"
            params.append(rvp)
        cursor = conn.execute(query + " ORDER BY last_name ASC, first_name ASC;", params)
        recruits = [row_to_dict(r) for r in cursor.fetchall()]
        
        histories = get_all_exam_attempts(conn)
        for r in recruits:
            r["exam_attempts"] = histories.get(r["id"], [])
            # Compute days left to book provincial exam
            r["prov_booking_days_left"] = calculate_booking_countdown(
                r["qual_exam_status"],
                r["qual_exam_date"],
                bool(r["prov_exam_booked"])
            )
        return recruits
    finally:
        conn.close()

def get_recruit(recruit_id: int, rvp: Optional[str] = None) -> Optional[dict]:
    """
    Fetch a single recruit by ID.
    """
    conn = get_db_connection()
    try:
        query = "SELECT * FROM recruits WHERE id = ?"
        params = [recruit_id]
        if rvp:
            query += " AND rvp = ?"
            params.append(rvp)
        cursor = conn.execute(query + ";", params)
        row = cursor.fetchone()
        if not row:
            return None
        
        r = row_to_dict(row)
        r["exam_attempts"] = get_exam_attempts(r["id"])
        r["prov_booking_days_left"] = calculate_booking_countdown(
            r["qual_exam_status"],
            r["qual_exam_date"],
            bool(r["prov_exam_booked"])
        )
        return r
    finally:
        conn.close()

def create_recruit(r_in: RecruitCreate) -> dict:
    """
    Insert a new recruit.
    """
    conn = get_db_connection()
    try:
        cursor = conn.cursor()
        cursor.execute(
            """
            INSERT INTO recruits (first_name, last_name, email, phone, iba_date, iba_status, solution_number, rvp)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?);
            """,
            (r_in.first_name, r_in.last_name, r_in.email, r_in.phone, r_in.iba_date, r_in.iba_status, r_in.solution_number, r_in.rvp)
        )
        recruit_id = cursor.lastrowid
        conn.commit()
    finally:
        conn.close()
        
    return get_recruit(recruit_id)

def create_recruits(recruits: Sequence[RecruitCreate]) -> int:
    """Insert a validated collection of recruits as one transaction."""
    if not recruits:
        return 0

    conn = get_db_connection()
    try:
        conn.executemany(
            """
            INSERT INTO recruits (first_name, last_name, email, phone, iba_date, iba_status, solution_number, rvp)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?);
            """,
            [(r.first_name, r.last_name, r.email, r.phone, r.iba_date,
              r.iba_status, r.solution_number, r.rvp) for r in recruits],
        )
        conn.commit()
        return len(recruits)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

def get_existing_solution_numbers(solution_numbers: Sequence[str]) -> set[str]:
    """Return normalized solution numbers that already belong to a recruit."""
    if not solution_numbers:
        return set()

    conn = get_db_connection()
    try:
        placeholders = ", ".join("?" for _ in solution_numbers)
        rows = conn.execute(
            f"SELECT solution_number FROM recruits "
            f"WHERE solution_number COLLATE NOCASE IN ({placeholders});",
            list(solution_numbers),
        ).fetchall()
        return {row["solution_number"].casefold() for row in rows}
    finally:
        conn.close()

def update_recruit(recruit_id: int, r_in: RecruitUpdate) -> Optional[dict]:
    """
    Dynamically build and execute parameterized SQL to update a recruit's state.
    """
    update_data = r_in.model_dump(exclude_unset=True)
    if not update_data:
        return get_recruit(recruit_id)

    # RVP billing is a fixed, downstream charge: one $50 invoice only after
    # the recruit's life-license reimbursement has been approved.
    if update_data.get('rvp_invoice_status') == 'Invoiced':
        existing = get_recruit(recruit_id)
        if not existing:
            return None
        if existing['reimbursement_status'] != 'Approved':
            raise ValueError('The RVP can be invoiced only after reimbursement is approved.')
        update_data['rvp_invoice_amount'] = 50.0

    if update_data.get('mf_reimbursement_status') == 'Approved':
        existing = get_recruit(recruit_id)
        if not existing:
            return None
        registered = update_data.get('mf_registration_date', existing['mf_registration_date'])
        leader_at_signup = update_data.get('mf_district_leader_at_signup', bool(existing['mf_district_leader_at_signup']))
        leader_date = update_data.get('mf_district_leader_date', existing['mf_district_leader_date'])
        eligible = leader_at_signup
        if not eligible and registered and leader_date:
            eligible = date.fromisoformat(leader_date) <= date.fromisoformat(registered) + timedelta(days=730)
        if not eligible:
            raise ValueError('Mutual fund reimbursement requires District Leader status at signup or within 2 years.')
        
    # Update timestamp
    update_data["updated_at"] = datetime.now().isoformat()
    
    query_parts = []
    params = []
    for key, val in update_data.items():
        query_parts.append(f"{key} = ?")
        # Handle conversion from bool to int for SQLite
        if isinstance(val, bool):
            params.append(1 if val else 0)
        else:
            params.append(val)
            
    params.append(recruit_id)
    query = f"UPDATE recruits SET {', '.join(query_parts)} WHERE id = ?;"
    
    conn = get_db_connection()
    try:
        cursor = conn.cursor()
        for prefix, exam_type in [('qual', 'Qualification'), ('prov', 'Provincial')]:
            latest = conn.execute(
                'SELECT result, exam_date FROM exam_history WHERE recruit_id = ? AND exam_type = ? ORDER BY exam_date DESC, id DESC LIMIT 1',
                (recruit_id, exam_type)
            ).fetchone()
            if latest:
                expected = {f'{prefix}_exam_status': {'Pass': 'Passed', 'Fail': 'Failed'}[latest['result']],
                            f'{prefix}_exam_date': latest['exam_date']}
                if any(key in update_data and update_data[key] != value for key, value in expected.items()):
                    raise ValueError('Exam status and date must match the latest recorded attempt.')
        cursor.execute(query, params)
        if cursor.rowcount == 0:
            return None
        conn.commit()
    finally:
        conn.close()
        
    return get_recruit(recruit_id)

def add_exam_attempt(recruit_id: int, attempt: ExamAttemptCreate) -> Optional[dict]:
    """
    Log an exam attempt and update the recruit's cached state automatically.
    """
    recruit = get_recruit(recruit_id)
    if not recruit:
        return None
        
    conn = get_db_connection()
    try:
        cursor = conn.cursor()
        
        # Log into history
        cursor.execute(
            """
            INSERT INTO exam_history (recruit_id, exam_type, exam_date, result, notes)
            VALUES (?, ?, ?, ?, ?);
            """,
            (recruit_id, attempt.exam_type, attempt.exam_date, attempt.result, attempt.notes)
        )
        
        latest = conn.execute(
            'SELECT result, exam_date FROM exam_history WHERE recruit_id = ? AND exam_type = ? ORDER BY exam_date DESC, id DESC LIMIT 1',
            (recruit_id, attempt.exam_type)
        ).fetchone()
        prefix = 'qual' if attempt.exam_type == 'Qualification' else 'prov'
        cursor.execute(
            f'UPDATE recruits SET {prefix}_exam_status = ?, {prefix}_exam_date = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
            ({'Pass': 'Passed', 'Fail': 'Failed'}[latest['result']], latest['exam_date'], recruit_id)
        )

        conn.commit()
    finally:
        conn.close()
        
    return get_recruit(recruit_id)

def delete_recruit(recruit_id: int) -> bool:
    """
    Delete a recruit by ID.
    """
    conn = get_db_connection()
    try:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM recruits WHERE id = ?;", (recruit_id,))
        success = cursor.rowcount > 0
        conn.commit()
        return success
    finally:
        conn.close()

def get_next_class_report(rvp: Optional[str] = None) -> List[dict]:
    """
    Return all recruits who are registered for the LLQP class (llqp_registered = 1)
    but have NOT yet completed the course (not all 4 attendance days checked).
    Ordered by llqp_class_date ascending so the most imminent class is first.
    """
    conn = get_db_connection()
    try:
        cursor = conn.execute(
            """
            SELECT *
            FROM recruits
            WHERE llqp_registered = 1
              AND (w1_sat_attended = 0 OR w1_sun_attended = 0 OR w2_sat_attended = 0 OR w2_sun_attended = 0)
              AND (? IS NULL OR rvp = ?)
            ORDER BY llqp_class_date ASC, last_name ASC, first_name ASC;
            """, (rvp, rvp)
        )
        return [row_to_dict(r) for r in cursor.fetchall()]
    finally:
        conn.close()

def get_weekly_status_report(rvp: Optional[str] = None) -> List[dict]:
    """
    Return all recruits sorted first by RVP (nulls last), then by last name.
    Includes computed pipeline stage label for each recruit.
    """
    conn = get_db_connection()
    try:
        cursor = conn.execute(
            """
            SELECT * FROM recruits
            WHERE (? IS NULL OR rvp = ?)
            ORDER BY
                CASE WHEN rvp IS NULL OR rvp = '' THEN 1 ELSE 0 END ASC,
                rvp ASC,
                last_name ASC,
                first_name ASC;
            """, (rvp, rvp)
        )
        rows = [row_to_dict(r) for r in cursor.fetchall()]
        histories = get_all_exam_attempts(conn)
        # Attach exam attempts and booking countdown
        for r in rows:
            r["exam_attempts"] = histories.get(r["id"], [])
            r["prov_booking_days_left"] = calculate_booking_countdown(
                r["qual_exam_status"],
                r["qual_exam_date"],
                bool(r["prov_exam_booked"])
            )
        return rows
    finally:
        conn.close()