Blame for scripts/extract_journey_data.py

#!/usr/bin/env python3
"""
Extract journey tracking data from the database.
Usage: python3 extract_journey_data.py [query_name]
"""

import sqlite3
import json
import pandas as pd
from pathlib import Path

# Database path
DB_PATH = Path(__file__).parent.parent / "backend" / "app" / "db" / "local.db"


def get_connection():
    """Connect to SQLite database"""
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    return conn


def extract_stand_visits_detailed():
    """Extract all stand visits with dwell time"""
    query = """
    SELECT
      sp.full_name,
      st.stand_code,
      st.exhibitor_name,
      sv.visit_start,
      ROUND(sv.dwell_time_seconds / 60.0, 1) as dwell_minutes,
      sv.conversation_happened,
      sv.materials_photographed,
      sv.primary_vs_secondary
    FROM stand_visits sv
    LEFT JOIN student_profiles sp ON sv.student_id = sp.student_id
    LEFT JOIN stands st ON sv.stand_id = st.id
    ORDER BY sv.visit_start;
    """
    conn = get_connection()
    df = pd.read_sql_query(query, conn)
    conn.close()
    return df


def extract_high_intent_leads():
    """Extract Léa & Thomas type leads (high intent signals)"""
    query = """
    SELECT
      sp.full_name,
      sp.email,
      COUNT(DISTINCT sv.stand_id) as stands_visited,
      ROUND(SUM(sv.dwell_time_seconds) / 60.0, 1) as total_dwell_minutes,
      SUM(CASE WHEN sv.conversation_happened = 1 THEN 1 ELSE 0 END) as conversations,
      SUM(CASE WHEN sv.materials_photographed = 1 THEN 1 ELSE 0 END) as materials_photos,
      COUNT(DISTINCT dcs.id) as direct_emails_sent,
      COALESCE(pt.fair_influenced_decision, 0) as parcoursup_influenced,
      COALESCE(js.journey_completeness_score, 0) as journey_score
    FROM student_profiles sp
    LEFT JOIN stand_visits sv ON sp.student_id = sv.student_id
    LEFT JOIN direct_contact_signals dcs ON sp.student_id = dcs.student_id
    LEFT JOIN parcoursup_tracking pt ON sp.student_id = pt.student_id
    LEFT JOIN journey_scores js ON sp.student_id = js.student_id
    WHERE sv.stand_id IS NOT NULL
    GROUP BY sp.student_id
    HAVING
      SUM(sv.dwell_time_seconds) > 600  -- 10+ minutes
      OR SUM(CASE WHEN sv.conversation_happened = 1 THEN 1 ELSE 0 END) >= 1
      OR COUNT(DISTINCT dcs.id) > 0
    ORDER BY journey_score DESC;
    """
    conn = get_connection()
    df = pd.read_sql_query(query, conn)
    conn.close()
    return df


def extract_discovery_sources():
    """Breakdown of how students found the fair"""
    query = """
    SELECT
      source_channel,
      COUNT(*) as count,
      AVG(registration_time_seconds) as avg_reg_time_sec,
      GROUP_CONCAT(DISTINCT sp.full_name, ', ') as students
    FROM discovery_sources ds
    LEFT JOIN student_profiles sp ON ds.student_id = sp.student_id
    GROUP BY source_channel
    ORDER BY count DESC;
    """
    conn = get_connection()
    df = pd.read_sql_query(query, conn)
    conn.close()
    return df


