update-remote-postgres.yml

name: Update Remote Postgres

on:
  schedule:
    - cron: '0 2 * * *'
  workflow_dispatch:

jobs:
  update_nuget_stats:
    runs-on: ubuntu-latest
    steps:
      - name: Install DB driver
        run: |
          python -m pip install --upgrade pip
          pip install psycopg2-binary requests

      - name: Run Nuget Update
        env:
          DB_URL: ${{ secrets.DB_URL }}
        run: |
          python - <<EOF
          import os
          import requests
          import psycopg2
          from datetime import date

          def parse_db_url(raw):
              """Accept PostgreSQL URI or .NET key=value;... connection string, return psycopg2 dsn."""
              raw = raw.strip()
              if raw.startswith("postgresql://") or raw.startswith("postgres://"):
                  return raw  # already a URI, psycopg2 handles it natively
              # .NET style: Host=...;Port=...;Database=...;Userfname=...;Password=...
              key_map = {
                  "host": "host", "server": "host",
                  "port": "port",
                  "database": "dbname", "initial catalog": "dbname",
                  "username": "user", "user id": "user", "uid": "user",
                  "password": "password", "pwd": "password",
                  "ssl mode": "sslmode", "sslmode": "sslmode",
              }
              parts = {}
              for pair in raw.split(";"):
                  pair = pair.strip()
                  if "=" not in pair:
                      continue
                  k, v = pair.split("=", 1)
                  mapped = key_map.get(k.strip().lower())
                  if mapped:
                      parts[mapped] = v.strip()
              return " ".join(f"{k}={v}" for k, v in parts.items())

          db_dsn = parse_db_url(os.getenv("DB_URL", ""))
          date_to_record = date.today().isoformat()

          # Packages to track: (nuget_package_id, db_package_name)
          packages = [
              ("Ivy", "Ivy"),
              ("Ivy.Tendril", "Ivy.Tendril"),
          ]

          results = {}
          for nuget_id, db_name in packages:
              try:
                  resp = requests.get(f"https://azuresearch-usnc.nuget.org/query?q=packageid:{nuget_id}").json()
                  dl = resp['data'][0]['totalDownloads']
                  results[db_name] = dl
                  print(f"Fetched {nuget_id}: {dl} downloads")
              except Exception as e:
                  print(f"Error fetching {nuget_id}: {e}")

          if not results:
              print("No download data fetched. Exiting.")
              exit(1)

          try:
              conn = psycopg2.connect(db_dsn)
              cur = conn.cursor()

              # Ensure table exists with package_name column
              cur.execute("""
                  CREATE TABLE IF NOT EXISTS nuget_history (
                      id SERIAL PRIMARY KEY,
                      package_name TEXT,
                      downloads BIGINT,
                      date DATE DEFAULT CURRENT_DATE,
                      UNIQUE(package_name, date)
                  );
              """)

              for db_name, downloads in results.items():
                  if downloads > 0:
                      cur.execute("""
                          INSERT INTO nuget_history (package_name, downloads, date)
                          VALUES (%s, %s, %s)
                          ON CONFLICT (package_name, date)
                          DO UPDATE SET downloads = EXCLUDED.downloads;
                      """, (db_name, downloads, date_to_record))
                      print(f"Success! {db_name}: {downloads} downloads added/updated in database.")
                  else:
                      print(f"Skipping {db_name}: 0 downloads returned.")

              conn.commit()
              cur.close()
              conn.close()
          except Exception as e:
              print(f"Database error: {e}")
              exit(1)
          EOF

  update_github_stats:
    runs-on: ubuntu-latest
    steps:
      - name: Install DB driver
        run: |
          python -m pip install --upgrade pip
          pip install psycopg2-binary requests

      - name: Run GitHub Update
        env:
          DB_URL: ${{ secrets.DB_URL }}
        run: |
          python - <<EOF
          import os
          import requests
          import psycopg2
          from datetime import date

          def parse_db_url(raw):
              raw = raw.strip()
              if raw.startswith("postgresql://") or raw.startswith("postgres://"):
                  return raw
              key_map = {
                  "host": "host", "server": "host",
                  "port": "port",
                  "database": "dbname", "initial catalog": "dbname",
                  "username": "user", "user id": "user", "uid": "user",
                  "password": "password", "pwd": "password",
                  "ssl mode": "sslmode", "sslmode": "sslmode",
              }
              parts = {}
              for pair in raw.split(";"):
                  pair = pair.strip()
                  if "=" not in pair:
                      continue
                  k, v = pair.split("=", 1)
                  mapped = key_map.get(k.strip().lower())
                  if mapped:
                      parts[mapped] = v.strip()
              return " ".join(f"{k}={v}" for k, v in parts.items())

          db_dsn = parse_db_url(os.getenv("DB_URL", ""))

          REPOS = [
              "Ivy-Interactive/Ivy-Framework",
              "Ivy-Interactive/Ivy-Tendril",
          ]
          headers = {'User-Agent': 'python-request-script'}
          date_to_record = date.today().isoformat()
          any_success = False

          try:
              conn = psycopg2.connect(db_dsn)
              cur = conn.cursor()

              cur.execute("""
                  CREATE TABLE IF NOT EXISTS github_stars_history (
                      id SERIAL PRIMARY KEY,
                      repo_name TEXT,
                      stars BIGINT,
                      date DATE DEFAULT CURRENT_DATE,
                      UNIQUE(repo_name, date)
                  );
              """)

              for repo in REPOS:
                  stars = 0
                  try:
                      repo_data = requests.get(f"https://api.github.com/repos/{repo}", headers=headers).json()
                      stars = repo_data.get('stargazers_count', 0)
                  except Exception as e:
                      print(f"Error fetching GitHub stats for {repo}: {e}")
                      continue

                  if stars > 0:
                      cur.execute("""
                          INSERT INTO github_stars_history (repo_name, stars, date)
                          VALUES (%s, %s, %s)
                          ON CONFLICT (repo_name, date)
                          DO UPDATE SET stars = EXCLUDED.stars;
                      """, (repo, stars, date_to_record))
                      print(f"Success! {repo}: {stars} stars added/updated in database.")
                      any_success = True
                  else:
                      print(f"No stars found for {repo} (fetch returned 0).")

              if any_success:
                  conn.commit()
              cur.close()
              conn.close()
          except Exception as e:
              print(f"Database error: {e}")
              exit(1)

          if not any_success:
              print("No GitHub star counts were updated for any repo.")
              exit(1)
          EOF

  update_github_stargazers:
    runs-on: ubuntu-latest
    needs: update_github_stats
    steps:
      - name: Install DB driver
        run: |
          python -m pip install --upgrade pip
          pip install psycopg2-binary requests

      - name: Run GitHub Stargazers Update
        env:
          DB_URL: ${{ secrets.DB_URL }}
        run: |
          python - <<EOF
          import os
          import requests
          import psycopg2
          from datetime import date, datetime, timezone

          def parse_db_url(raw):
              raw = raw.strip()
              if raw.startswith("postgresql://") or raw.startswith("postgres://"):
                  return raw
              key_map = {
                  "host": "host", "server": "host",
                  "port": "port",
                  "database": "dbname", "initial catalog": "dbname",
                  "username": "user", "user id": "user", "uid": "user",
                  "password": "password", "pwd": "password",
                  "ssl mode": "sslmode", "sslmode": "sslmode",
              }
              parts = {}
              for pair in raw.split(";"):
                  pair = pair.strip()
                  if "=" not in pair:
                      continue
                  k, v = pair.split("=", 1)
                  mapped = key_map.get(k.strip().lower())
                  if mapped:
                      parts[mapped] = v.strip()
              return " ".join(f"{k}={v}" for k, v in parts.items())

          db_dsn = parse_db_url(os.getenv("DB_URL", ""))

          REPOS = [
              "Ivy-Interactive/Ivy-Framework",
              "Ivy-Interactive/Ivy-Tendril",
          ]

          def fetch_all_stargazers(repo):
              """Fetch all stargazers with their starred_at timestamps."""
              result = []
              page = 1
              headers = {
                  "Accept": "application/vnd.github.star+json",
                  "User-Agent": "ivy-stars-tracker"
              }

              while True:
                  resp = requests.get(
                      f"https://api.github.com/repos/{repo}/stargazers",
                      headers=headers,
                      params={"per_page": 100, "page": page}
                  )
                  resp.raise_for_status()
                  data = resp.json()
                  if not data:
                      break

                  for item in data:
                      user = item.get("user") or {}
                      login = user.get("login")
                      starred_at = item.get("starred_at")
                      if login:
                          result.append((login, starred_at))

                  page += 1

              return result

          try:
              conn = psycopg2.connect(db_dsn)
              cur = conn.cursor()

              cur.execute("""
                  CREATE TABLE IF NOT EXISTS github_stargazers (
                      repo_name TEXT,
                      user_login TEXT,
                      starred_at TIMESTAMPTZ,
                      unstarred_at TIMESTAMPTZ,
                      PRIMARY KEY (repo_name, user_login)
                  );
                  ALTER TABLE github_stargazers ADD COLUMN IF NOT EXISTS starred_at TIMESTAMPTZ;
                  ALTER TABLE github_stargazers ADD COLUMN IF NOT EXISTS unstarred_at TIMESTAMPTZ;
              """)

              for REPO in REPOS:
                  try:
                      stargazers = fetch_all_stargazers(REPO)
                  except Exception as e:
                      print(f"Error fetching stargazers for {REPO}: {e}")
                      raise SystemExit(1)

                  print(f"Fetched {len(stargazers)} stargazers for {REPO}.")

                  if not stargazers:
                      print(f"No stargazers returned for {REPO}; skipping.")
                      continue

                  current = {login for login, _ in stargazers}
                  starred_at_map = {login: starred_at for login, starred_at in stargazers}

                  cur.execute("""
                      SELECT user_login FROM github_stargazers
                      WHERE repo_name = %s AND unstarred_at IS NULL;
                  """, (REPO,))
                  previous = {row[0] for row in cur.fetchall()}

                  new_users = current - previous
                  lost_users = previous - current

                  for login, starred_at in stargazers:
                      if not starred_at:
                          continue
                      cur.execute("""
                          UPDATE github_stargazers
                          SET starred_at = COALESCE(starred_at, %s)
                          WHERE repo_name = %s AND user_login = %s;
                      """, (starred_at, REPO, login))

                  for login in new_users:
                      starred_at = starred_at_map.get(login)
                      cur.execute("""
                          INSERT INTO github_stargazers (repo_name, user_login, starred_at, unstarred_at)
                          VALUES (%s, %s, %s, NULL)
                          ON CONFLICT (repo_name, user_login)
                          DO NOTHING;
                      """, (REPO, login, starred_at))

                  reactivate_count = 0
                  for login in current:
                      cur.execute("""
                          UPDATE github_stargazers SET unstarred_at = NULL
                          WHERE repo_name = %s AND user_login = %s AND unstarred_at IS NOT NULL;
                      """, (REPO, login))
                      if cur.rowcount > 0:
                          reactivate_count += 1

                  now = datetime.now(timezone.utc)
                  for login in lost_users:
                      cur.execute("""
                          UPDATE github_stargazers SET unstarred_at = %s
                          WHERE repo_name = %s AND user_login = %s AND unstarred_at IS NULL;
                      """, (now, REPO, login))

                  print(f"{REPO}: New: {len(new_users)}, Unstarred: {len(lost_users)}, Reactivated: {reactivate_count}. Total active: {len(current)}.")

                  if new_users:
                      print("New stargazers since last run:")
                      for login in sorted(new_users):
                          print(f"  + {login}")
                  else:
                      print("No new stargazers since last run.")

                  if lost_users:
                      print("Users who unstarred since last run:")
                      for login in sorted(lost_users):
                          print(f"  - {login}")
                  else:
                      print("No users unstarred since last run.")

                  if reactivate_count > 0:
                      print(f"Users who re-starred since last run: {reactivate_count}")

              conn.commit()
              cur.close()
              conn.close()
          except Exception as e:
              print(f"Database error while updating stargazers: {e}")
              raise SystemExit(1)
          EOF