Fpl Copilot

-- FPL Copilot SQLite Schema
-- All FPL data + user squad management in a single file database.

-- ============================================
-- FPL Data Tables
-- ============================================

CREATE TABLE IF NOT EXISTS teams (
  id                    INTEGER PRIMARY KEY,  -- FPL team ID (1-20)
  name                  TEXT    NOT NULL,      -- e.g., "Arsenal"
  short_name            TEXT    NOT NULL,      -- e.g., "ARS"
  code                  INTEGER NOT NULL,      -- FPL team code
  strength              INTEGER NOT NULL,      -- Overall strength rating
  strength_attack_home  INTEGER NOT NULL,
  strength_attack_away  INTEGER NOT NULL,
  strength_defence_home INTEGER NOT NULL,
  strength_defence_away INTEGER NOT NULL,
  updated_at            TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

CREATE TABLE IF NOT EXISTS gameweeks (
  id             INTEGER PRIMARY KEY,  -- GW number (1-38)
  name           TEXT    NOT NULL,      -- e.g., "Gameweek 1"
  deadline_time  TEXT    NOT NULL,      -- ISO timestamp
  finished       INTEGER NOT NULL DEFAULT 0,
  is_current     INTEGER NOT NULL DEFAULT 0,
  is_next        INTEGER NOT NULL DEFAULT 0,
  average_score  INTEGER,
  highest_score  INTEGER,
  updated_at     TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

CREATE TABLE IF NOT EXISTS players (
  id                          INTEGER PRIMARY KEY,  -- FPL element ID
  fpl_code                    INTEGER NOT NULL,      -- Stable across seasons
  first_name                  TEXT    NOT NULL,
  last_name                   TEXT    NOT NULL,
  web_name                    TEXT    NOT NULL,       -- Display name (e.g., "Salah")
  position                    TEXT    NOT NULL,       -- GKP, DEF, MID, FWD
  team_id                     INTEGER NOT NULL REFERENCES teams(id),
  now_cost                    INTEGER NOT NULL,       -- Price in 0.1m units (130 = £13.0m)
  total_points                INTEGER NOT NULL DEFAULT 0,
  points_per_game             REAL    NOT NULL DEFAULT 0,
  form                        REAL    NOT NULL DEFAULT 0,
  selected_by_percent         REAL    NOT NULL DEFAULT 0,
  minutes                     INTEGER NOT NULL DEFAULT 0,
  goals                       INTEGER NOT NULL DEFAULT 0,
  assists                     INTEGER NOT NULL DEFAULT 0,
  clean_sheets                INTEGER NOT NULL DEFAULT 0,
  goals_conceded              INTEGER NOT NULL DEFAULT 0,
  own_goals                   INTEGER NOT NULL DEFAULT 0,
  penalties_saved             INTEGER NOT NULL DEFAULT 0,
  penalties_missed            INTEGER NOT NULL DEFAULT 0,
  yellow_cards                INTEGER NOT NULL DEFAULT 0,
  red_cards                   INTEGER NOT NULL DEFAULT 0,
  saves                       INTEGER NOT NULL DEFAULT 0,
  bonus                       INTEGER NOT NULL DEFAULT 0,
  expected_goals              REAL    NOT NULL DEFAULT 0,
  expected_assists            REAL    NOT NULL DEFAULT 0,
  expected_goal_involvements  REAL    NOT NULL DEFAULT 0,
  expected_goals_conceded     REAL    NOT NULL DEFAULT 0,
  ict_index                   REAL    NOT NULL DEFAULT 0,
  influence                   REAL    NOT NULL DEFAULT 0,
  creativity                  REAL    NOT NULL DEFAULT 0,
  threat                      REAL    NOT NULL DEFAULT 0,
  status                      TEXT    NOT NULL DEFAULT 'a',  -- a/d/i/s/u
  chance_of_playing            INTEGER,                       -- 0-100, nullable
  news                        TEXT,                           -- Injury/suspension info
  updated_at                  TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_players_team_id  ON players(team_id);
CREATE INDEX IF NOT EXISTS idx_players_position ON players(position);
CREATE INDEX IF NOT EXISTS idx_players_now_cost ON players(now_cost);

CREATE TABLE IF NOT EXISTS fixtures (
  id              INTEGER PRIMARY KEY,  -- FPL fixture ID
  gameweek_id     INTEGER REFERENCES gameweeks(id),
  home_team_id    INTEGER NOT NULL REFERENCES teams(id),
  away_team_id    INTEGER NOT NULL REFERENCES teams(id),
  kickoff_time    TEXT,                  -- ISO timestamp, nullable
  finished        INTEGER NOT NULL DEFAULT 0,
  home_score      INTEGER,
  away_score      INTEGER,
  home_difficulty INTEGER NOT NULL,      -- FDR 1-5
  away_difficulty INTEGER NOT NULL,      -- FDR 1-5
  updated_at      TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_fixtures_gameweek_id  ON fixtures(gameweek_id);
CREATE INDEX IF NOT EXISTS idx_fixtures_home_team_id ON fixtures(home_team_id);
CREATE INDEX IF NOT EXISTS idx_fixtures_away_team_id ON fixtures(away_team_id);
CREATE INDEX IF NOT EXISTS idx_fixtures_kickoff_time ON fixtures(kickoff_time);

CREATE TABLE IF NOT EXISTS player_gameweek_stats (
  player_id                   INTEGER NOT NULL REFERENCES players(id),
  gameweek_id                 INTEGER NOT NULL REFERENCES gameweeks(id),
  fixture_id                  INTEGER NOT NULL REFERENCES fixtures(id),
  opponent_team_id            INTEGER NOT NULL,
  was_home                    INTEGER NOT NULL,  -- 0 or 1
  total_points                INTEGER NOT NULL DEFAULT 0,
  minutes                     INTEGER NOT NULL DEFAULT 0,
  goals                       INTEGER NOT NULL DEFAULT 0,
  assists                     INTEGER NOT NULL DEFAULT 0,
  clean_sheets                INTEGER NOT NULL DEFAULT 0,
  goals_conceded              INTEGER NOT NULL DEFAULT 0,
  own_goals                   INTEGER NOT NULL DEFAULT 0,
  penalties_saved             INTEGER NOT NULL DEFAULT 0,
  penalties_missed            INTEGER NOT NULL DEFAULT 0,
  yellow_cards                INTEGER NOT NULL DEFAULT 0,
  red_cards                   INTEGER NOT NULL DEFAULT 0,
  saves                       INTEGER NOT NULL DEFAULT 0,
  bonus                       INTEGER NOT NULL DEFAULT 0,
  bps                         INTEGER NOT NULL DEFAULT 0,
  expected_goals              REAL    NOT NULL DEFAULT 0,
  expected_assists            REAL    NOT NULL DEFAULT 0,
  expected_goal_involvements  REAL    NOT NULL DEFAULT 0,
  expected_goals_conceded     REAL    NOT NULL DEFAULT 0,
  ict_index                   REAL    NOT NULL DEFAULT 0,
  influence                   REAL    NOT NULL DEFAULT 0,
  creativity                  REAL    NOT NULL DEFAULT 0,
  threat                      REAL    NOT NULL DEFAULT 0,
  value                       INTEGER NOT NULL,  -- Price at this GW (0.1m units)
  transfers_in                INTEGER NOT NULL DEFAULT 0,
  transfers_out               INTEGER NOT NULL DEFAULT 0,
  selected                    INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (player_id, gameweek_id, fixture_id)
);

CREATE INDEX IF NOT EXISTS idx_pgs_player_id       ON player_gameweek_stats(player_id);
CREATE INDEX IF NOT EXISTS idx_pgs_gameweek_id     ON player_gameweek_stats(gameweek_id);
CREATE INDEX IF NOT EXISTS idx_pgs_fixture_id      ON player_gameweek_stats(fixture_id);
CREATE INDEX IF NOT EXISTS idx_pgs_opponent_team_id ON player_gameweek_stats(opponent_team_id);

-- ============================================
-- Sync Metadata
-- ============================================

CREATE TABLE IF NOT EXISTS sync_metadata (
  id                      TEXT PRIMARY KEY DEFAULT 'singleton',
  last_bootstrap_sync     TEXT,
  last_fixtures_sync      TEXT,
  last_player_stats_sync  TEXT,
  last_synced_gameweek    INTEGER
);

INSERT OR IGNORE INTO sync_metadata (id) VALUES ('singleton');

-- ============================================
-- User squads are stored as markdown files in ~/.fplcopilot/squads/
-- See references/squad.md for the format specification.
-- ============================================

#!/usr/bin/env bash
# FPL Copilot — Data Sync Script
# Syncs Fantasy Premier League data into a local SQLite database.
#
# Usage:
#   sync.sh bootstrap              # Teams, gameweeks, players
#   sync.sh fixtures               # All fixtures
#   sync.sh player <id>            # Single player's match-by-match history
#   sync.sh player-stats           # All players' histories (~60s)
#   sync.sh all                    # bootstrap + fixtures + player-stats
#
# Options:
#   --force                        # Bypass freshness checks

set -euo pipefail

# ============================================
# Configuration
# ============================================

FPL_API="https://fantasy.premierleague.com/api"
DATA_DIR="${HOME}/.fplcopilot"
DB="${DATA_DIR}/fplcopilot.db"
SCHEMA_DIR="$(cd "$(dirname "$0")" && pwd)"
SCHEMA="${SCHEMA_DIR}/schema.sql"

BOOTSTRAP_TTL=21600   # 6 hours in seconds
RATE_LIMIT_MS=0.1     # 100ms between player API calls

# ============================================
# Helpers
# ============================================

log()  { echo "[FPL Copilot] $*"; }
warn() { echo "[FPL Copilot] WARNING: $*" >&2; }
die()  { echo "[FPL Copilot] ERROR: $*" >&2; exit 1; }

now_iso() { date -u +%Y-%m-%dT%H:%M:%SZ; }

seconds_since() {
  local ts="$1"
  if [ -z "$ts" ] || [ "$ts" = "null" ]; then
    echo 999999
    return
  fi
  local now_epoch ts_epoch
  now_epoch=$(date -u +%s)
  if date -j -f "%Y-%m-%dT%H:%M:%SZ" "$ts" +%s >/dev/null 2>&1; then
    ts_epoch=$(date -j -f "%Y-%m-%dT%H:%M:%SZ" "$ts" +%s 2>/dev/null || echo 0)
  else
    ts_epoch=$(date -d "$ts" +%s 2>/dev/null || echo 0)
  fi
  echo $(( now_epoch - ts_epoch ))
}

sql() { sqlite3 "$DB" "$@"; }

# Import CSV data into a table via INSERT OR REPLACE
# Usage: csv_upsert <table_name> <csv_file> <column_list>
csv_upsert() {
  local table="$1" csvfile="$2" columns="$3"
  local tmptable="_import_${table}"

  # Create a temp table with no constraints, import CSV, then upsert
  sql <<EOSQL
.mode csv
CREATE TEMP TABLE IF NOT EXISTS ${tmptable} AS SELECT ${columns} FROM ${table} LIMIT 0;
DELETE FROM ${tmptable};
.import --skip 1 ${csvfile} ${tmptable}
INSERT OR REPLACE INTO ${table} (${columns}) SELECT ${columns} FROM ${tmptable};
DROP TABLE IF EXISTS ${tmptable};
EOSQL
}

# ============================================
# Init
# ============================================

init_db() {
  mkdir -p "$DATA_DIR"
  if [ ! -f "$DB" ]; then
    log "Creating database at $DB"
  fi
  sql < "$SCHEMA"
}

# ============================================
# JQ Filters (written to temp files to avoid quoting issues)
# ============================================

write_jq_filters() {
  JQ_TEAMS=$(mktemp)
  cat > "$JQ_TEAMS" << 'JQEOF'
def map_pos: if . == 1 then "GKP" elif . == 2 then "DEF" elif . == 3 then "MID" elif . == 4 then "FWD" else "MID" end;
def nn: . // "";
def nz: (. | tonumber) // 0;
def bl: if . then 1 else 0 end;
.teams | [.[] | [.id, .name, .short_name, .code, .strength, .strength_attack_home, .strength_attack_away, .strength_defence_home, .strength_defence_away, $now]] | (["id","name","short_name","code","strength","strength_attack_home","strength_attack_away","strength_defence_home","strength_defence_away","updated_at"] | @csv), (.[] | @csv)
JQEOF

  JQ_GAMEWEEKS=$(mktemp)
  cat > "$JQ_GAMEWEEKS" << 'JQEOF'
def bl: if . then 1 else 0 end;
.events | [.[] | [.id, .name, .deadline_time, (.finished | bl), (.is_current | bl), (.is_next | bl), (.average_entry_score // ""), (.highest_score // ""), $now]] | (["id","name","deadline_time","finished","is_current","is_next","average_score","highest_score","updated_at"] | @csv), (.[] | @csv)
JQEOF

  JQ_PLAYERS=$(mktemp)
  cat > "$JQ_PLAYERS" << 'JQEOF'
def map_pos: if . == 1 then "GKP" elif . == 2 then "DEF" elif . == 3 then "MID" elif . == 4 then "FWD" else "MID" end;
def nz: (. | tonumber) // 0;
def bl: if . then 1 else 0 end;
.elements | [.[] | [
  .id, .code, .first_name, .second_name, .web_name,
  (.element_type | map_pos), .team, .now_cost, .total_points,
  (.points_per_game | nz), (.form | nz), (.selected_by_percent | nz),
  .minutes, .goals_scored, .assists, .clean_sheets, .goals_conceded,
  .own_goals, .penalties_saved, .penalties_missed, .yellow_cards,
  .red_cards, .saves, .bonus,
  (.expected_goals | nz), (.expected_assists | nz),
  (.expected_goal_involvements | nz), (.expected_goals_conceded | nz),
  (.ict_index | nz), (.influence | nz), (.creativity | nz), (.threat | nz),
  .status, (.chance_of_playing_next_round // ""), (.news // ""), $now
]] | (["id","fpl_code","first_name","last_name","web_name","position","team_id","now_cost","total_points","points_per_game","form","selected_by_percent","minutes","goals","assists","clean_sheets","goals_conceded","own_goals","penalties_saved","penalties_missed","yellow_cards","red_cards","saves","bonus","expected_goals","expected_assists","expected_goal_involvements","expected_goals_conceded","ict_index","influence","creativity","threat","status","chance_of_playing","news","updated_at"] | @csv), (.[] | @csv)
JQEOF

  JQ_FIXTURES=$(mktemp)
  cat > "$JQ_FIXTURES" << 'JQEOF'
def bl: if . then 1 else 0 end;
[.[] | [
  .id, (.event // ""), .team_h, .team_a, (.kickoff_time // ""),
  (.finished | bl), (.team_h_score // ""), (.team_a_score // ""),
  .team_h_difficulty, .team_a_difficulty, $now
]] | (["id","gameweek_id","home_team_id","away_team_id","kickoff_time","finished","home_score","away_score","home_difficulty","away_difficulty","updated_at"] | @csv), (.[] | @csv)
JQEOF

  JQ_PLAYER_STATS=$(mktemp)
  cat > "$JQ_PLAYER_STATS" << 'JQEOF'
def nz: (. | tonumber) // 0;
def bl: if . then 1 else 0 end;
.history | [.[] | [
  .element, .round, .fixture, .opponent_team, (.was_home | bl),
  .total_points, .minutes, .goals_scored, .assists, .clean_sheets,
  .goals_conceded, .own_goals, .penalties_saved, .penalties_missed,
  .yellow_cards, .red_cards, .saves, .bonus, .bps,
  (.expected_goals | nz), (.expected_assists | nz),
  (.expected_goal_involvements | nz), (.expected_goals_conceded | nz),
  (.ict_index | nz), (.influence | nz), (.creativity | nz), (.threat | nz),
  .value, .transfers_in, .transfers_out, .selected
]] | (["player_id","gameweek_id","fixture_id","opponent_team_id","was_home","total_points","minutes","goals","assists","clean_sheets","goals_conceded","own_goals","penalties_saved","penalties_missed","yellow_cards","red_cards","saves","bonus","bps","expected_goals","expected_assists","expected_goal_involvements","expected_goals_conceded","ict_index","influence","creativity","threat","value","transfers_in","transfers_out","selected"] | @csv), (.[] | @csv)
JQEOF
}

cleanup_jq_filters() {
  rm -f "$JQ_TEAMS" "$JQ_GAMEWEEKS" "$JQ_PLAYERS" "$JQ_FIXTURES" "$JQ_PLAYER_STATS" 2>/dev/null || true
}

# ============================================
# Bootstrap Sync
# ============================================

sync_bootstrap() {
  local force="${1:-false}"

  if [ "$force" != "true" ]; then
    local last_sync
    last_sync=$(sql "SELECT last_bootstrap_sync FROM sync_metadata WHERE id='singleton';")
    local age
    age=$(seconds_since "$last_sync")
    if [ "$age" -lt "$BOOTSTRAP_TTL" ]; then
      log "Bootstrap data is fresh (synced $(( age / 60 ))m ago). Use --force to override."
      return 0
    fi
  fi

  log "Fetching bootstrap-static from FPL API..."
  local tmpjson tmpcsv
  tmpjson=$(mktemp)

  if ! curl -sf "${FPL_API}/bootstrap-static/" -o "$tmpjson"; then
    rm -f "$tmpjson"
    die "Failed to fetch bootstrap-static"
  fi

  local now
  now=$(now_iso)

  # --- Teams ---
  local team_count
  team_count=$(jq '.teams | length' "$tmpjson")
  log "Syncing $team_count teams..."

  tmpcsv=$(mktemp)
  jq -r --arg now "$now" -f "$JQ_TEAMS" "$tmpjson" > "$tmpcsv"
  csv_upsert "teams" "$tmpcsv" "id,name,short_name,code,strength,strength_attack_home,strength_attack_away,strength_defence_home,strength_defence_away,updated_at"
  rm -f "$tmpcsv"

  # --- Gameweeks ---
  local gw_count
  gw_count=$(jq '.events | length' "$tmpjson")
  log "Syncing $gw_count gameweeks..."

  tmpcsv=$(mktemp)
  jq -r --arg now "$now" -f "$JQ_GAMEWEEKS" "$tmpjson" > "$tmpcsv"
  csv_upsert "gameweeks" "$tmpcsv" "id,name,deadline_time,finished,is_current,is_next,average_score,highest_score,updated_at"
  rm -f "$tmpcsv"

  # --- Players ---
  local player_count
  player_count=$(jq '.elements | length' "$tmpjson")
  log "Syncing $player_count players..."

  tmpcsv=$(mktemp)
  jq -r --arg now "$now" -f "$JQ_PLAYERS" "$tmpjson" > "$tmpcsv"
  csv_upsert "players" "$tmpcsv" "id,fpl_code,first_name,last_name,web_name,position,team_id,now_cost,total_points,points_per_game,form,selected_by_percent,minutes,goals,assists,clean_sheets,goals_conceded,own_goals,penalties_saved,penalties_missed,yellow_cards,red_cards,saves,bonus,expected_goals,expected_assists,expected_goal_involvements,expected_goals_conceded,ict_index,influence,creativity,threat,status,chance_of_playing,news,updated_at"
  rm -f "$tmpcsv"

  rm -f "$tmpjson"

  sql "UPDATE sync_metadata SET last_bootstrap_sync = '$now' WHERE id = 'singleton';"

  log "Bootstrap sync complete: $team_count teams, $gw_count gameweeks, $player_count players"
}

# ============================================
# Fixtures Sync
# ============================================

sync_fixtures() {
  local force="${1:-false}"

  if [ "$force" != "true" ]; then
    local today today_end count
    today=$(date -u +%Y-%m-%dT00:00:00Z)
    today_end=$(date -u +%Y-%m-%dT23:59:59Z)
    count=$(sql "SELECT COUNT(*) FROM fixtures WHERE kickoff_time >= '$today' AND kickoff_time <= '$today_end';" 2>/dev/null || echo "0")
    if [ "$count" -eq 0 ] 2>/dev/null; then
      log "No fixtures today. Use --force to override."
      return 0
    fi
  fi

  log "Fetching fixtures from FPL API..."
  local tmpjson tmpcsv
  tmpjson=$(mktemp)

  if ! curl -sf "${FPL_API}/fixtures/" -o "$tmpjson"; then
    rm -f "$tmpjson"
    die "Failed to fetch fixtures"
  fi

  local now
  now=$(now_iso)

  local fixture_count
  fixture_count=$(jq 'length' "$tmpjson")
  log "Syncing $fixture_count fixtures..."

  tmpcsv=$(mktemp)
  jq -r --arg now "$now" -f "$JQ_FIXTURES" "$tmpjson" > "$tmpcsv"
  csv_upsert "fixtures" "$tmpcsv" "id,gameweek_id,home_team_id,away_team_id,kickoff_time,finished,home_score,away_score,home_difficulty,away_difficulty,updated_at"
  rm -f "$tmpcsv" "$tmpjson"

  sql "UPDATE sync_metadata SET last_fixtures_sync = '$now' WHERE id = 'singleton';"

  log "Fixtures sync complete: $fixture_count fixtures"
}

# ============================================
# Player Stats Sync (single player)
# ============================================

sync_single_player() {
  local player_id="$1"

  local tmpjson tmpcsv
  tmpjson=$(mktemp)

  if ! curl -sf "${FPL_API}/element-summary/${player_id}/" -o "$tmpjson"; then
    rm -f "$tmpjson"
    warn "Failed to fetch element-summary for player $player_id"
    return 1
  fi

  local stat_count
  stat_count=$(jq '.history | length' "$tmpjson")

  if [ "$stat_count" -eq 0 ]; then
    rm -f "$tmpjson"
    return 0
  fi

  tmpcsv=$(mktemp)
  jq -r -f "$JQ_PLAYER_STATS" "$tmpjson" > "$tmpcsv"
  csv_upsert "player_gameweek_stats" "$tmpcsv" "player_id,gameweek_id,fixture_id,opponent_team_id,was_home,total_points,minutes,goals,assists,clean_sheets,goals_conceded,own_goals,penalties_saved,penalties_missed,yellow_cards,red_cards,saves,bonus,bps,expected_goals,expected_assists,expected_goal_involvements,expected_goals_conceded,ict_index,influence,creativity,threat,value,transfers_in,transfers_out,selected"
  rm -f "$tmpcsv" "$tmpjson"

  log "Player $player_id: $stat_count gameweek records synced"
}

# ============================================
# Player Stats Sync (batch)
# ============================================

sync_all_player_stats() {
  local force="${1:-false}"

  if [ "$force" != "true" ]; then
    local last_gw latest_finished
    last_gw=$(sql "SELECT last_synced_gameweek FROM sync_metadata WHERE id='singleton';")
    latest_finished=$(sql "SELECT MAX(id) FROM gameweeks WHERE finished = 1;")

    if [ -z "$latest_finished" ] || [ "$latest_finished" = "" ]; then
      log "No finished gameweeks. Nothing to sync."
      return 0
    fi

    if [ -n "$last_gw" ] && [ "$last_gw" != "" ] && [ "$latest_finished" -le "$last_gw" ] 2>/dev/null; then
      log "No new finished gameweek (last synced: GW$last_gw, latest finished: GW$latest_finished). Use --force to override."
      return 0
    fi

    log "New gameweek detected: GW$latest_finished (last synced: ${last_gw:-none})"
  fi

  local player_ids
  player_ids=$(sql "SELECT id FROM players ORDER BY id;")
  local total
  total=$(echo "$player_ids" | wc -l | tr -d ' ')
  local success=0 fail=0 current=0

  log "Syncing player stats for $total players..."

  while IFS= read -r pid; do
    [ -z "$pid" ] && continue
    current=$(( current + 1 ))
    if sync_single_player "$pid" 2>/dev/null; then
      success=$(( success + 1 ))
    else
      fail=$(( fail + 1 ))
    fi

    if [ $(( current % 50 )) -eq 0 ]; then
      log "Progress: $current/$total ($success synced, $fail failed)"
    fi

    sleep "$RATE_LIMIT_MS"
  done <<< "$player_ids"

  local latest_finished
  latest_finished=$(sql "SELECT MAX(id) FROM gameweeks WHERE finished = 1;")
  local now
  now=$(now_iso)
  sql "UPDATE sync_metadata SET last_player_stats_sync = '$now', last_synced_gameweek = $latest_finished WHERE id = 'singleton';"

  log "Player stats sync complete: $success/$total synced ($fail failed)"
}

# ============================================
# Full Sync
# ============================================

sync_all() {
  log "Starting full sync..."
  sync_bootstrap "true"
  sync_fixtures "true"
  sync_all_player_stats "true"
  log "Full sync complete."
}

# ============================================
# Main
# ============================================

main() {
  local command="${1:-}"
  local force="false"

  for arg in "$@"; do
    if [ "$arg" = "--force" ] || [ "$arg" = "-f" ]; then
      force="true"
    fi
  done

  for cmd in curl jq sqlite3; do
    if ! command -v "$cmd" >/dev/null 2>&1; then
      die "Required command not found: $cmd"
    fi
  done

  init_db
  write_jq_filters
  trap cleanup_jq_filters EXIT

  case "$command" in
    bootstrap)
      sync_bootstrap "$force"
      ;;
    fixtures)
      sync_fixtures "$force"
      ;;
    player)
      local player_id="${2:-}"
      if [ -z "$player_id" ]; then
        die "Usage: sync.sh player <player_id>"
      fi
      sync_single_player "$player_id"
      ;;
    player-stats)
      sync_all_player_stats "$force"
      ;;
    all)
      sync_all
      ;;
    *)
      echo "FPL Copilot Sync"
      echo ""
      echo "Usage:"
      echo "  sync.sh bootstrap              # Sync teams, gameweeks, players"
      echo "  sync.sh fixtures               # Sync fixtures"
      echo "  sync.sh player <id>            # Sync single player history"
      echo "  sync.sh player-stats           # Sync all player histories"
      echo "  sync.sh all                    # Full sync (everything)"
      echo ""
      echo "Options:"
      echo "  --force                        # Bypass freshness checks"
      exit 1
      ;;
  esac
}

main "$@"

<!--
  TEMPLATE: captain-ranking.html
  Replace placeholder data with top N captain options for the GW.
  Slots: {gw_num}, {deadline}, {candidates[]} — for each: rank, web_name, team_color, team_code, position, fixture (opp, was_home, fdr), form, xpts, ownership_pct, recommendation_score, reasoning.
-->
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Captain Picks — GW14</title>
<style>
  :root {
    --pl-purple: #37003c; --pl-teal: #00ff87; --pl-magenta: #e90052;
    --fdr-1: #00ff87; --fdr-2: #91dfb3; --fdr-3: #e8e8e8; --fdr-4: #ff6b85; --fdr-5: #e90052;
    --bg: #fafafa; --surface: #ffffff; --text: #1a1a1a; --text-muted: #6b6b6b; --border: #e5e5e5;
  }
  @media (prefers-color-scheme: dark) {
    :root { --bg: #0a0a0a; --surface: #161616; --text: #f0f0f0; --text-muted: #9b9b9b; --border: #2a2a2a; }
    .fdr-3 { background: #4a4a4a !important; color: #f0f0f0 !important; }
  }
  * { box-sizing: border-box; }
  body { font-family: ui-sans-serif, -apple-system, "Segoe UI", sans-serif; font-size: 15px; line-height: 1.55; max-width: 1100px; margin: 2rem auto; padding: 0 1.25rem; color: var(--text); background: var(--bg); }
  h1 { font-family: Georgia, serif; font-weight: 600; letter-spacing: -0.01em; font-size: 1.75rem; margin-bottom: 0.25rem; }
  .subtitle { color: var(--text-muted); font-size: 0.9rem; margin-top: 0; }
  .top-pick { background: var(--surface); border: 2px solid var(--pl-teal); border-radius: 8px; padding: 1.25rem 1.5rem; margin: 1.5rem 0; display: flex; align-items: center; gap: 1.5rem; flex-wrap: wrap; }
  .crown-svg { width: 32px; height: 32px; color: var(--pl-magenta); flex-shrink: 0; }
  .top-pick-body { flex: 1; min-width: 200px; }
  .top-pick .label { font-size: 0.7rem; text-transform: uppercase; letter-spacing: 0.08em; color: var(--text-muted); font-weight: 600; }
  .top-pick .name { font-family: Georgia, serif; font-size: 1.5rem; font-weight: 600; margin: 0.15rem 0; }
  .top-pick .meta { color: var(--text-muted); font-size: 0.9rem; }
  .top-pick .xpts { text-align: right; }
  .top-pick .xpts-num { font-size: 2rem; font-weight: 700; color: var(--pl-purple); font-variant-numeric: tabular-nums; line-height: 1; }
  .top-pick .xpts-label { font-size: 0.75rem; text-transform: uppercase; letter-spacing: 0.05em; color: var(--text-muted); margin-top: 0.25rem; }
  table { width: 100%; border-collapse: collapse; font-variant-numeric: tabular-nums; margin-top: 1rem; }
  th, td { padding: 0.6rem 0.5rem; text-align: left; border-bottom: 1px solid var(--border); }
  th { font-weight: 600; font-size: 0.75rem; color: var(--text-muted); text-transform: uppercase; letter-spacing: 0.05em; cursor: pointer; user-select: none; background: var(--surface); }
  th:hover { color: var(--pl-purple); }
  th[data-dir="asc"]::after { content: " ▲"; }
  th[data-dir="desc"]::after { content: " ▼"; }
  td.num { text-align: right; font-weight: 600; }
  .team-badge { display: inline-flex; align-items: center; gap: 0.35rem; }
  .team-dot { width: 9px; height: 9px; border-radius: 50%; background: var(--team-color); border: 1px solid rgba(0,0,0,0.15); }
  .fix-chip { display: inline-block; padding: 0.15rem 0.4rem; border-radius: 3px; font-size: 0.75rem; font-weight: 600; }
  .fdr-1 { background: var(--fdr-1); color: #0a4a2c; }
  .fdr-2 { background: var(--fdr-2); color: #0a4a2c; }
  .fdr-3 { background: var(--fdr-3); color: #1a1a1a; }
  .fdr-4 { background: var(--fdr-4); color: #5a1020; }
  .fdr-5 { background: var(--fdr-5); color: #ffffff; }
  .away { opacity: 0.75; font-style: italic; }
  .row-toggle { cursor: pointer; }
  .row-toggle:hover { background: var(--bg); }
  .reasoning { background: var(--bg); display: none; }
  .reasoning.open { display: table-row; }
  .reasoning td { padding: 1rem 1.5rem; color: var(--text-muted); font-size: 0.9rem; border-bottom: 1px solid var(--border); }
  .conf { display: inline-block; width: 60px; height: 6px; background: var(--border); border-radius: 3px; overflow: hidden; vertical-align: middle; }
  .conf-bar { height: 100%; background: var(--pl-teal); }
  .rank { font-family: Georgia, serif; font-weight: 600; color: var(--text-muted); }
</style>
</head>
<body>

<svg width="0" height="0" style="display:none"><defs>
  <symbol id="i-crown" viewBox="0 0 24 24"><path d="M2 17h20l-2-9-5 4-3-8-3 8-5-4z" fill="currentColor"/></symbol>
</defs></svg>

<h1>Captain Picks</h1>
<p class="subtitle">GW14 · Deadline Saturday 11:30 · 8 candidates ranked</p>

<div class="top-pick">
  <svg class="crown-svg" aria-hidden="true"><use href="#i-crown"/></svg>
  <div class="top-pick-body">
    <div class="label">Top pick</div>
    <div class="name">Mohamed Salah</div>
    <div class="meta"><span class="team-badge" style="--team-color:#c8102e"><span class="team-dot"></span>LIV</span> · vs <span class="fix-chip fdr-2 away">WOL (A)</span> · Form 8.0 · Ownership 38%</div>
  </div>
  <div class="xpts">
    <div class="xpts-num">8.6</div>
    <div class="xpts-label">xPts captained</div>
  </div>
</div>

<table data-sortable>
  <thead>
    <tr>
      <th data-type="number">#</th>
      <th data-type="text">Player</th>
      <th data-type="text">Fixture</th>
      <th data-type="number">Form</th>
      <th data-type="number">xPts</th>
      <th data-type="number">Own %</th>
      <th data-type="number">Confidence</th>
    </tr>
  </thead>
  <tbody>
    <tr class="row-toggle">
      <td class="rank">1</td>
      <td><span class="team-badge" style="--team-color:#c8102e"><span class="team-dot"></span></span> Salah</td>
      <td><span class="fix-chip fdr-2 away">WOL (A)</span></td>
      <td class="num">8.0</td>
      <td class="num">8.6</td>
      <td class="num">38%</td>
      <td class="num"><span class="conf"><span class="conf-bar" style="width:92%"></span></span></td>
    </tr>
    <tr class="reasoning"><td colspan="7">Salah has averaged 7.2 ppg over the last 5 GWs, faces a Wolves side that's conceded 9 in their last 6. Ownership floor at 38% caps downside vs the field. Reliable double-digit ceiling.</td></tr>

    <tr class="row-toggle">
      <td class="rank">2</td>
      <td><span class="team-badge" style="--team-color:#034694"><span class="team-dot"></span></span> Palmer</td>
      <td><span class="fix-chip fdr-3">MCI (H)</span></td>
      <td class="num">8.2</td>
      <td class="num">8.1</td>
      <td class="num">31%</td>
      <td class="num"><span class="conf"><span class="conf-bar" style="width:78%"></span></span></td>
    </tr>
    <tr class="reasoning"><td colspan="7">Form leader with 41 pts last 5. Home vs City is harder than the FDR suggests, but Palmer's set-piece duties and penalty role give him a higher floor than most premium MIDs. Differential edge if City defense holds.</td></tr>

    <tr class="row-toggle">
      <td class="rank">3</td>
      <td><span class="team-badge" style="--team-color:#dd0000"><span class="team-dot"></span></span> Wood</td>
      <td><span class="fix-chip fdr-2">LEE (H)</span></td>
      <td class="num">7.4</td>
      <td class="num">7.2</td>
      <td class="num">14%</td>
      <td class="num"><span class="conf"><span class="conf-bar" style="width:65%"></span></span></td>
    </tr>
    <tr class="reasoning"><td colspan="7">Differential punt at 14% ownership. Home vs Leeds with Wood as the lone striker — clear shot-on-goal monopoly. Risk: Forest rotation possible after midweek European fixture.</td></tr>

    <tr class="row-toggle">
      <td class="rank">4</td>
      <td><span class="team-badge" style="--team-color:#241f20"><span class="team-dot"></span></span> Isak</td>
      <td><span class="fix-chip fdr-2 away">BUR (A)</span></td>
      <td class="num">6.6</td>
      <td class="num">7.0</td>
      <td class="num">22%</td>
      <td class="num"><span class="conf"><span class="conf-bar" style="width:62%"></span></span></td>
    </tr>
    <tr class="reasoning"><td colspan="7">Burnley away is winnable, but Isak's xG conversion has been streaky — overperformed by 2.1 goals this season, due regression. Solid floor, lower ceiling than the top 3.</td></tr>

    <tr class="row-toggle">
      <td class="rank">5</td>
      <td><span class="team-badge" style="--team-color:#6cabdd"><span class="team-dot"></span></span> Haaland</td>
      <td><span class="fix-chip fdr-5 away">CHE (A)</span></td>
      <td class="num">4.6</td>
      <td class="num">5.8</td>
      <td class="num">52%</td>
      <td class="num"><span class="conf"><span class="conf-bar" style="width:48%"></span></span></td>
    </tr>
    <tr class="reasoning"><td colspan="7">Template captain, but Chelsea away is a brutal fixture. Highest base xPts in the model gets dragged down by FDR. If you're chasing rank, sticking with him is defensible; if you're attacking rank, pivot.</td></tr>

    <tr class="row-toggle">
      <td class="rank">6</td>
      <td><span class="team-badge" style="--team-color:#ef0107"><span class="team-dot"></span></span> Saka</td>
      <td><span class="fix-chip fdr-2">CHE (H)</span></td>
      <td class="num">6.2</td>
      <td class="num">6.4</td>
      <td class="num">24%</td>
      <td class="num"><span class="conf"><span class="conf-bar" style="width:55%"></span></span></td>
    </tr>
    <tr class="reasoning"><td colspan="7">Home in a London derby, but Saka's xG has been suppressed in the last 3 — touches in the box dropped by 18%. Wait for confirmed minutes before locking in.</td></tr>
  </tbody>
</table>

<script>
  document.querySelectorAll('table[data-sortable] th').forEach((th, col) => {
    th.addEventListener('click', () => {
      const tbody = th.closest('table').tBodies[0];
      const pairs = [];
      for (let i = 0; i < tbody.rows.length; i += 2) pairs.push([tbody.rows[i], tbody.rows[i + 1]]);
      const type = th.dataset.type || 'text';
      const dir = th.dataset.dir === 'asc' ? -1 : 1;
      th.parentElement.querySelectorAll('th').forEach(h => h.removeAttribute('data-dir'));
      th.dataset.dir = dir === 1 ? 'asc' : 'desc';
      pairs.sort(([a], [b]) => {
        const av = a.cells[col].textContent.trim();
        const bv = b.cells[col].textContent.trim();
        return type === 'number' ? (parseFloat(av) - parseFloat(bv)) * dir : av.localeCompare(bv) * dir;
      });
      pairs.forEach(([r1, r2]) => { tbody.appendChild(r1); tbody.appendChild(r2); });
    });
  });
  document.querySelectorAll('.row-toggle').forEach(tr => {
    tr.addEventListener('click', () => {
      const next = tr.nextElementSibling;
      if (next && next.classList.contains('reasoning')) next.classList.toggle('open');
    });
  });
</script>

</body>
</html>

<!--
  TEMPLATE: fixture-matrix.html
  Replace placeholder data with real fixtures from `fixtures` table joined with `teams`.
  Slots: {gw_start}, {gw_end}, {teams[]} — for each team: short_name, team_color, fixtures[] (one per GW: opponent_code, was_home, fdr), avg_fdr.
  Sortable by any GW column or Avg FDR. Click a row to highlight.
-->
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Fixture Difficulty — GW14 → GW19</title>
<style>
  :root {
    --pl-purple: #37003c; --fdr-1: #00ff87; --fdr-2: #91dfb3; --fdr-3: #e8e8e8; --fdr-4: #ff6b85; --fdr-5: #e90052;
    --bg: #fafafa; --surface: #ffffff; --text: #1a1a1a; --text-muted: #6b6b6b; --border: #e5e5e5;
  }
  @media (prefers-color-scheme: dark) {
    :root { --bg: #0a0a0a; --surface: #161616; --text: #f0f0f0; --text-muted: #9b9b9b; --border: #2a2a2a; }
    .fdr-3 { background: #4a4a4a !important; color: #f0f0f0 !important; }
  }
  * { box-sizing: border-box; }
  body { font-family: ui-sans-serif, -apple-system, "Segoe UI", sans-serif; font-size: 15px; line-height: 1.55; max-width: 1100px; margin: 2rem auto; padding: 0 1.25rem; color: var(--text); background: var(--bg); }
  h1 { font-family: Georgia, serif; font-weight: 600; letter-spacing: -0.01em; font-size: 1.75rem; margin-bottom: 0.25rem; }
  .subtitle { color: var(--text-muted); font-size: 0.9rem; margin-top: 0; }
  .legend { display: flex; gap: 0.5rem; align-items: center; margin: 1rem 0; font-size: 0.8rem; color: var(--text-muted); }
  .legend-cell { display: inline-block; width: 28px; height: 18px; border-radius: 3px; text-align: center; line-height: 18px; font-weight: 700; font-size: 0.75rem; }
  table { width: 100%; border-collapse: separate; border-spacing: 2px; font-variant-numeric: tabular-nums; }
  th { padding: 0.5rem 0.5rem; text-align: center; font-weight: 600; font-size: 0.75rem; color: var(--text-muted); text-transform: uppercase; letter-spacing: 0.05em; cursor: pointer; user-select: none; background: var(--surface); border-radius: 4px; }
  th:hover { color: var(--pl-purple); }
  th[data-dir="asc"]::after { content: " ▲"; }
  th[data-dir="desc"]::after { content: " ▼"; }
  th.team-col { text-align: left; }
  td { padding: 0.6rem 0.4rem; text-align: center; border-radius: 4px; font-weight: 600; font-size: 0.85rem; }
  td.team-cell { text-align: left; background: var(--surface); padding-left: 0.75rem; }
  .team-badge { display: inline-flex; align-items: center; gap: 0.4rem; font-weight: 600; }
  .team-dot { width: 10px; height: 10px; border-radius: 50%; background: var(--team-color); border: 1px solid rgba(0,0,0,0.15); flex-shrink: 0; }
  .fdr-1 { background: var(--fdr-1); color: #0a4a2c; }
  .fdr-2 { background: var(--fdr-2); color: #0a4a2c; }
  .fdr-3 { background: var(--fdr-3); color: #1a1a1a; }
  .fdr-4 { background: var(--fdr-4); color: #5a1020; }
  .fdr-5 { background: var(--fdr-5); color: #ffffff; }
  .avg { background: var(--surface); color: var(--text); font-weight: 700; }
  tbody tr.highlight td { outline: 2px solid var(--pl-purple); }
  tbody tr { cursor: pointer; }
  .away { opacity: 0.75; font-weight: 500; font-style: italic; }
  @media (max-width: 700px) { td, th { padding: 0.4rem 0.25rem; font-size: 0.75rem; } }
</style>
</head>
<body>

<h1>Fixture Difficulty</h1>
<p class="subtitle">GW14 → GW19 · 6 gameweeks · Generated 2026-05-13</p>

<div class="legend">
  Difficulty:
  <span class="legend-cell fdr-1">1</span>
  <span class="legend-cell fdr-2">2</span>
  <span class="legend-cell fdr-3">3</span>
  <span class="legend-cell fdr-4">4</span>
  <span class="legend-cell fdr-5">5</span>
  · Home in bold, away italic · Click column to sort · Click row to highlight
</div>

<table data-sortable>
  <thead>
    <tr>
      <th data-type="text" class="team-col">Team</th>
      <th data-type="number">GW14</th>
      <th data-type="number">GW15</th>
      <th data-type="number">GW16</th>
      <th data-type="number">GW17</th>
      <th data-type="number">GW18</th>
      <th data-type="number">GW19</th>
      <th data-type="number">Avg</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td class="team-cell"><span class="team-badge" style="--team-color:#ef0107"><span class="team-dot"></span>ARS</span></td>
      <td class="fdr-2" data-sort="2">CHE</td>
      <td class="fdr-3" data-sort="3"><span class="away">BHA</span></td>
      <td class="fdr-2" data-sort="2">WOL</td>
      <td class="fdr-4" data-sort="4"><span class="away">LIV</span></td>
      <td class="fdr-2" data-sort="2">CRY</td>
      <td class="fdr-3" data-sort="3"><span class="away">FUL</span></td>
      <td class="avg" data-sort="2.7">2.7</td>
    </tr>
    <tr>
      <td class="team-cell"><span class="team-badge" style="--team-color:#c8102e"><span class="team-dot"></span>LIV</span></td>
      <td class="fdr-2" data-sort="2"><span class="away">WOL</span></td>
      <td class="fdr-3" data-sort="3">BRE</td>
      <td class="fdr-4" data-sort="4"><span class="away">NEW</span></td>
      <td class="fdr-4" data-sort="4">ARS</td>
      <td class="fdr-2" data-sort="2"><span class="away">SUN</span></td>
      <td class="fdr-3" data-sort="3">EVE</td>
      <td class="avg" data-sort="3.0">3.0</td>
    </tr>
    <tr>
      <td class="team-cell"><span class="team-badge" style="--team-color:#6cabdd"><span class="team-dot"></span>MCI</span></td>
      <td class="fdr-5" data-sort="5">CHE</td>
      <td class="fdr-4" data-sort="4"><span class="away">TOT</span></td>
      <td class="fdr-3" data-sort="3">FUL</td>
      <td class="fdr-5" data-sort="5"><span class="away">ARS</span></td>
      <td class="fdr-4" data-sort="4">NEW</td>
      <td class="fdr-3" data-sort="3"><span class="away">BHA</span></td>
      <td class="avg" data-sort="4.0">4.0</td>
    </tr>
    <tr>
      <td class="team-cell"><span class="team-badge" style="--team-color:#241f20"><span class="team-dot"></span>NEW</span></td>
      <td class="fdr-2" data-sort="2"><span class="away">BUR</span></td>
      <td class="fdr-3" data-sort="3">CRY</td>
      <td class="fdr-4" data-sort="4">LIV</td>
      <td class="fdr-3" data-sort="3"><span class="away">WHU</span></td>
      <td class="fdr-4" data-sort="4"><span class="away">MCI</span></td>
      <td class="fdr-2" data-sort="2">LEE</td>
      <td class="avg" data-sort="3.0">3.0</td>
    </tr>
    <tr>
      <td class="team-cell"><span class="team-badge" style="--team-color:#132257"><span class="team-dot"></span>TOT</span></td>
      <td class="fdr-3" data-sort="3"><span class="away">FUL</span></td>
      <td class="fdr-3" data-sort="3">MCI</td>
      <td class="fdr-2" data-sort="2"><span class="away">BOU</span></td>
      <td class="fdr-2" data-sort="2">BUR</td>
      <td class="fdr-3" data-sort="3"><span class="away">EVE</span></td>
      <td class="fdr-4" data-sort="4">CHE</td>
      <td class="avg" data-sort="2.8">2.8</td>
    </tr>
    <tr>
      <td class="team-cell"><span class="team-badge" style="--team-color:#dd0000"><span class="team-dot"></span>NFO</span></td>
      <td class="fdr-2" data-sort="2">LEE</td>
      <td class="fdr-3" data-sort="3"><span class="away">EVE</span></td>
      <td class="fdr-2" data-sort="2">SUN</td>
      <td class="fdr-4" data-sort="4"><span class="away">CHE</span></td>
      <td class="fdr-2" data-sort="2">BOU</td>
      <td class="fdr-3" data-sort="3"><span class="away">BRE</span></td>
      <td class="avg" data-sort="2.7">2.7</td>
    </tr>
  </tbody>
</table>

<script>
  document.querySelectorAll('table[data-sortable] th').forEach((th, col) => {
    th.addEventListener('click', () => {
      const tbody = th.closest('table').tBodies[0];
      const rows = [...tbody.rows];
      const type = th.dataset.type || 'text';
      const dir = th.dataset.dir === 'asc' ? -1 : 1;
      th.parentElement.querySelectorAll('th').forEach(h => h.removeAttribute('data-dir'));
      th.dataset.dir = dir === 1 ? 'asc' : 'desc';
      rows.sort((a, b) => {
        const av = a.cells[col].dataset.sort ?? a.cells[col].textContent.trim();
        const bv = b.cells[col].dataset.sort ?? b.cells[col].textContent.trim();
        return type === 'number' ? (parseFloat(av) - parseFloat(bv)) * dir : av.localeCompare(bv) * dir;
      });
      rows.forEach(r => tbody.appendChild(r));
    });
  });
  document.querySelectorAll('tbody tr').forEach(tr => {
    tr.addEventListener('click', () => tr.classList.toggle('highlight'));
  });
</script>

</body>
</html>