name: CultRoll D1 Migration

on:
  workflow_dispatch:

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: '22'

      - name: Drop all tables (clean slate)
        env:
          CLOUDFLARE_API_TOKEN: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          CLOUDFLARE_ACCOUNT_ID: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
        run: |
          # Use --file to ensure ALL statements run (--command may only execute first statement)
          tmpdir="${TMPDIR:-${RUNNER_TEMP:-$HOME/.tmp}}"
          mkdir -p "$tmpdir"
          cat > "$tmpdir/cultroll-drop.sql" << 'EOF'
          DROP TABLE IF EXISTS recommendations;
          DROP TABLE IF EXISTS collection_titles;
          DROP TABLE IF EXISTS collections;
          DROP TABLE IF EXISTS tvod_links;
          DROP TABLE IF EXISTS showtimes;
          DROP TABLE IF EXISTS events;
          DROP TABLE IF EXISTS editorial_posts;
          DROP TABLE IF EXISTS chain_movie_refs;
          DROP TABLE IF EXISTS movies;
          DROP TABLE IF EXISTS title_chain_refs;
          DROP TABLE IF EXISTS title_credits;
          DROP TABLE IF EXISTS titles;
          DROP TABLE IF EXISTS genre_aliases;
          DROP TABLE IF EXISTS genres;
          DROP TABLE IF EXISTS source_discovered_cinemas;
          DROP TABLE IF EXISTS source_target_cinemas;
          DROP TABLE IF EXISTS source_targets;
          DROP TABLE IF EXISTS pipeline_targets;
          DROP TABLE IF EXISTS scraper_runs;
          DROP TABLE IF EXISTS cinemas;
          DROP TABLE IF EXISTS cinema_chains;
          DROP TABLE IF EXISTS city_waitlist;
          DROP TABLE IF EXISTS settings;
          DROP TABLE IF EXISTS cities;
          EOF
          npx wrangler d1 execute cultroll-db --remote --file "$tmpdir/cultroll-drop.sql"

      - name: Apply fresh schema
        env:
          CLOUDFLARE_API_TOKEN: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          CLOUDFLARE_ACCOUNT_ID: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
        run: |
          npx wrangler d1 execute cultroll-db --remote \
            --file src/sites/cultroll/lib/schema.sql

      - name: Verify schema
        env:
          CLOUDFLARE_API_TOKEN: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          CLOUDFLARE_ACCOUNT_ID: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
        run: |
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT COUNT(*) as genre_count FROM genres"
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT COUNT(*) as alias_count FROM genre_aliases"
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT COUNT(*) as city_count FROM cities"
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT COUNT(*) as chain_count FROM cinema_chains"
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT id, adapter_id, country_code, label, enabled FROM source_targets ORDER BY adapter_id, country_code, id"
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT source_target_id, external_cinema_id, cinema_id FROM source_target_cinemas ORDER BY source_target_id, priority"
          npx wrangler d1 execute cultroll-db --remote --command \
            "SELECT id, adapter_id, label, enabled FROM pipeline_targets ORDER BY id"
