#!/bin/bash

# LifeCycle Database Export Script
# Exports primary observational data tables to JSON format

# Default values
DB_FILE="life.db"
OUTPUT_DIR="lifecycle_export"

# Parse command line arguments
usage() {
    echo "Usage: $0 [OPTIONS]"
    echo "Options:"
    echo "  -d, --database FILE    Path to the LifeCycle database file (default: life.db)"
    echo "  -o, --output DIR       Output directory for exported JSON files (default: lifecycle_export)"
    echo "  -h, --help             Display this help message"
    echo ""
    echo "This script exports LifeCycle database tables to JSON format for use with OwnTracks."
    exit 1
}

while [[ $# -gt 0 ]]; do
    case $1 in
        -d|--database)
            DB_FILE="$2"
            shift 2
            ;;
        -o|--output)
            OUTPUT_DIR="$2"
            shift 2
            ;;
        -h|--help)
            usage
            ;;
        *)
            echo "Unknown option: $1"
            usage
            ;;
    esac
done

# Check if database file exists
if [ ! -f "$DB_FILE" ]; then
    echo "Error: Database file '$DB_FILE' not found"
    echo "Please specify the correct path using -d/--database option"
    exit 1
fi

# Create output directory
mkdir -p "$OUTPUT_DIR"

echo "Exporting LifeCycle database to JSON..."
echo "Database: $DB_FILE"
echo "Output directory: $OUTPUT_DIR"
echo

# Function to export table to JSON
export_table() {
    local table_name=$1
    local output_file="$OUTPUT_DIR/${table_name}.json"
    
    echo "Exporting $table_name..."
    
    # Determine appropriate sort column based on table
    local sort_clause=""
    case "$table_name" in
        "LocationEvent"|"Visit"|"Activity"|"Motion"|"Transit")
            sort_clause="ORDER BY timestamp"
            ;;
        "LocationPosition"|"LocationName"|"ActivityType"|"ActivityCategory"|"TimeZone")
            sort_clause="ORDER BY id"
            ;;
        *)
            sort_clause="ORDER BY rowid"
            ;;
    esac
    
    sqlite3 "$DB_FILE" <<EOF
.mode json
.output $output_file
SELECT * FROM $table_name $sort_clause;
.output stdout
EOF
    
    if [ -f "$output_file" ]; then
        local count=$(jq -r '. | length' "$output_file" 2>/dev/null || wc -l < "$output_file" | tr -d ' ')
        echo "  → $count records exported to $output_file"
    else
        echo "  → Error: Failed to create $output_file"
    fi
}

# Export primary observational data tables
echo "=== PRIMARY LOCATION TRACKING DATA ==="
export_table "LocationEvent"
export_table "Visit" 
export_table "LocationPosition"
export_table "LocationName"

echo
echo "=== ADDITIONAL CORE DATA ==="
export_table "Activity"
export_table "Motion"
export_table "Transit"

echo
echo "=== REFERENCE DATA ==="
export_table "ActivityType"
export_table "ActivityCategory"
export_table "TimeZone"

echo
echo "Export completed!"
echo "Files created in: $OUTPUT_DIR/"
ls -la "$OUTPUT_DIR/"

# Create a summary file
echo "Creating export summary..."
cat > "$OUTPUT_DIR/export_summary.txt" <<EOF
LifeCycle Database Export Summary
Generated: $(date)
Source Database: $DB_FILE

Primary Location Data:
- LocationEvent: Raw GPS coordinates and sensor data
- Visit: Stationary periods at locations
- LocationPosition: Named locations with coordinates
- LocationName: Location identifiers and names

Core Activity Data:  
- Activity: Classified activities and behaviors
- Motion: Movement type detection (walk/run/drive/etc)
- Transit: Transportation between locations

Reference Data:
- ActivityType: Activity classifications (sleep, work, etc)
- ActivityCategory: Activity categories (transport, social, etc)
- TimeZone: Timezone reference data

Note: This export focuses on primary observational data.
Derived tables like DailyStats, LongTermStats, and Insights are excluded.
EOF

echo "Summary created: $OUTPUT_DIR/export_summary.txt"