import requests
import pandas as pd
import sqlite3
from datetime import datetime, timedelta

# Extract
yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')

url = "https://api.github.com/search/repositories"
params = {
    "q": f"language:python created:>{yesterday}",
    "sort": "stars",
    "order": "desc",
    "per_page": 30
}

response = requests.get(url, params=params)
data = response.json()

# Transform
repos = []
for repo in data['items']:
    repos.append({
        "name": repo['name'],
        "owner": repo['owner']['login'],
        "stars": repo['stargazers_count'],
        "forks": repo['forks_count'],
        "language": repo['language'],
        "description": repo['description'],
        "url": repo['html_url'],
        "created_at": repo['created_at']
    })

df = pd.DataFrame(repos)
df_clean = df.dropna(subset=['description'])
df_clean = df_clean.copy()
df_clean['viral'] = df_clean['stars'].apply(lambda x: 'Yes' if x > 50000 else 'No')
df_clean = df_clean.sort_values('stars', ascending=False).reset_index(drop=True)

# Load
conn = sqlite3.connect('/content/drive/MyDrive/github_repos.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS repos (
        name TEXT, owner TEXT, stars INTEGER, forks INTEGER,
        language TEXT, description TEXT, url TEXT,
        created_at TEXT, viral TEXT, loaded_at TEXT
    )
''')

df_clean['loaded_at'] = datetime.now().strftime('%Y-%m-%d')
df_clean.to_sql('repos_temp', conn, if_exists='replace', index=False)

cursor.execute('''
    DELETE FROM repos WHERE url IN (SELECT url FROM repos_temp)
''')
cursor.execute('''
    INSERT INTO repos SELECT * FROM repos_temp
''')

conn.commit()
conn.close()
print("Pipeline complete. Duplicates handled.")