database_helper.py

import requests
from datetime import datetime, timezone
import os
from bs4 import BeautifulSoup as bs
import json
from supabase import create_client
import logging
from fake_useragent import UserAgent
from dotenv import load_dotenv
from playwright.sync_api import sync_playwright

# Load environment variables from .env file
load_dotenv()

# Configure logging
logging.basicConfig(
    level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s"
)


class DBHelper:
    def __init__(self):
        # Ensure environment variables are set
        self.ua = UserAgent(platforms="mobile")
        self.url = os.environ.get("SUPABASE_URL")
        self.key = os.environ.get("SUPABASE_KEY")

        if not self.url or not self.key:
            raise ValueError("SUPABASE_URL and SUPABASE_KEY must be set")

        self.supabase = create_client(self.url, self.key)

    def fetch_and_insert_codeforces_contests(self):
        try:
            # Fetch contests from Codeforces API
            r = requests.get(
                "https://codeforces.com/api/contest.list?gym=false",
                headers={"User-Agent": self.ua.random},
            )
            response = r.json()

            # Check if the response is valid
            if response["status"] != "OK":
                raise ValueError("Failed to fetch contests from Codeforces")

            all_contests = response["result"]
            upcoming_contests = []

            # Process contests
            for contest in all_contests:
                if contest["phase"] == "BEFORE":
                    duration_hours = contest["durationSeconds"] // 3600
                    duration_minutes = (contest["durationSeconds"] % 3600) // 60
                    duration_seconds = contest["durationSeconds"] % 60

                    duration_str = f"{duration_hours}h"
                    if duration_minutes > 0:
                        duration_str += f" {duration_minutes}m"
                    if duration_seconds > 0:
                        duration_str += f" {duration_seconds}s"

                    my_contest = {
                        "contest_provider": "Codeforces",
                        "contest_id": contest["id"],
                        "contest_name": contest["name"],
                        "contest_start": contest[
                            "startTimeSeconds"
                        ],  # Store as Unix timestamp
                        "url": f"https://codeforces.com/contests/{contest['id']}",
                        "notified": False,
                        "duration": duration_str,
                    }
                    upcoming_contests.append(my_contest)
                else:
                    break

            # Insert contests into the database if they don't already exist
            new_contests = []
            existing_contests = (
                self.supabase.table("contests")
                .select("url")
                .eq("contest_provider", "Codeforces")
                .execute()
            )
            existing_urls = set(contest["url"] for contest in existing_contests.data)

            for contest in upcoming_contests:
                if contest["url"] not in existing_urls:
                    new_contests.append(contest)

            if new_contests:
                self.supabase.table("contests").insert(new_contests).execute()
                logging.info(f"Inserted {len(new_contests)} new Codeforces contests.")
            else:
                logging.info("No new Codeforces contests to insert.")

        except Exception as e:
            logging.error(f"Error fetching or inserting Codeforces contests: {e}")

    def fetch_and_insert_leetcode_contests(self):
        try:
            logging.info("Fetching LeetCode contests.")

            with sync_playwright() as p:
                browser = p.chromium.launch(headless=True)
                context = browser.new_context(
                    user_agent=self.ua.random,
                    viewport={"width": 1920, "height": 1080},
                    device_scale_factor=1,
                )
                page = context.new_page()

                # Navigate to the contests page
                page.goto("https://leetcode.com/contest/")

                # Wait for the script to load with an increased timeout
                try:
                    element = page.wait_for_selector(
                        "script#__NEXT_DATA__", state="attached", timeout=60000
                    )
                except Exception:
                    logging.error(
                        "Timeout waiting for __NEXT_DATA__ script. The page might not have loaded correctly."
                    )
                    return

                # Extract the JSON data
                script_content = element.inner_text()
                data = json.loads(script_content)

                contests = data["props"]["pageProps"]["dehydratedState"]["queries"][4][
                    "state"
                ]["data"]["topTwoContests"]

                browser.close()

            new_contests = []
            existing_contests = (
                self.supabase.table("contests")
                .select("url")
                .eq("contest_provider", "LeetCode")
                .execute()
            )
            existing_urls = set(contest["url"] for contest in existing_contests.data)

            for contest in contests:
                duration_hours = contest["duration"] // 3600
                duration_minutes = (contest["duration"] % 3600) // 60
                duration_seconds = contest["duration"] % 60

                duration_str = f"{duration_hours}h"
                if duration_minutes > 0:
                    duration_str += f" {duration_minutes}m"
                if duration_seconds > 0:
                    duration_str += f" {duration_seconds}s"

                temp_contest = {
                    "contest_provider": "LeetCode",
                    "contest_id": contest["titleSlug"],
                    "contest_name": contest["title"],
                    "contest_start": contest["startTime"],  # Store as Unix timestamp
                    "url": f"https://leetcode.com/contest/{contest['titleSlug']}",
                    "notified": False,
                    "duration": duration_str,
                }

                if temp_contest["url"] not in existing_urls:
                    new_contests.append(temp_contest)

            if new_contests:
                self.supabase.table("contests").insert(new_contests).execute()
                logging.info(f"Inserted {len(new_contests)} new LeetCode contests.")
            else:
                logging.info("No new LeetCode contests to insert.")

        except Exception as e:
            logging.error(f"Error fetching or inserting LeetCode contests: {e}")

    def fetch_and_insert_atcoder_contests(self):
        try:
            # Fetch contests from AtCoder website
            url = "https://atcoder.jp/contests/"
            response = requests.get(url, headers={"User-Agent": self.ua.random})
            soup = bs(response.text, "lxml")

            upcoming_contests = soup.find("div", id="contest-table-upcoming")
            table = upcoming_contests.find("table")
            rows = table.find_all("tr")[1:]

            contests = []

            for row in rows:
                cols = row.find_all("td")

                start_time = cols[0].find("time").text
                start_time = datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S%z")
                utc_start_time = start_time.astimezone(timezone.utc).timestamp()

                duration_parts = cols[2].text.strip().split(":")
                duration_hours = int(duration_parts[0])
                duration_minutes = (
                    int(duration_parts[1]) if len(duration_parts) > 1 else 0
                )
                duration_seconds = (
                    int(duration_parts[2]) if len(duration_parts) > 2 else 0
                )

                duration_str = f"{duration_hours}h"
                if duration_minutes > 0:
                    duration_str += f" {duration_minutes}m"
                if duration_seconds > 0:
                    duration_str += f" {duration_seconds}s"

                contest = {
                    "contest_provider": "AtCoder",
                    "contest_id": cols[1].find("a")["href"].split("/")[-1],
                    "contest_name": cols[1].find("a").text.strip(),
                    "contest_start": int(utc_start_time),  # Store as Unix timestamp
                    "url": "https://atcoder.jp" + cols[1].find("a")["href"],
                    "notified": False,
                    "duration": duration_str,
                }
                contests.append(contest)

            # Insert contests into the database if they don't already exist
            new_contests = []
            existing_contests = (
                self.supabase.table("contests")
                .select("url")
                .eq("contest_provider", "AtCoder")
                .execute()
            )
            existing_urls = set(contest["url"] for contest in existing_contests.data)

            for contest in contests:
                if contest["url"] not in existing_urls:
                    new_contests.append(contest)

            if new_contests:
                self.supabase.table("contests").insert(new_contests).execute()
                logging.info(f"Inserted {len(new_contests)} new AtCoder contests.")
            else:
                logging.info("No new AtCoder contests to insert.")

        except Exception as e:
            logging.error(f"Error fetching or inserting AtCoder contests: {e}")

    def fetch_unnotified_contests(self):
        try:
            logging.info("Fetching list of unnotified contests from the database.")
            contests = (
                self.supabase.table("contests")
                .select("*")
                .eq("notified", False)
                .execute()
            )
            logging.info(f"Fetched {len(contests.data)} unnotified contests.")
            return contests.data
        except Exception as e:
            logging.error(f"Error fetching contests: {e}")
            return []

    def update_contest(self, contest_url, notified):
        try:
            logging.info(
                f"Updating contest with URL {contest_url}. Setting notified to {notified}."
            )
            result = (
                self.supabase.table("contests")
                .update({"notified": notified})
                .eq("url", contest_url)
                .execute()
            )
            if result.data:
                logging.info(f"Contest with URL {contest_url} updated successfully.")
            else:
                logging.warning(
                    f"No contest found with URL {contest_url}. Update may have failed."
                )
        except Exception as e:
            logging.error(f"Error updating contest with URL {contest_url}: {e}")
        finally:
            # Verify the update
            verification = (
                self.supabase.table("contests")
                .select("notified")
                .eq("url", contest_url)
                .execute()
            )
            if verification.data and verification.data[0]["notified"] == notified:
                logging.info(
                    f"Verified: Contest with URL {contest_url} has been updated correctly."
                )
            else:
                logging.error(
                    f"Update verification failed for contest with URL {contest_url}."
                )

    def insert_notified_contest(self, contest_url, num_notified_users):
        try:
            logging.info(f"Inserting notified contest with URL {contest_url}.")
            result = (
                self.supabase.table("notified_contests")
                .insert({"url": contest_url, "num_notified_users": num_notified_users})
                .execute()
            )
            if result.data:
                logging.info(
                    f"Notified contest with URL {contest_url} inserted successfully."
                )
            else:
                logging.warning(
                    f"Failed to insert notified contest with URL {contest_url}."
                )
        except Exception as e:
            logging.error(
                f"Error inserting notified contest with URL {contest_url}: {e}"
            )

    def get_users_by_provider(self, contest_provider):
        try:
            logging.info(
                f"Fetching users subscribed to {contest_provider} contests from the database."
            )
            users = (
                self.supabase.table("users")
                .select("*")
                .eq(contest_provider.lower(), True)
                .execute()
            )
            logging.info(
                f"Fetched {len(users.data)} users subscribed to {contest_provider} contests."
            )
            return users.data
        except Exception as e:
            logging.error(f"Error fetching users for {contest_provider} contests: {e}")
            return []

    def get_user_details(self, user_id):
        try:
            response = self.supabase.auth.admin.get_user_by_id(user_id)
            if response.user:
                return response.user
            else:
                logging.error(f"Error: User with ID {user_id} not found.")
                return None
        except Exception as e:
            logging.error(f"Error fetching user details for ID {user_id}: {e}")
            return None

    def get_contest_by_url(self, contest_url):
        try:
            contest = (
                self.supabase.table("contests")
                .select("*")
                .eq("url", contest_url)
                .execute()
            )
            if contest.data:
                return contest.data[0]
            else:
                logging.error(f"Error: Contest with URL {contest_url} not found.")
                return None
        except Exception as e:
            logging.error(f"Error fetching contest with URL {contest_url}: {e}")
            return None