import os, sqlite3, json
from datetime import datetime
from functools import wraps
from flask import Flask, render_template, request, redirect, url_for, session, flash, Response

APP_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(APP_DIR, "crew_profiles.sqlite3")
app = Flask(__name__)
app.secret_key = os.environ.get("SPACE_ACADEMY_SECRET", "change-this-before-real-use")

CORE_SKILLS = [('problem_solving', 'Problem Solving', 'finding solutions when things don’t work', 'Thinking & Problem Solving'), ('logical_thinking', 'Logical Thinking', 'working step-by-step', 'Thinking & Problem Solving'), ('decision_making', 'Decision Making', 'choosing the best option', 'Thinking & Problem Solving'), ('practical_building', 'Practical Building', 'constructing or assembling things', 'Making & Doing'), ('using_tools', 'Using Tools & Equipment', 'working safely and effectively', 'Making & Doing'), ('attention_to_detail', 'Accuracy & Attention to Detail', 'getting things right', 'Making & Doing'), ('design', 'Design', 'planning how something should look or work', 'Creative & Visual'), ('visualisation', 'Visualisation', 'imagining and sketching ideas', 'Creative & Visual'), ('creativity', 'Creativity', 'generating new or improved ideas', 'Creative & Visual'), ('measurement_data', 'Measurement & Data', 'collecting and using information', 'Maths & Data'), ('numeracy', 'Numeracy', 'applying maths in real situations', 'Maths & Data'), ('communication', 'Communication', 'explaining ideas clearly', 'Communication & Collaboration'), ('teamwork', 'Teamwork', 'working effectively with others', 'Communication & Collaboration'), ('organisation', 'Organisation', 'planning tasks and steps', 'Organisation & Delivery'), ('time_management', 'Time Management', 'keeping work on track', 'Organisation & Delivery')]
DEVELOPING_SKILLS = [('leadership', 'Leadership', 'guiding and supporting a team', 'Leadership & Coordination'), ('team_coordination', 'Team Coordination', 'organising people and roles', 'Leadership & Coordination'), ('delegation', 'Delegation', 'assigning tasks effectively', 'Leadership & Coordination'), ('project_management', 'Project Management', 'planning and tracking progress', 'Planning & Project Delivery'), ('task_planning', 'Task Planning', 'breaking work into steps', 'Planning & Project Delivery'), ('timeline_management', 'Timeline Management', 'managing deadlines', 'Planning & Project Delivery'), ('systems_thinking', 'Systems Thinking', 'understanding how parts connect', 'Systems & Big Picture Thinking'), ('understanding_task_links', 'Understanding Task Links', 'knowing what depends on what', 'Systems & Big Picture Thinking'), ('resource_management', 'Resource Management', 'using people and materials effectively', 'Systems & Big Picture Thinking'), ('mission_monitoring', 'Mission Monitoring', 'tracking overall progress', 'Operations & Mission Control'), ('decision_under_pressure', 'Decision Under Pressure', 'making choices when it matters', 'Operations & Mission Control'), ('problem_escalation', 'Problem Escalation', 'raising issues effectively', 'Operations & Mission Control'), ('professional_communication', 'Professional Communication', 'clear, purposeful communication', 'Communication Advanced'), ('briefing_reporting', 'Briefing & Reporting', 'summarising progress for others', 'Communication Advanced'), ('persuasion_influence', 'Persuasion & Influence', 'getting others on board', 'Communication Advanced'), ('self_assessment', 'Self-Assessment', 'recognising strengths and improvements', 'Reflection & Growth'), ('peer_review', 'Peer Review', 'giving constructive feedback', 'Reflection & Growth'), ('resilience', 'Resilience', 'continuing after setbacks', 'Reflection & Growth')]
ALL_SKILLS = CORE_SKILLS + DEVELOPING_SKILLS
SKILL_LOOKUP = {s[0]: {"key":s[0],"name":s[1],"description":s[2],"category":s[3],"type":"core" if s in CORE_SKILLS else "developing"} for s in ALL_SKILLS}

def now(): return datetime.utcnow().isoformat(timespec="seconds")

