name: Sync CultRoll Infrastructure

on:
  push:
    branches: [main]
    paths:
      - "src/sites/cultroll/lib/schema.sql"
      - "src/sites/cultroll/lib/seed.sql"
      - "wrangler.cultroll.toml"
      - "wrangler.cultroll-admin.toml"
  workflow_dispatch:

jobs:
  sync:
    runs-on: ubuntu-latest
    permissions:
      contents: read
    steps:
      - uses: actions/checkout@v4
        with:
          lfs: true

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

      - name: Create D1 database
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 create cultroll-db

      - name: Run D1 schema migration
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --file=src/sites/cultroll/lib/schema.sql --remote

      - name: D1 migration — tmdb_enriched_at
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "ALTER TABLE movies ADD COLUMN tmdb_enriched_at TEXT"

      - name: D1 migration — movies columns
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "ALTER TABLE movies ADD COLUMN duration_min INTEGER; ALTER TABLE movies ADD COLUMN title_original TEXT; ALTER TABLE movies ADD COLUMN language TEXT; ALTER TABLE movies ADD COLUMN subtitles TEXT; ALTER TABLE movies ADD COLUMN backdrop_url TEXT; ALTER TABLE movies ADD COLUMN trailer_url TEXT; ALTER TABLE movies ADD COLUMN director TEXT; ALTER TABLE movies ADD COLUMN cast_list TEXT; ALTER TABLE movies ADD COLUMN tags TEXT; ALTER TABLE movies ADD COLUMN imdb_id TEXT; ALTER TABLE movies ADD COLUMN tmdb_id TEXT; ALTER TABLE movies ADD COLUMN release_date TEXT; ALTER TABLE movies ADD COLUMN synopsis_en TEXT; ALTER TABLE movies ADD COLUMN synopsis_ar TEXT; ALTER TABLE movies ADD COLUMN rating TEXT; ALTER TABLE movies ADD COLUMN genre TEXT; ALTER TABLE movies ADD COLUMN is_independent INTEGER DEFAULT 0; ALTER TABLE movies ADD COLUMN is_featured INTEGER DEFAULT 0;"

      - name: D1 migration — titles tmdb_popularity
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "ALTER TABLE titles ADD COLUMN tmdb_popularity REAL"

      - name: D1 migration — titles tmdb_vote_average
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "ALTER TABLE titles ADD COLUMN tmdb_vote_average REAL"

      - name: D1 migration — titles tmdb_vote_count
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "ALTER TABLE titles ADD COLUMN tmdb_vote_count INTEGER"

      - name: D1 migration — titles is_enabled
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "ALTER TABLE titles ADD COLUMN is_enabled INTEGER NOT NULL DEFAULT 1"

      - name: D1 migration — title_credits
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS title_credits (id INTEGER PRIMARY KEY AUTOINCREMENT, title_id TEXT NOT NULL REFERENCES titles(id) ON DELETE CASCADE, person_tmdb_id TEXT, person_name TEXT NOT NULL, credit_type TEXT NOT NULL CHECK(credit_type IN ('cast','crew')), department TEXT, job TEXT NOT NULL DEFAULT '', character_name TEXT NOT NULL DEFAULT '', credit_order INTEGER, is_primary INTEGER NOT NULL DEFAULT 0, popularity REAL, profile_path TEXT, updated_at TEXT NOT NULL DEFAULT (datetime('now'))); CREATE UNIQUE INDEX IF NOT EXISTS idx_title_credits_unique ON title_credits(title_id, credit_type, person_tmdb_id, person_name, job, character_name); CREATE INDEX IF NOT EXISTS idx_title_credits_person ON title_credits(person_tmdb_id, person_name, credit_type); CREATE INDEX IF NOT EXISTS idx_title_credits_title ON title_credits(title_id, credit_type, credit_order);"

      - name: D1 migration — chain_movie_refs
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS chain_movie_refs (chain_id TEXT NOT NULL, chain_movie_id TEXT NOT NULL, movie_id TEXT NOT NULL REFERENCES movies(id) ON DELETE CASCADE, confirmed INTEGER DEFAULT 0, created_at TEXT DEFAULT (datetime('now')), PRIMARY KEY (chain_id, chain_movie_id)); CREATE INDEX IF NOT EXISTS idx_chain_refs_movie ON chain_movie_refs(movie_id);"

      - name: D1 migration — booking_clicks
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS booking_clicks (id INTEGER PRIMARY KEY AUTOINCREMENT, showtime_id TEXT NOT NULL, title_id TEXT NOT NULL, cinema_id TEXT NOT NULL, city_id TEXT NOT NULL, booking_domain TEXT NOT NULL, source TEXT, referer TEXT, user_agent TEXT, clicked_at TEXT NOT NULL DEFAULT (datetime('now'))); CREATE INDEX IF NOT EXISTS idx_booking_clicks_showtime ON booking_clicks(showtime_id, clicked_at); CREATE INDEX IF NOT EXISTS idx_booking_clicks_title ON booking_clicks(title_id, clicked_at); CREATE INDEX IF NOT EXISTS idx_booking_clicks_cinema ON booking_clicks(cinema_id, clicked_at); CREATE INDEX IF NOT EXISTS idx_booking_clicks_date ON booking_clicks(clicked_at);"

      - name: D1 migration — title_alerts
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS title_alerts (id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL, title_id TEXT NOT NULL, title_en TEXT, locale TEXT NOT NULL DEFAULT 'en', source TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now'))); CREATE UNIQUE INDEX IF NOT EXISTS idx_title_alerts_unique ON title_alerts(email, title_id); CREATE INDEX IF NOT EXISTS idx_title_alerts_title ON title_alerts(title_id, created_at); CREATE INDEX IF NOT EXISTS idx_title_alerts_date ON title_alerts(created_at); CREATE INDEX IF NOT EXISTS idx_title_alerts_email ON title_alerts(email);"

      - name: D1 migration — cultroll users auth
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS cultroll_users (id TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, display_name TEXT, avatar_url TEXT, google_sub TEXT UNIQUE, status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'suspended')), email_verified_at TEXT, last_login_at TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now')), updated_at TEXT NOT NULL DEFAULT (datetime('now'))); CREATE INDEX IF NOT EXISTS idx_cultroll_users_status ON cultroll_users(status, created_at); CREATE INDEX IF NOT EXISTS idx_cultroll_users_last_login ON cultroll_users(last_login_at); CREATE TABLE IF NOT EXISTS cultroll_user_sessions (id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES cultroll_users(id) ON DELETE CASCADE, token_hash TEXT NOT NULL UNIQUE, user_agent TEXT, ip_address TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now')), updated_at TEXT NOT NULL DEFAULT (datetime('now')), last_seen_at TEXT NOT NULL DEFAULT (datetime('now')), expires_at TEXT NOT NULL, revoked_at TEXT); CREATE INDEX IF NOT EXISTS idx_cultroll_user_sessions_user ON cultroll_user_sessions(user_id, expires_at); CREATE INDEX IF NOT EXISTS idx_cultroll_user_sessions_expiry ON cultroll_user_sessions(expires_at, revoked_at); CREATE TABLE IF NOT EXISTS cultroll_magic_links (id TEXT PRIMARY KEY, email TEXT NOT NULL, token_hash TEXT NOT NULL UNIQUE, redirect_path TEXT, user_agent TEXT, ip_address TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now')), updated_at TEXT NOT NULL DEFAULT (datetime('now')), expires_at TEXT NOT NULL, consumed_at TEXT); CREATE INDEX IF NOT EXISTS idx_cultroll_magic_links_email ON cultroll_magic_links(email, created_at); CREATE INDEX IF NOT EXISTS idx_cultroll_magic_links_expiry ON cultroll_magic_links(expires_at, consumed_at);"

      - name: D1 migration — cultroll watchlist
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS cultroll_user_watchlist (id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES cultroll_users(id) ON DELETE CASCADE, title_id TEXT NOT NULL REFERENCES titles(id) ON DELETE CASCADE, added_at TEXT NOT NULL DEFAULT (datetime('now'))); CREATE UNIQUE INDEX IF NOT EXISTS idx_cultroll_watchlist_unique ON cultroll_user_watchlist(user_id, title_id); CREATE INDEX IF NOT EXISTS idx_cultroll_watchlist_user ON cultroll_user_watchlist(user_id, added_at); CREATE INDEX IF NOT EXISTS idx_cultroll_watchlist_title ON cultroll_user_watchlist(title_id, added_at);"

      - name: D1 migration — cultroll title reactions
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS cultroll_user_title_reactions (id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES cultroll_users(id) ON DELETE CASCADE, title_id TEXT NOT NULL REFERENCES titles(id) ON DELETE CASCADE, reaction TEXT NOT NULL CHECK(reaction IN ('like', 'dislike')), created_at TEXT NOT NULL DEFAULT (datetime('now')), updated_at TEXT NOT NULL DEFAULT (datetime('now'))); CREATE UNIQUE INDEX IF NOT EXISTS idx_cultroll_title_reactions_unique ON cultroll_user_title_reactions(user_id, title_id); CREATE INDEX IF NOT EXISTS idx_cultroll_title_reactions_user ON cultroll_user_title_reactions(user_id, updated_at); CREATE INDEX IF NOT EXISTS idx_cultroll_title_reactions_title ON cultroll_user_title_reactions(title_id, updated_at);"

      - name: D1 migration — cultroll experiment events
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --remote --command "CREATE TABLE IF NOT EXISTS cultroll_experiment_events (id INTEGER PRIMARY KEY AUTOINCREMENT, experiment_key TEXT NOT NULL, variant TEXT NOT NULL, event_name TEXT NOT NULL CHECK(event_name IN ('exposure', 'booking_click', 'watchlist_add', 'title_alert', 'sign_in')), title_id TEXT REFERENCES titles(id) ON DELETE CASCADE, user_id TEXT REFERENCES cultroll_users(id) ON DELETE SET NULL, session_key TEXT NOT NULL, source TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now'))); CREATE INDEX IF NOT EXISTS idx_cultroll_experiment_events_lookup ON cultroll_experiment_events(experiment_key, variant, event_name, created_at); CREATE INDEX IF NOT EXISTS idx_cultroll_experiment_events_title ON cultroll_experiment_events(experiment_key, title_id, created_at); CREATE INDEX IF NOT EXISTS idx_cultroll_experiment_events_actor ON cultroll_experiment_events(experiment_key, session_key, title_id, event_name);"

      - name: Seed D1 database
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: d1 execute cultroll-db --file=src/sites/cultroll/lib/seed.sql --remote

      - name: Create R2 bucket
        continue-on-error: true
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: r2 bucket create cultroll-images
