server/onboarding.py

"""
Onboarding System - One-try limit, guided tour, and pricing cards
"""
import sqlite3
from datetime import datetime, timedelta
from pathlib import Path
import os
import json

OUTPUT_DIR = Path(os.environ.get('OUTPUT_DIR', './output'))
ONBOARDING_DB = OUTPUT_DIR / 'onboarding.db'

def init_onboarding_db():
    """Initialize onboarding tables"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    c = conn.cursor()
    
    # Track free trial usage
    c.execute('''CREATE TABLE IF NOT EXISTS trial_usage (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL UNIQUE,
        tries_used INTEGER DEFAULT 0,
        first_crawl_date TIMESTAMP,
        trial_expires TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )''')
    
    # Track onboarding progress
    c.execute('''CREATE TABLE IF NOT EXISTS onboarding_progress (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL UNIQUE,
        tour_completed BOOLEAN DEFAULT 0,
        tour_step INTEGER DEFAULT 0,
        pricing_shown BOOLEAN DEFAULT 0,
        last_step_date TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )''')
    
    conn.commit()
    conn.close()

def get_trial_status(user_id: int) -> dict:
    """Get user's trial status"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute('SELECT * FROM trial_usage WHERE user_id = ?', (user_id,))
    result = c.fetchone()
    conn.close()
    
    if result:
        row = dict(result)
        now = datetime.utcnow()
        trial_expires = datetime.fromisoformat(row['trial_expires']) if row['trial_expires'] else None
        
        return {
            'tries_used': row['tries_used'],
            'tries_remaining': max(0, 1 - row['tries_used']),
            'trial_expired': trial_expires and now > trial_expires if trial_expires else False,
            'trial_expires': row['trial_expires'],
            'first_crawl_date': row['first_crawl_date']
        }
    
    return {'tries_used': 0, 'tries_remaining': 1, 'trial_expired': False, 'trial_expires': None, 'first_crawl_date': None}

def init_trial(user_id: int, trial_days: int = 14) -> dict:
    """Initialize trial for new user"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    c = conn.cursor()
    
    now = datetime.utcnow()
    trial_expires = now + timedelta(days=trial_days)
    
    try:
        c.execute('''INSERT INTO trial_usage 
                     (user_id, tries_used, trial_expires)
                     VALUES (?, ?, ?)''',
                  (user_id, 0, trial_expires))
        conn.commit()
    except sqlite3.IntegrityError:
        pass
    
    conn.close()
    return get_trial_status(user_id)

def use_trial(user_id: int) -> dict:
    """Use one trial attempt"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    c = conn.cursor()
    
    now = datetime.utcnow()
    
    # Get current status
    c.execute('SELECT tries_used, first_crawl_date FROM trial_usage WHERE user_id = ?', (user_id,))
    result = c.fetchone()
    
    if not result:
        # Initialize if doesn't exist
        init_trial(user_id)
        c.execute('SELECT tries_used, first_crawl_date FROM trial_usage WHERE user_id = ?', (user_id,))
        result = c.fetchone()
    
    tries_used, first_crawl = result
    
    # Update tries and first crawl date
    if tries_used == 0:
        c.execute('''UPDATE trial_usage 
                     SET tries_used = 1, first_crawl_date = ?
                     WHERE user_id = ?''', (now, user_id))
    else:
        c.execute('''UPDATE trial_usage 
                     SET tries_used = tries_used + 1
                     WHERE user_id = ?''', (user_id,))
    
    conn.commit()
    conn.close()
    
    return get_trial_status(user_id)

def get_onboarding_progress(user_id: int) -> dict:
    """Get user's onboarding progress"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute('SELECT * FROM onboarding_progress WHERE user_id = ?', (user_id,))
    result = c.fetchone()
    conn.close()
    
    if result:
        return dict(result)
    
    return {
        'user_id': user_id,
        'tour_completed': False,
        'tour_step': 0,
        'pricing_shown': False,
        'last_step_date': None
    }

def init_onboarding(user_id: int) -> dict:
    """Initialize onboarding for new user"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    c = conn.cursor()
    
    try:
        c.execute('''INSERT INTO onboarding_progress 
                     (user_id, tour_step)
                     VALUES (?, ?)''', (user_id, 0))
        conn.commit()
    except sqlite3.IntegrityError:
        pass
    
    conn.close()
    return get_onboarding_progress(user_id)

def update_tour_step(user_id: int, step: int) -> dict:
    """Update tour step"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    c = conn.cursor()
    
    now = datetime.utcnow()
    c.execute('''UPDATE onboarding_progress 
                 SET tour_step = ?, last_step_date = ?
                 WHERE user_id = ?''', (step, now, user_id))
    conn.commit()
    conn.close()
    
    return get_onboarding_progress(user_id)

def complete_tour(user_id: int) -> dict:
    """Mark tour as completed"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    c = conn.cursor()
    
    now = datetime.utcnow()
    c.execute('''UPDATE onboarding_progress 
                 SET tour_completed = 1, last_step_date = ?
                 WHERE user_id = ?''', (now, user_id))
    conn.commit()
    conn.close()
    
    return get_onboarding_progress(user_id)

def mark_pricing_shown(user_id: int) -> dict:
    """Mark pricing cards as shown"""
    conn = sqlite3.connect(str(ONBOARDING_DB))
    c = conn.cursor()
    
    now = datetime.utcnow()
    c.execute('''UPDATE onboarding_progress 
                 SET pricing_shown = 1, last_step_date = ?
                 WHERE user_id = ?''', (now, user_id))
    conn.commit()
    conn.close()
    
    return get_onboarding_progress(user_id)

# Initialize on import
try:
    init_onboarding_db()
except Exception as e:
    print(f"  Onboarding DB init failed: {e}")