def db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = db()
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        team TEXT NOT NULL,
        icon_json TEXT NOT NULL,
        pin TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'student',
        created_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS student_skills (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INTEGER NOT NULL,
        skill_key TEXT NOT NULL,
        skill_type TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'approved',
        evidence TEXT,
        peer_comment TEXT,
        teacher_comment TEXT,
        created_at TEXT NOT NULL,
        approved_at TEXT,
        UNIQUE(student_id, skill_key)
    );
    CREATE TABLE IF NOT EXISTS mission_diary (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INTEGER NOT NULL,
        entry TEXT NOT NULL,
        created_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS skill_applications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INTEGER NOT NULL,
        skill_key TEXT NOT NULL,
        evidence TEXT NOT NULL,
        peer_comment TEXT,
        status TEXT NOT NULL DEFAULT 'pending',
        teacher_comment TEXT,
        created_at TEXT NOT NULL,
        decided_at TEXT
    );
    CREATE TABLE IF NOT EXISTS job_posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        team TEXT NOT NULL,
        description TEXT NOT NULL,
        required_skills_json TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'open',
        created_by INTEGER,
        created_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS job_applications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        job_id INTEGER NOT NULL,
        student_id INTEGER NOT NULL,
        message TEXT,
        status TEXT NOT NULL DEFAULT 'interested',
        created_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS ai_summaries (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INTEGER NOT NULL,
        summary TEXT NOT NULL,
        created_at TEXT NOT NULL
    );
    """)
    
    # Sprint 2: Academy / team support. Safe migrations for existing SQLite databases.
    def ensure_column(table, column, definition):
        cols = [r["name"] for r in conn.execute(f"PRAGMA table_info({table})").fetchall()]
        if column not in cols:
            conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")

    ensure_column("students", "academy_id", "INTEGER DEFAULT 1")
    ensure_column("students", "team_id", "INTEGER")
    ensure_column("job_posts", "academy_id", "INTEGER DEFAULT 1")
    ensure_column("mission_diary", "academy_id", "INTEGER DEFAULT 1")
    ensure_column("skill_applications", "academy_id", "INTEGER DEFAULT 1")

    conn.execute("""
    CREATE TABLE IF NOT EXISTS academies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        club_code TEXT NOT NULL UNIQUE,
        mission_phase TEXT NOT NULL DEFAULT 'Launch Preparation',
        created_at TEXT NOT NULL
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS teams (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        academy_id INTEGER NOT NULL DEFAULT 1,
        name TEXT NOT NULL,
        icon_json TEXT NOT NULL,
        level INTEGER NOT NULL DEFAULT 1,
        focus TEXT,
        blocker TEXT,
        created_at TEXT NOT NULL
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS partners (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        partner_type TEXT NOT NULL DEFAULT 'company',
        contact_name TEXT,
        contact_email TEXT,
        website TEXT,
        mission_focus TEXT,
        status TEXT NOT NULL DEFAULT 'pending',
        admin_fee_interest TEXT DEFAULT '',
        created_at TEXT NOT NULL,
        approved_at TEXT
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS partner_mission_drafts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        partner_id INTEGER,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        mission_type TEXT NOT NULL DEFAULT 'partner',
        difficulty TEXT NOT NULL DEFAULT 'foundation',
        estimated_sessions INTEGER NOT NULL DEFAULT 3,
        required_skills_json TEXT NOT NULL DEFAULT '[]',
        evidence_required TEXT,
        target_metric TEXT,
        status TEXT NOT NULL DEFAULT 'draft',
        published_mission_id INTEGER,
        created_at TEXT NOT NULL,
        reviewed_at TEXT
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS sponsor_campaigns (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        partner_id INTEGER,
        title TEXT NOT NULL,
        description TEXT,
        target_academies INTEGER DEFAULT 1,
        suggested_contribution TEXT,
        status TEXT NOT NULL DEFAULT 'concept',
        created_at TEXT NOT NULL
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS network_feed (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        academy_id INTEGER,
        message TEXT NOT NULL,
        event_type TEXT NOT NULL DEFAULT 'update',
        created_at TEXT NOT NULL
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS featured_innovations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        academy_id INTEGER NOT NULL,
        mission_id INTEGER,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        featured_by TEXT NOT NULL DEFAULT 'Spacecrafted Mission Control',
        created_at TEXT NOT NULL
    );
    """)

    def ensure_academy_column(column, definition):
        cols = [r["name"] for r in conn.execute("PRAGMA table_info(academies)").fetchall()]
        if column not in cols:
            conn.execute(f"ALTER TABLE academies ADD COLUMN {column} {definition}")

    ensure_academy_column("mission_statement", "TEXT DEFAULT ''")
    ensure_academy_column("public_profile", "TEXT DEFAULT ''")

    conn.execute("""
    CREATE TABLE IF NOT EXISTS network_missions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_by_academy_id INTEGER NOT NULL DEFAULT 1,
        title TEXT NOT NULL,
        mission_type TEXT NOT NULL DEFAULT 'challenge',
        description TEXT NOT NULL,
        difficulty TEXT NOT NULL DEFAULT 'foundation',
        estimated_sessions INTEGER NOT NULL DEFAULT 3,
        required_skills_json TEXT NOT NULL DEFAULT '[]',
        evidence_required TEXT,
        target_metric TEXT,
        status TEXT NOT NULL DEFAULT 'open',
        is_official INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS network_mission_acceptances (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mission_id INTEGER NOT NULL,
        academy_id INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT 'accepted',
        result_summary TEXT,
        evidence_url TEXT,
        score_value TEXT,
        created_at TEXT NOT NULL,
        completed_at TEXT
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS commendations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        academy_id INTEGER NOT NULL,
        mission_id INTEGER,
        title TEXT NOT NULL,
        description TEXT,
        issued_by TEXT NOT NULL DEFAULT 'Spacecrafted Mission Control',
        created_at TEXT NOT NULL
    );
    """)

    # Sprint 6 seed missions from Spacecrafted Mission Control.
    seed_missions = [
        ('Maximum Height', 'benchmark', 'Reach the greatest launch altitude possible. Compare launch altitude with other agencies while documenting design choices and measurement method.', 'foundation', 3, ['measurement_data','practical_building','numeracy'], 'Altitude evidence, method of measurement, rocket design notes, and reflection on improvements.', 'highest altitude', 1),
        ('Maximum Time Aloft', 'benchmark', 'Keep the rocket or recovery system airborne for as long as possible. This rewards recovery engineering, drag design and controlled descent.', 'foundation', 3, ['design','visualisation','measurement_data'], 'Time aloft evidence, recovery design, test notes and improvement reflection.', 'longest time aloft', 1),
        ('Accurate Height', 'benchmark', 'Hit a target altitude as closely as possible. This is about prediction, calibration and controlled engineering rather than maximum performance.', 'intermediate', 4, ['logical_thinking','numeracy','measurement_data','project_management'], 'Target altitude, achieved altitude, error from target, and explanation of calibration choices.', 'smallest error from target', 1),
        ('Precise Height', 'benchmark', 'Demonstrate repeatability by hitting a target altitude reliably over multiple launches. This rewards consistency, process control and operational discipline.', 'advanced', 5, ['measurement_data','attention_to_detail','systems_thinking','team_coordination'], 'Multiple launch measurements, spread/range, average altitude and process notes.', 'lowest spread over attempts', 1)
    ]
    for title, mission_type, desc, difficulty, sessions, skills, evidence, metric, official in seed_missions:
        existing = conn.execute("SELECT id FROM network_missions WHERE lower(title)=lower(?) AND is_official=1", (title,)).fetchone()
        if not existing:
            conn.execute("""INSERT INTO network_missions(created_by_academy_id, title, mission_type, description, difficulty, estimated_sessions, required_skills_json, evidence_required, target_metric, status, is_official, created_at)
                            VALUES(1, ?, ?, ?, ?, ?, ?, ?, ?, 'open', ?, ?)""",
                         (title, mission_type, desc, difficulty, sessions, json.dumps(skills), evidence, metric, official, now()))

    conn.execute("""
    CREATE TABLE IF NOT EXISTS mission_tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        academy_id INTEGER NOT NULL DEFAULT 1,
        team_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        start_session INTEGER NOT NULL DEFAULT 1,
        duration_sessions INTEGER NOT NULL DEFAULT 1,
        people_needed INTEGER NOT NULL DEFAULT 1,
        assigned_students_json TEXT NOT NULL DEFAULT '[]',
        required_skills_json TEXT NOT NULL DEFAULT '[]',
        depends_on_task_id INTEGER,
        status TEXT NOT NULL DEFAULT 'not_started',
        created_at TEXT NOT NULL,
        updated_at TEXT
    );
    """)

    conn.execute("""
    CREATE TABLE IF NOT EXISTS mission_feed (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        academy_id INTEGER NOT NULL DEFAULT 1,
        message TEXT NOT NULL,
        created_at TEXT NOT NULL
    );
    """)

    conn.execute("INSERT OR IGNORE INTO academies(id, name, club_code, mission_phase, created_at) VALUES(1, 'Cambridge Space Academy', 'launch', 'Launch Preparation', ?)", (now(),))

    default_teams = [
        ('Command & Control', 'Mission coordination, launch planning and team support'),
        ('Rocket Build', 'Rocket construction, testing and launch readiness'),
        ('Payload', 'Payload design, protection and integration'),
        ('Data & Measurement', 'Altitude, angles, measurements and mission evidence'),
        ('Communications', 'Briefings, reporting and mission updates'),
        ('Design & Mission Patch', 'Visual identity, mission patches and presentation')
    ]

    for idx, (team_name, focus) in enumerate(default_teams):
        existing = conn.execute("SELECT id FROM teams WHERE academy_id=1 AND lower(name)=lower(?)", (team_name,)).fetchone()
        if not existing:
            icon = json.dumps(["#ffd166" if i in [9+idx%6, 18+idx%6, 27+idx%6, 36+idx%6, 45+idx%6] else "" for i in range(64)])
            conn.execute("INSERT INTO teams(academy_id, name, icon_json, level, focus, blocker, created_at) VALUES(1, ?, ?, 1, ?, '', ?)", (team_name, icon, focus, now()))

    # Link existing students to default academy and matching/created team.
    students_missing = conn.execute("SELECT id, team FROM students WHERE team_id IS NULL").fetchall()
    for st in students_missing:
        team_name = st["team"] or "Unassigned"
        team_row = conn.execute("SELECT id FROM teams WHERE academy_id=1 AND lower(name)=lower(?)", (team_name,)).fetchone()
        if not team_row:
            icon = json.dumps(["#58d2ff" if i in [0,9,18,27,36,45,54,63] else "" for i in range(64)])
            conn.execute("INSERT INTO teams(academy_id, name, icon_json, level, focus, blocker, created_at) VALUES(1, ?, ?, 1, '', '', ?)", (team_name, icon, now()))
            team_id = conn.execute("SELECT last_insert_rowid() AS id").fetchone()["id"]
        else:
            team_id = team_row["id"]
        conn.execute("UPDATE students SET academy_id=1, team_id=? WHERE id=?", (team_id, st["id"]))


    conn.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('teacher_pin',?)", (os.environ.get("SPACE_ACADEMY_TEACHER_PIN","2468"),))
    conn.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('student_join_code',?)", (os.environ.get("SPACE_ACADEMY_JOIN_CODE","launch"),))
    conn.commit(); conn.close()

@app.before_request
def ensure_db():
    if not os.path.exists(DB_PATH): init_db()

def get_setting(k):
    conn=db(); row=conn.execute("SELECT value FROM settings WHERE key=?", (k,)).fetchone(); conn.close()
    return row["value"] if row else ""

def login_required(kind=None):
    def deco(fn):
        @wraps(fn)
        def wrapper(*a, **kw):
            if kind=="teacher" and not session.get("teacher"):
                flash("Please log in as teacher."); return redirect(url_for("teacher_login"))
            if kind=="student" and not session.get("student_id"):
                flash("Please log in."); return redirect(url_for("student_login"))
            if kind is None and not (session.get("teacher") or session.get("student_id")):
                flash("Please log in."); return redirect(url_for("index"))
            return fn(*a, **kw)
        return wrapper
    return deco

def current_student():
    sid=session.get("student_id")
    if not sid: return None
    conn=db(); row=conn.execute("SELECT * FROM students WHERE id=?", (sid,)).fetchone(); conn.close()
    return row

def badge_path(skill_key):
    info=SKILL_LOOKUP.get(skill_key)
    return f"/static/badges/{info['type']}/{skill_key}.svg" if info else ""


def current_academy_id():
    return int(session.get("academy_id") or 1)

def get_academy(academy_id=None):
    academy_id = academy_id or current_academy_id()
    conn = db()
    row = conn.execute("SELECT * FROM academies WHERE id=?", (academy_id,)).fetchone()
    conn.close()
    return row

def get_teams(academy_id=None):
    academy_id = academy_id or current_academy_id()
    conn = db()
    rows = conn.execute("SELECT * FROM teams WHERE academy_id=? ORDER BY name", (academy_id,)).fetchall()
    conn.close()
    return rows

def team_capability(academy_id=None):
    academy_id = academy_id or current_academy_id()
    conn = db()
    teams = conn.execute("SELECT * FROM teams WHERE academy_id=? ORDER BY name", (academy_id,)).fetchall()
    output = []
    for team in teams:
        members = conn.execute("SELECT * FROM students WHERE academy_id=? AND team_id=? ORDER BY first_name", (academy_id, team["id"])).fetchall()
        skill_counts = {}
        for m in members:
            rows = conn.execute("SELECT skill_key FROM student_skills WHERE student_id=? AND status='approved'", (m["id"],)).fetchall()
            for r in rows:
                skill_counts[r["skill_key"]] = skill_counts.get(r["skill_key"], 0) + 1
        output.append({"team": team, "members": members, "skill_counts": skill_counts})
    conn.close()
    return output

def feed(academy_id=None, limit=12):
    academy_id = academy_id or current_academy_id()
    conn = db()
    rows = conn.execute("SELECT * FROM mission_feed WHERE academy_id=? ORDER BY created_at DESC LIMIT ?", (academy_id, limit)).fetchall()
    conn.close()
    return rows

def add_feed(message, academy_id=1):
    conn = db()
    conn.execute("INSERT INTO mission_feed(academy_id, message, created_at) VALUES(?, ?, ?)", (academy_id, message[:240], now()))
    conn.commit()
    conn.close()


@app.context_processor
def inject():
    return dict(CORE_SKILLS=CORE_SKILLS, DEVELOPING_SKILLS=DEVELOPING_SKILLS, SKILL_LOOKUP=SKILL_LOOKUP, badge_path=badge_path, current_academy=get_academy)

@app.route("/")
def index(): return render_template("index.html")

@app.route("/student/register", methods=["GET","POST"])
def student_register():
    conn = db()
    academies = conn.execute("SELECT * FROM academies ORDER BY name").fetchall()
    teams = conn.execute("SELECT * FROM teams WHERE academy_id=1 ORDER BY name").fetchall()
    conn.close()

    if request.method=="POST":
        first_name=request.form.get("first_name","").strip()[:40]
        academy_code=request.form.get("join_code","").strip()
        team_id_raw=request.form.get("team_id","").strip()
        new_team=request.form.get("new_team","").strip()[:60]
        pin=request.form.get("pin","").strip()[:20]
        icon_json=request.form.get("icon_json","[]")

        conn=db()
        academy=conn.execute("SELECT * FROM academies WHERE lower(club_code)=lower(?)", (academy_code,)).fetchone()
        if not academy:
            conn.close()
            flash("Club code is not correct.")
            return redirect(url_for("student_register"))

        if not first_name or not pin:
            conn.close()
            flash("Please enter first name and PIN.")
            return redirect(url_for("student_register"))

        if new_team:
            existing = conn.execute("SELECT * FROM teams WHERE academy_id=? AND lower(name)=lower(?)", (academy["id"], new_team)).fetchone()
            if existing:
                team_id = existing["id"]
            else:
                blank_icon = json.dumps([""] * 64)
                conn.execute("INSERT INTO teams(academy_id, name, icon_json, level, focus, blocker, created_at) VALUES(?, ?, ?, 1, '', '', ?)", (academy["id"], new_team, blank_icon, now()))
                team_id = conn.execute("SELECT last_insert_rowid() AS id").fetchone()["id"]
        else:
            try:
                team_id = int(team_id_raw)
            except Exception:
                team_id = None

        team = conn.execute("SELECT * FROM teams WHERE id=? AND academy_id=?", (team_id, academy["id"])).fetchone()
        if not team:
            conn.close()
            flash("Please choose a valid team.")
            return redirect(url_for("student_register"))

        cur=conn.cursor()
        cur.execute("INSERT INTO students(first_name,team,team_id,academy_id,icon_json,pin,role,created_at) VALUES(?,?,?,?,?,?,?,?)",
                    (first_name, team["name"], team["id"], academy["id"], icon_json, pin, "student", now()))
        sid=cur.lastrowid

        for sk in request.form.getlist("core_skills"):
            if sk in SKILL_LOOKUP and SKILL_LOOKUP[sk]["type"]=="core":
                cur.execute("""INSERT OR IGNORE INTO student_skills(student_id,skill_key,skill_type,status,evidence,created_at,approved_at)
                               VALUES(?,?,?,?,?,?,?)""", (sid, sk, "core", "approved", "Selected as an existing core skill at profile creation.", now(), now()))

        conn.commit(); conn.close()
        session.clear(); session["student_id"]=sid; session["academy_id"]=academy["id"]
        add_feed(f"{first_name} joined {team['name']}.", academy["id"])
        flash("Crew profile created.")
        return redirect(url_for("dashboard"))

    return render_template("student_register.html", academies=academies, teams=teams)

@app.route("/student/login", methods=["GET","POST"])
def student_login():
    if request.method=="POST":
        first_name=request.form.get("first_name","").strip()
        pin=request.form.get("pin","").strip()
        conn=db(); row=conn.execute("SELECT * FROM students WHERE lower(first_name)=lower(?) AND pin=? ORDER BY id DESC LIMIT 1",(first_name,pin)).fetchone(); conn.close()
        if not row:
            flash("No matching profile found."); return redirect(url_for("student_login"))
        session.clear(); session["student_id"]=row["id"]; session["academy_id"]=row["academy_id"] or 1
        return redirect(url_for("dashboard"))
    return render_template("student_login.html")

@app.route("/teacher/login", methods=["GET","POST"])
def teacher_login():
    if request.method=="POST":
        if request.form.get("pin","").strip()==get_setting("teacher_pin"):
            session.clear(); session["teacher"]=True; session["academy_id"]=1; return redirect(url_for("academy_hq"))
        flash("Teacher PIN is not correct.")
    return render_template("teacher_login.html")

@app.route("/logout")
def logout():
    session.clear(); flash("Logged out."); return redirect(url_for("index"))


@app.route("/hq")
@login_required()
def academy_hq():
    academy_id = current_academy_id()
    academy = get_academy(academy_id)
    capability = team_capability(academy_id)
    mission_feed = feed(academy_id)
    return render_template("academy_hq.html", academy=academy, capability=capability, mission_feed=mission_feed)

@app.route("/teacher/team/<int:team_id>/update", methods=["POST"])
@login_required("teacher")
def update_team(team_id):
    academy_id = current_academy_id()
    name = request.form.get("name", "").strip()[:80]
    level_raw = request.form.get("level", "1")
    focus = request.form.get("focus", "").strip()[:400]
    blocker = request.form.get("blocker", "").strip()[:400]
    icon_json = request.form.get("team_icon_json", "").strip()

    try:
        level = int(level_raw)
    except Exception:
        level = 1
    level = max(1, min(5, level))

    conn = db()
    old = conn.execute("SELECT * FROM teams WHERE id=? AND academy_id=?", (team_id, academy_id)).fetchone()
    if not old:
        conn.close()
        flash("Team not found.")
        return redirect(url_for("teacher_dashboard"))

    if not name:
        name = old["name"]
    if not icon_json:
        icon_json = old["icon_json"]

    conn.execute("UPDATE teams SET name=?, level=?, focus=?, blocker=?, icon_json=? WHERE id=? AND academy_id=?",
                 (name, level, focus, blocker, icon_json, team_id, academy_id))
    conn.execute("UPDATE students SET team=? WHERE team_id=? AND academy_id=?", (name, team_id, academy_id))
    conn.commit()
    conn.close()

    if old["level"] != level:
        add_feed(f"{name} moved to Level {level}/5.", academy_id)
    flash("Team updated.")
    return redirect(url_for("teacher_dashboard"))

@app.route("/teacher/team/create", methods=["POST"])
@login_required("teacher")
def create_team():
    academy_id = current_academy_id()
    name = request.form.get("name", "").strip()[:80]
    focus = request.form.get("focus", "").strip()[:400]
    if not name:
        flash("Team name required.")
        return redirect(url_for("teacher_dashboard"))
    blank_icon = json.dumps([""] * 64)
    conn = db()
    conn.execute("INSERT INTO teams(academy_id, name, icon_json, level, focus, blocker, created_at) VALUES(?, ?, ?, 1, ?, '', ?)",
                 (academy_id, name, blank_icon, focus, now()))
    conn.commit()
    conn.close()
    add_feed(f"{name} team created.", academy_id)
    flash("Team created.")
    return redirect(url_for("teacher_dashboard"))


@app.route("/dashboard")
@login_required("student")
def dashboard():
    st=current_student(); conn=db()
    skills=conn.execute("SELECT * FROM student_skills WHERE student_id=? AND status='approved'",(st["id"],)).fetchall()
    apps=conn.execute("SELECT * FROM skill_applications WHERE student_id=? ORDER BY created_at DESC",(st["id"],)).fetchall()
    summary=conn.execute("SELECT * FROM ai_summaries WHERE student_id=? ORDER BY created_at DESC LIMIT 1",(st["id"],)).fetchone()
    conn.close()
    return render_template("dashboard.html", student=st, skills=skills, apps=apps, latest_summary=summary)

@app.route("/diary", methods=["GET","POST"])
@login_required("student")
def diary():
    st=current_student(); conn=db()
    if request.method=="POST":
        entry=request.form.get("entry","").strip()
        if entry:
            conn.execute("INSERT INTO mission_diary(student_id,entry,created_at) VALUES(?,?,?)",(st["id"],entry[:2000],now()))
            conn.commit(); flash("Mission diary saved.")
        conn.close(); return redirect(url_for("diary"))
    rows=conn.execute("SELECT * FROM mission_diary WHERE student_id=? ORDER BY created_at DESC",(st["id"],)).fetchall()
    conn.close(); return render_template("diary.html", student=st, rows=rows)

@app.route("/skills/apply", methods=["GET","POST"])
@login_required("student")
def apply_skill():
    st=current_student()
    if request.method=="POST":
        skill=request.form.get("skill_key")
        evidence=request.form.get("evidence","").strip()
        peer=request.form.get("peer_comment","").strip()
        if skill not in SKILL_LOOKUP or SKILL_LOOKUP[skill]["type"]!="developing":
            flash("Please choose a developing skill."); return redirect(url_for("apply_skill"))
        if not evidence:
            flash("Please add evidence."); return redirect(url_for("apply_skill"))
        conn=db()
        conn.execute("INSERT INTO skill_applications(student_id,skill_key,evidence,peer_comment,status,created_at) VALUES(?,?,?,?,?,?)",
                     (st["id"], skill, evidence[:2000], peer[:1000], "pending", now()))
        conn.commit(); conn.close()
        flash("Skill application submitted."); return redirect(url_for("dashboard"))
    return render_template("apply_skill.html", student=st)

@app.route("/crew/search")
@login_required()
def crew_search():
    selected=request.args.getlist("skills"); team=request.args.get("team","").strip()
    academy_id = current_academy_id()
    conn=db(); students=conn.execute("SELECT * FROM students WHERE academy_id=? ORDER BY team,first_name", (academy_id,)).fetchall()
    results=[]
    for st in students:
        skills=[r["skill_key"] for r in conn.execute("SELECT skill_key FROM student_skills WHERE student_id=? AND status='approved'",(st["id"],)).fetchall()]
        if selected and not all(s in skills for s in selected): continue
        if team and team.lower() not in st["team"].lower(): continue
        results.append(dict(student=st, skills=skills))
    conn.close()
    return render_template("crew_search.html", results=results, selected=selected, team=team)

@app.route("/jobs", methods=["GET","POST"])
@login_required()
def jobs():
    if request.method=="POST":
        title=request.form.get("title","").strip()
        team=request.form.get("team","").strip()
        desc=request.form.get("description","").strip()
        skills=request.form.getlist("required_skills")
        if title and team and desc:
            conn=db()
            conn.execute("INSERT INTO job_posts(title,team,description,required_skills_json,status,created_by,created_at,academy_id) VALUES(?,?,?,?,?,?,?,?)",
                         (title[:100],team[:60],desc[:1000],json.dumps(skills),"open",session.get("student_id"),now(),current_academy_id()))
            conn.commit(); conn.close(); flash("Mission role posted."); return redirect(url_for("jobs"))
        flash("Please complete the role title, team and description.")
    conn=db(); rows=conn.execute("SELECT * FROM job_posts WHERE academy_id=? ORDER BY created_at DESC", (current_academy_id(),)).fetchall(); conn.close()
    out=[]
    for r in rows:
        d=dict(r); d["skills"]=json.loads(d["required_skills_json"] or "[]"); out.append(d)
    return render_template("jobs.html", jobs=out)

@app.route("/jobs/<int:job_id>/apply", methods=["POST"])
@login_required("student")
def apply_job(job_id):
    conn=db()
    conn.execute("INSERT INTO job_applications(job_id,student_id,message,status,created_at) VALUES(?,?,?,?,?)",
                 (job_id,session["student_id"],request.form.get("message","").strip()[:1000],"interested",now()))
    conn.commit(); conn.close(); flash("Interest sent.")
    return redirect(url_for("jobs"))

@app.route("/generate-capabilities", methods=["POST"])
@login_required("student")
def generate_capabilities():
    st=current_student(); conn=db()
    skills=conn.execute("SELECT skill_key FROM student_skills WHERE student_id=? AND status='approved'",(st["id"],)).fetchall()
    diary=conn.execute("SELECT entry FROM mission_diary WHERE student_id=? ORDER BY created_at DESC LIMIT 5",(st["id"],)).fetchall()
    skill_names=[SKILL_LOOKUP[s["skill_key"]]["name"] for s in skills if s["skill_key"] in SKILL_LOOKUP]
    phrase=", ".join(skill_names[:8]) if skill_names else "teamwork, communication and mission contribution"
    summary=(f"During the Space Academy mission, {st['first_name']} contributed as part of the {st['team']} team. "
             f"Their recognised mission capabilities include {phrase}. "
             f"They have shown these capabilities through practical mission work, reflection, collaboration and problem solving. "
             f"This profile gives evidence that {st['first_name']} can contribute to a real project team and continue developing valuable skills.")
    if diary:
        summary += " Their mission diary shows reflection on their work and growing awareness of how their skills support the wider launch mission."
    conn.execute("INSERT INTO ai_summaries(student_id,summary,created_at) VALUES(?,?,?)",(st["id"],summary,now()))
    conn.commit(); conn.close(); flash("Mission capabilities generated.")
    return redirect(url_for("dashboard"))

@app.route("/teacher")
@login_required("teacher")
def teacher_dashboard():
    academy_id = current_academy_id()
    conn=db()
    students=conn.execute("SELECT * FROM students WHERE academy_id=? ORDER BY team,first_name", (academy_id,)).fetchall()
    pending=conn.execute("""SELECT a.*,s.first_name,s.team FROM skill_applications a JOIN students s ON s.id=a.student_id
                            WHERE a.status='pending' AND s.academy_id=? ORDER BY a.created_at ASC""", (academy_id,)).fetchall()
    jobs_count=conn.execute("SELECT COUNT(*) c FROM job_posts WHERE academy_id=?", (academy_id,)).fetchone()["c"]
    diary_count=conn.execute("SELECT COUNT(*) c FROM mission_diary WHERE academy_id=? OR academy_id IS NULL", (academy_id,)).fetchone()["c"]
    skill_count=conn.execute("""SELECT COUNT(*) c FROM student_skills ss JOIN students s ON s.id=ss.student_id
                                WHERE ss.status='approved' AND s.academy_id=?""", (academy_id,)).fetchone()["c"]
    conn.close()
    academy = get_academy(academy_id)
    capability = team_capability(academy_id)
    mission_feed = feed(academy_id, limit=8)
    return render_template("teacher_dashboard.html", students=students, pending=pending, jobs_count=jobs_count, diary_count=diary_count, skill_count=skill_count, academy=academy, capability=capability, mission_feed=mission_feed)

@app.route("/teacher/applications/<int:app_id>/<decision>", methods=["POST"])
@login_required("teacher")
def decide_application(app_id, decision):
    if decision not in ["approved","rejected"]: return redirect(url_for("teacher_dashboard"))
    comment=request.form.get("teacher_comment","").strip()
    conn=db(); a=conn.execute("SELECT * FROM skill_applications WHERE id=?",(app_id,)).fetchone()
    if not a:
        conn.close(); flash("Application not found."); return redirect(url_for("teacher_dashboard"))
    conn.execute("UPDATE skill_applications SET status=?,teacher_comment=?,decided_at=? WHERE id=?",(decision,comment[:1000],now(),app_id))
    if decision=="approved":
        conn.execute("""INSERT OR IGNORE INTO student_skills(student_id,skill_key,skill_type,status,evidence,peer_comment,teacher_comment,created_at,approved_at)
                        VALUES(?,?,?,?,?,?,?,?,?)""",(a["student_id"],a["skill_key"],"developing","approved",a["evidence"],a["peer_comment"],comment,now(),now()))
    conn.commit(); conn.close(); flash(f"Application {decision}.")
    return redirect(url_for("teacher_dashboard"))

@app.route("/teacher/student/<int:student_id>")
@login_required("teacher")
def teacher_student(student_id):
    conn=db()
    st=conn.execute("SELECT * FROM students WHERE id=?",(student_id,)).fetchone()
    skills=conn.execute("SELECT * FROM student_skills WHERE student_id=? AND status='approved'",(student_id,)).fetchall()
    diary=conn.execute("SELECT * FROM mission_diary WHERE student_id=? ORDER BY created_at DESC",(student_id,)).fetchall()
    summaries=conn.execute("SELECT * FROM ai_summaries WHERE student_id=? ORDER BY created_at DESC",(student_id,)).fetchall()
    conn.close()
    return render_template("teacher_student.html", student=st, skills=skills, diary=diary, summaries=summaries)

@app.route("/teacher/export.csv")
@login_required("teacher")
def export_csv():
    conn=db()
    rows=conn.execute("""SELECT s.first_name,s.team,ss.skill_key,ss.skill_type,ss.evidence,ss.approved_at
                         FROM student_skills ss JOIN students s ON s.id=ss.student_id
                         WHERE ss.status='approved' ORDER BY s.team,s.first_name,ss.skill_type,ss.skill_key""").fetchall()
    conn.close()
    def gen():
        yield "first_name,team,skill,skill_type,evidence,approved_at\n"
        for r in rows:
            skill=SKILL_LOOKUP.get(r["skill_key"],{}).get("name",r["skill_key"])
            vals=[r["first_name"],r["team"],skill,r["skill_type"],(r["evidence"] or "").replace("\n"," "),r["approved_at"] or ""]
            yield ",".join('"' + str(v).replace('"','""') + '"' for v in vals) + "\n"
    return Response(gen(), mimetype="text/csv", headers={"Content-Disposition":"attachment; filename=space_academy_skills_export.csv"})

@app.route("/teacher/impact")
@login_required("teacher")
def impact_report():
    conn=db()
    student_count=conn.execute("SELECT COUNT(*) c FROM students").fetchone()["c"]
    team_count=conn.execute("SELECT COUNT(DISTINCT team) c FROM students").fetchone()["c"]
    skill_rows=conn.execute("SELECT skill_key,COUNT(*) c FROM student_skills WHERE status='approved' GROUP BY skill_key ORDER BY c DESC").fetchall()
    diary_count=conn.execute("SELECT COUNT(*) c FROM mission_diary").fetchone()["c"]
    conn.close()
    return render_template("impact_report.html", student_count=student_count, team_count=team_count, skill_rows=skill_rows, diary_count=diary_count)

if __name__=="__main__":
    init_db()
    app.run(debug=True)