def extract_engagement_timeline(student_id):
    """Get complete engagement timeline for a student"""
    query = """
    SELECT
      'Pre-Fair App Opens' as phase,
      pfe.app_opens_before_fair as value,
      pfe.created_at as timestamp
    FROM pre_fair_engagement pfe
    WHERE pfe.student_id = ?

    UNION ALL

    SELECT
      'Stand Visit: ' || st.exhibitor_name,
      ROUND(sv.dwell_time_seconds / 60.0, 1),
      sv.visit_start
    FROM stand_visits sv
    LEFT JOIN stands st ON sv.stand_id = st.id
    WHERE sv.student_id = ?

    UNION ALL

    SELECT
      'Email to Exhibitor',
      1,
      dcs.contact_time
    FROM direct_contact_signals dcs
    WHERE dcs.student_id = ? AND dcs.contact_type = 'email_to_exhibitor'

    ORDER BY timestamp;
    """
    conn = get_connection()
    df = pd.read_sql_query(query, conn, params=[student_id, student_id, student_id])
    conn.close()
    return df


def extract_parcoursup_changes():
    """Show students who changed Parcoursup choices post-fair"""
    query = """
    SELECT
      sp.full_name,
      sp.email,
      pt.parcoursup_programmes_before_fair,
      pt.parcoursup_programmes_after_fair,
      (pt.parcoursup_programmes_after_fair - pt.parcoursup_programmes_before_fair) as net_change,
      pt.programmes_added,
      pt.programmes_removed,
      pt.confidence_change,
      pt.updated_at
    FROM parcoursup_tracking pt
    LEFT JOIN student_profiles sp ON pt.student_id = sp.student_id
    WHERE pt.programmes_added IS NOT NULL OR pt.programmes_removed IS NOT NULL
    ORDER BY pt.updated_at DESC;
    """
    conn = get_connection()
    df = pd.read_sql_query(query, conn)
    conn.close()
    return df


def extract_churn_risk():
    """Identify students at risk of churning"""
    query = """
    SELECT
      sp.full_name,
      sp.email,
      ch.last_engagement_date,
      ch.days_to_first_churn,
      ch.reason_inferred,
      ch.re_engagement_attempts,
      ch.re_engaged
    FROM churn_signals ch
    LEFT JOIN student_profiles sp ON ch.student_id = sp.student_id
    ORDER BY ch.last_engagement_date DESC;
    """
    conn = get_connection()
    df = pd.read_sql_query(query, conn)
    conn.close()
    return df


def export_to_formats(df, name):
    """Export dataframe to multiple formats"""
    csv_path = f"journey_data_{name}.csv"
    json_path = f"journey_data_{name}.json"

    df.to_csv(csv_path, index=False)
    df.to_json(json_path, orient='records', indent=2)

    print(f"✓ Exported to {csv_path}")
    print(f"✓ Exported to {json_path}")
    return csv_path, json_path


def main():
    print("=" * 70)
    print("JOURNEY DATA EXTRACTION")
    print("=" * 70)

    # 1. High-intent leads (like Léa)
    print("\n📊 HIGH-INTENT LEADS (Léa & Thomas profiles)...")
    df_high_intent = extract_high_intent_leads()
    print(df_high_intent.to_string())
    export_to_formats(df_high_intent, "high_intent_leads")

    # 2. Stand visits with dwell time
    print("\n\n📊 STAND VISITS WITH DWELL TIME...")
    df_stands = extract_stand_visits_detailed()
    print(df_stands.head(20).to_string())
    export_to_formats(df_stands, "stand_visits")

    # 3. Discovery sources
    print("\n\n📊 HOW STUDENTS FOUND THE FAIR...")
    df_discovery = extract_discovery_sources()
    print(df_discovery.to_string())
    export_to_formats(df_discovery, "discovery_sources")

    # 4. Parcoursup changes
    print("\n\n📊 PARCOURSUP DECISIONS INFLUENCED BY FAIR...")
    df_parco = extract_parcoursup_changes()
    print(df_parco.to_string())
    export_to_formats(df_parco, "parcoursup_changes")

    # 5. Churn risk
    print("\n\n📊 STUDENTS AT CHURN RISK...")
    df_churn = extract_churn_risk()
    print(df_churn.to_string())
    export_to_formats(df_churn, "churn_risk")

    print("\n" + "=" * 70)
    print("✓ All data exported to journey_data_*.csv and journey_data_*.json")
    print("=" * 70)


if __name__ == "__main__":
    main()