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") SPACECRAFTED_TEAMS = [ "Launch Services", "Mission Instrumentation", "Satellite Services", "Mission Communications", "Command & Control", ] STUDENT_RANKS = [ "Cadet", "Flight Engineer", "Mission Engineer", "Commander", "Agency Director", ] TEAM_ATMOSPHERIC_LEVELS = [ "Troposphere", "Stratosphere", "Mesosphere", "Thermosphere", "Exosphere", ] TEAM_INFO = { "Launch Services": { "icon": "πŸš€", "purpose": "Rocket design, launch systems and optimisation.", "needed_skills": ["practical_building", "using_tools", "measurement_data", "problem_solving", "decision_making"], "current_project": "Build and improve the safest, most reliable 2L water rocket launch system." }, "Mission Instrumentation": { "icon": "πŸ›°", "purpose": "Measurement, payloads, sensors and research.", "needed_skills": ["measurement_data", "numeracy", "logical_thinking", "attention_to_detail", "communication"], "current_project": "Measure launch performance and turn flight evidence into useful mission data." }, "Satellite Services": { "icon": "πŸ“Š", "purpose": "Recovery systems, aerodynamics and testing.", "needed_skills": ["design", "visualisation", "measurement_data", "practical_building", "resilience"], "current_project": "Design recovery systems and aerodynamic tests to protect the mission payload." }, "Mission Communications": { "icon": "🎨", "purpose": "Mission patches, storytelling, media, websites and presentations.", "needed_skills": ["creativity", "design", "communication", "professional_communication", "briefing_reporting"], "current_project": "Tell the story of the agency and turn progress into a visible mission identity." }, "Command & Control": { "icon": "🎯", "purpose": "Planning, safety, staffing, scheduling and strategy.", "needed_skills": ["organisation", "time_management", "project_management", "mission_monitoring", "decision_under_pressure"], "current_project": "Coordinate readiness, staffing, safety and the countdown to launch day." }, } MISSION_SEEDS = [ { "mission_key": "astro-pi-mission-zero", "title": "Astro Pi Mission Zero", "provider": "ESA / Raspberry Pi Foundation", "summary": "Write a simple program that can run on the International Space Station.", "description": "A real external mission opportunity. Students create a short Python program for Astro Pi Mission Zero, connecting their Spacecrafted avatar and identity work to genuine space computing.", "eligible_level": "Troposphere", "teams": ["Launch Services", "Mission Instrumentation", "Satellite Services", "Mission Communications", "Command & Control"], }, { "mission_key": "water-rocket-baseline", "title": "Water Rocket Baseline Flight", "provider": "Spacecrafted Internal Mission", "summary": "Build the first safe baseline rocket and collect launch evidence.", "description": "Launch Services leads the baseline rocket build. Other teams contribute measurement, recovery, communications and safety planning so the whole agency learns from the first launch.", "eligible_level": "Troposphere", "teams": ["Launch Services", "Mission Instrumentation", "Command & Control"], }, { "mission_key": "nose-cone-test", "title": "Nose Cone Design Investigation", "provider": "Spacecrafted Internal Mission", "summary": "Compare nose cone shapes before risking them on a real launch.", "description": "Students design fair tests for cone geometry, drop testing, timing and evidence collection. The best design earns a place on a future launch vehicle.", "eligible_level": "Stratosphere", "teams": ["Launch Services", "Mission Instrumentation", "Satellite Services"], }, { "mission_key": "mission-patch-v1", "title": "Agency Mission Patch v1", "provider": "Spacecrafted Internal Mission", "summary": "Turn team progress into the first Spacecrafted mission patch.", "description": "Mission Communications uses the agency star/radar chart and team stories to create a shared visual identity that represents everyone contributing to the mission.", "eligible_level": "Troposphere", "teams": ["Mission Communications", "Command & Control"], }, ] 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 ); CREATE TABLE IF NOT EXISTS team_settings ( team TEXT PRIMARY KEY, atmospheric_level TEXT NOT NULL DEFAULT 'Troposphere', current_project_key TEXT, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS mission_opportunities ( id INTEGER PRIMARY KEY AUTOINCREMENT, mission_key TEXT NOT NULL UNIQUE, title TEXT NOT NULL, provider TEXT, summary TEXT NOT NULL, description TEXT NOT NULL, eligible_level TEXT NOT NULL DEFAULT 'Troposphere', suggested_teams_json TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'available', created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS mission_votes ( id INTEGER PRIMARY KEY AUTOINCREMENT, mission_id INTEGER NOT NULL, student_id INTEGER NOT NULL, vote TEXT NOT NULL, created_at TEXT NOT NULL, UNIQUE(mission_id, student_id) ); """) # Lightweight v1 profile migration. This extends the existing students table # rather than creating a second profile table, so old student records remain usable. existing_cols = {row[1] for row in conn.execute("PRAGMA table_info(students)").fetchall()} profile_columns = { "preferred_name": "TEXT", "rank": "TEXT NOT NULL DEFAULT 'Cadet'", "avatar_url": "TEXT", "bio": "TEXT", "atmospheric_level": "TEXT NOT NULL DEFAULT 'Troposphere'", } for col_name, col_type in profile_columns.items(): if col_name not in existing_cols: conn.execute(f"ALTER TABLE students ADD COLUMN {col_name} {col_type}") # Seed team maturity rows and mission opportunities. These are data rows, not page hardcoding. for team in SPACECRAFTED_TEAMS: conn.execute( "INSERT OR IGNORE INTO team_settings(team, atmospheric_level, current_project_key, updated_at) VALUES(?,?,?,?)", (team, "Troposphere", None, now()) ) for mission in MISSION_SEEDS: conn.execute(""" INSERT OR IGNORE INTO mission_opportunities (mission_key, title, provider, summary, description, eligible_level, suggested_teams_json, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, 'available', ?) """, ( mission["mission_key"], mission["title"], mission["provider"], mission["summary"], mission["description"], mission["eligible_level"], json.dumps(mission["teams"]), now() )) 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(): # Run lightweight migrations on every request so existing deployed databases # gain new v1 columns without deleting or rebuilding any data. 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 level_index(level): try: return TEAM_ATMOSPHERIC_LEVELS.index(level) except ValueError: return 0 def mission_is_available_for_team(mission_row, team_name): try: teams = json.loads(mission_row["suggested_teams_json"] or "[]") except Exception: teams = [] return team_name in teams or "All" in teams def badge_path(skill_key): info=SKILL_LOOKUP.get(skill_key) return f"/static/badges/{info['type']}/{skill_key}.svg" if info else "" @app.context_processor def inject(): return dict( CORE_SKILLS=CORE_SKILLS, DEVELOPING_SKILLS=DEVELOPING_SKILLS, SKILL_LOOKUP=SKILL_LOOKUP, badge_path=badge_path, SPACECRAFTED_TEAMS=SPACECRAFTED_TEAMS, STUDENT_RANKS=STUDENT_RANKS, TEAM_ATMOSPHERIC_LEVELS=TEAM_ATMOSPHERIC_LEVELS, body_class="logged-in" if (session.get("student_id") or session.get("teacher")) else "public-page", ) @app.route("/") def index(): return render_template("index.html") @app.route("/academy") @app.route("/academy-hq") def academy_hq(): return redirect(url_for("index")) @app.route("/network") @app.route("/agency/network") def agency_network(): return redirect(url_for("crew_search")) @app.route("/student/register", methods=["GET","POST"]) def student_register(): if request.method=="POST": first_name=request.form.get("first_name","").strip()[:40] team=request.form.get("team","").strip()[:60] pin=request.form.get("pin","").strip()[:20] join_code=request.form.get("join_code","").strip() icon_json=request.form.get("icon_json","[]") if join_code != get_setting("student_join_code"): flash("Join code is not correct."); return redirect(url_for("student_register")) if not first_name or not team or not pin: flash("Please enter first name, team and PIN."); return redirect(url_for("student_register")) conn=db(); cur=conn.cursor() cur.execute("""INSERT INTO students(first_name,team,icon_json,pin,role,created_at,preferred_name,rank,atmospheric_level) VALUES(?,?,?,?,?,?,?,?,?)""", (first_name, team, icon_json, pin, "student", now(), first_name, "Cadet", "Troposphere")) 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 flash("Crew profile created."); return redirect(url_for("dashboard")) return render_template("student_register.html") @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"] 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; return redirect(url_for("teacher_dashboard")) 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("/students") @login_required("teacher") def students_list(): conn = db() students = conn.execute(""" SELECT * FROM students ORDER BY team, first_name """).fetchall() conn.close() return render_template("students_list.html", students=students) @app.route("/students/new", methods=["GET", "POST"]) @login_required("teacher") def create_student_profile(): if request.method == "POST": first_name = request.form.get("first_name", "").strip()[:40] preferred_name = request.form.get("preferred_name", "").strip()[:40] team = request.form.get("team", "").strip()[:60] rank = request.form.get("rank", "Cadet").strip() atmospheric_level = request.form.get("atmospheric_level", "Troposphere").strip() avatar_url = request.form.get("avatar_url", "").strip()[:500] bio = request.form.get("bio", "").strip()[:1000] pin = request.form.get("pin", "").strip()[:20] if not first_name or not team or not pin: flash("Please enter first name, team and PIN.") return redirect(url_for("create_student_profile")) if team not in SPACECRAFTED_TEAMS: flash("Please choose a Spacecrafted team.") return redirect(url_for("create_student_profile")) if rank not in STUDENT_RANKS: rank = "Cadet" if atmospheric_level not in TEAM_ATMOSPHERIC_LEVELS: atmospheric_level = "Troposphere" conn = db() cur = conn.cursor() cur.execute(""" INSERT INTO students(first_name, team, icon_json, pin, role, created_at, preferred_name, rank, avatar_url, bio, atmospheric_level) VALUES(?,?,?,?,?,?,?,?,?,?,?) """, (first_name, team, "[]", pin, "student", now(), preferred_name or first_name, rank, avatar_url, bio, atmospheric_level)) conn.commit() conn.close() flash("Student profile created.") return redirect(url_for("students_list")) return render_template("student_profile_form.html", student=None) @app.route("/students//edit", methods=["GET", "POST"]) @login_required("teacher") def edit_student_profile(student_id): conn = db() student = conn.execute("SELECT * FROM students WHERE id=?", (student_id,)).fetchone() if not student: conn.close() flash("Student not found.") return redirect(url_for("students_list")) if request.method == "POST": first_name = request.form.get("first_name", "").strip()[:40] preferred_name = request.form.get("preferred_name", "").strip()[:40] team = request.form.get("team", "").strip()[:60] rank = request.form.get("rank", "Cadet").strip() atmospheric_level = request.form.get("atmospheric_level", "Troposphere").strip() avatar_url = request.form.get("avatar_url", "").strip()[:500] bio = request.form.get("bio", "").strip()[:1000] pin = request.form.get("pin", "").strip()[:20] if not first_name or not team: flash("Please enter first name and team.") conn.close() return redirect(url_for("edit_student_profile", student_id=student_id)) if team not in SPACECRAFTED_TEAMS: flash("Please choose a Spacecrafted team.") conn.close() return redirect(url_for("edit_student_profile", student_id=student_id)) if rank not in STUDENT_RANKS: rank = "Cadet" if atmospheric_level not in TEAM_ATMOSPHERIC_LEVELS: atmospheric_level = "Troposphere" if pin: conn.execute(""" UPDATE students SET first_name=?, preferred_name=?, team=?, rank=?, atmospheric_level=?, avatar_url=?, bio=?, pin=? WHERE id=? """, (first_name, preferred_name or first_name, team, rank, atmospheric_level, avatar_url, bio, pin, student_id)) else: conn.execute(""" UPDATE students SET first_name=?, preferred_name=?, team=?, rank=?, atmospheric_level=?, avatar_url=?, bio=? WHERE id=? """, (first_name, preferred_name or first_name, team, rank, atmospheric_level, avatar_url, bio, student_id)) conn.commit() conn.close() flash("Student profile updated.") return redirect(url_for("students_list")) conn.close() return render_template("student_profile_form.html", student=student) def can_edit_student_avatar(student_id): """Teachers can edit any avatar; students can edit their own avatar.""" return bool(session.get("teacher") or session.get("student_id") == student_id) @app.route("/students//avatar", methods=["GET", "POST"]) def student_avatar_designer(student_id): if not can_edit_student_avatar(student_id): flash("Please log in to edit this avatar.") return redirect(url_for("index")) conn = db() student = conn.execute("SELECT * FROM students WHERE id=?", (student_id,)).fetchone() if not student: conn.close() flash("Student not found.") return redirect(url_for("students_list") if session.get("teacher") else url_for("dashboard")) if request.method == "POST": icon_json = request.form.get("icon_json", "[]") try: cells = json.loads(icon_json) if not isinstance(cells, list) or len(cells) != 64: raise ValueError("Avatar must contain exactly 64 cells.") cleaned = [] for cell in cells: if isinstance(cell, str) and (cell.startswith("#") or cell == "") and len(cell) <= 9: cleaned.append(cell) else: cleaned.append("") icon_json = json.dumps(cleaned) except Exception: flash("Avatar data was not valid. Please try again.") conn.close() return redirect(url_for("student_avatar_designer", student_id=student_id)) conn.execute("UPDATE students SET icon_json=?, avatar_url='' WHERE id=?", (icon_json, student_id)) conn.commit() conn.close() flash("Avatar saved.") if request.form.get("popup") == "1": return render_template("avatar_saved_popup.html", student_id=student_id) return redirect(url_for("edit_student_profile", student_id=student_id) if session.get("teacher") else url_for("dashboard")) conn.close() return render_template("avatar_designer.html", student=student) @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() conn=db(); students=conn.execute("SELECT * FROM students ORDER BY team,first_name").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("/team/") @login_required() def team_landing(team_name): team_name = team_name.strip() if team_name not in SPACECRAFTED_TEAMS: flash("Team not found.") return redirect(url_for("dashboard") if session.get("student_id") else url_for("teacher_dashboard")) conn = db() team_row = conn.execute("SELECT * FROM team_settings WHERE team=?", (team_name,)).fetchone() roster = conn.execute("SELECT * FROM students WHERE team=? ORDER BY first_name", (team_name,)).fetchall() skill_rows = conn.execute(""" SELECT ss.skill_key, COUNT(*) AS c FROM student_skills ss JOIN students s ON s.id = ss.student_id WHERE s.team=? AND ss.status='approved' GROUP BY ss.skill_key ORDER BY c DESC, ss.skill_key """, (team_name,)).fetchall() all_missions = conn.execute("SELECT * FROM mission_opportunities WHERE status='available' ORDER BY id ASC").fetchall() vote_rows = conn.execute(""" SELECT mission_id, vote, COUNT(*) AS c FROM mission_votes GROUP BY mission_id, vote """).fetchall() conn.close() vote_map = {} for row in vote_rows: vote_map.setdefault(row["mission_id"], {})[row["vote"]] = row["c"] info = TEAM_INFO.get(team_name, {"icon": "⭐", "purpose": "Student agency team.", "needed_skills": [], "current_project": "Build the agency."}) available_skill_keys = {r["skill_key"] for r in skill_rows} needed = [] for key in info["needed_skills"]: sk = SKILL_LOOKUP.get(key) if sk: needed.append({"key": key, "name": sk["name"], "available": key in available_skill_keys}) missions = [] current_level = team_row["atmospheric_level"] if team_row else "Troposphere" for m in all_missions: if mission_is_available_for_team(m, team_name): d = dict(m) d["votes"] = vote_map.get(m["id"], {}) d["locked"] = level_index(current_level) < level_index(m["eligible_level"]) missions.append(d) return render_template( "team_landing.html", team_name=team_name, team_info=info, team_level=current_level, roster=roster, skill_rows=skill_rows, needed_skills=needed, missions=missions, level_index=level_index, ) @app.route("/missions") @login_required() def missions_list(): conn = db() rows = conn.execute("SELECT * FROM mission_opportunities WHERE status='available' ORDER BY eligible_level, id").fetchall() votes = conn.execute("SELECT mission_id, vote, COUNT(*) AS c FROM mission_votes GROUP BY mission_id, vote").fetchall() conn.close() vote_map = {} for row in votes: vote_map.setdefault(row["mission_id"], {})[row["vote"]] = row["c"] missions = [] for row in rows: d = dict(row) d["teams"] = json.loads(row["suggested_teams_json"] or "[]") d["votes"] = vote_map.get(row["id"], {}) missions.append(d) return render_template("missions_list.html", missions=missions) @app.route("/missions/", methods=["GET", "POST"]) @login_required() def mission_detail(mission_id): conn = db() mission = conn.execute("SELECT * FROM mission_opportunities WHERE id=?", (mission_id,)).fetchone() if not mission: conn.close() flash("Mission not found.") return redirect(url_for("missions_list")) if request.method == "POST": if not session.get("student_id"): conn.close() flash("Only students can vote on missions.") return redirect(url_for("mission_detail", mission_id=mission_id)) vote = request.form.get("vote", "interested") if vote not in ["interested", "priority", "not_now"]: vote = "interested" conn.execute(""" INSERT INTO mission_votes(mission_id, student_id, vote, created_at) VALUES (?, ?, ?, ?) ON CONFLICT(mission_id, student_id) DO UPDATE SET vote=excluded.vote, created_at=excluded.created_at """, (mission_id, session["student_id"], vote, now())) conn.commit() flash("Mission vote saved.") votes = conn.execute("SELECT vote, COUNT(*) AS c FROM mission_votes WHERE mission_id=? GROUP BY vote", (mission_id,)).fetchall() my_vote = None if session.get("student_id"): row = conn.execute("SELECT vote FROM mission_votes WHERE mission_id=? AND student_id=?", (mission_id, session["student_id"])).fetchone() my_vote = row["vote"] if row else None conn.close() mission_dict = dict(mission) mission_dict["teams"] = json.loads(mission["suggested_teams_json"] or "[]") vote_counts = {row["vote"]: row["c"] for row in votes} return render_template("mission_detail.html", mission=mission_dict, vote_counts=vote_counts, my_vote=my_vote) @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) VALUES(?,?,?,?,?,?,?)", (title[:100],team[:60],desc[:1000],json.dumps(skills),"open",session.get("student_id"),now())) 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 ORDER BY created_at DESC").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//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(): conn=db() students=conn.execute("SELECT * FROM students ORDER BY team,first_name").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' ORDER BY a.created_at ASC""").fetchall() jobs_count=conn.execute("SELECT COUNT(*) c FROM job_posts").fetchone()["c"] diary_count=conn.execute("SELECT COUNT(*) c FROM mission_diary").fetchone()["c"] skill_count=conn.execute("SELECT COUNT(*) c FROM student_skills WHERE status='approved'").fetchone()["c"] conn.close() return render_template("teacher_dashboard.html", students=students, pending=pending, jobs_count=jobs_count, diary_count=diary_count, skill_count=skill_count) @app.route("/teacher/applications//", 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/") @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)