import json
from datetime import date, timedelta
from google.oauth2 import service_account
from googleapiclient.discovery import build
import mysql.connector

# --- Config
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
KEY_PATH = '/var/www/html/fitfoodie-secrets/gsc-service-account.json'
SITE_URL = 'https://fitfoodiesa.com/'

# --- GSC Connection
credentials = service_account.Credentials.from_service_account_file(KEY_PATH, scopes=SCOPES)
service = build('searchconsole', 'v1', credentials=credentials)

# --- Fetch daily performance
def fetch_daily_data():
    request = {
        'startDate': (date.today() - timedelta(days=90)).isoformat(),
        'endDate': date.today().isoformat(),
        'dimensions': ['date'],
        'rowLimit': 1000
    }
    response = service.searchanalytics().query(siteUrl=SITE_URL, body=request).execute()
    return response.get('rows', [])

# --- Fetch all query data (paginated)
def fetch_all_query_data():
    all_rows = []
    start_row = 0
    page_size = 1000

    while True:
        request = {
            'startDate': (date.today() - timedelta(days=90)).isoformat(),
            'endDate': date.today().isoformat(),
            'dimensions': ['query'],
            'rowLimit': page_size,
            'startRow': start_row
        }
        response = service.searchanalytics().query(siteUrl=SITE_URL, body=request).execute()
        rows = response.get('rows', [])
        if not rows:
            break
        all_rows.extend(rows)
        start_row += page_size

    return all_rows

# --- Main insert logic
if __name__ == "__main__":
    print("Storing daily performance...")
    date_rows = fetch_daily_data()

    print("Storing query performance...")
    query_rows = fetch_all_query_data()

    db = mysql.connector.connect(
        host="localhost", user="fitfoodie", password="FitFoodie25!", database="fitfoodiedb"
    )
    cursor = db.cursor()

    # Insert date-based stats
    for r in date_rows:
        record = {
            'date': r['keys'][0],
            'clicks': r['clicks'],
            'impressions': r['impressions'],
            'ctr': round(r['ctr'] * 100, 2),
            'position': round(r['position'], 2)
        }
        sql = """
        INSERT INTO gsc_stats (date, clicks, impressions, ctr, position)
        VALUES (%s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE clicks=VALUES(clicks),
                                impressions=VALUES(impressions),
                                ctr=VALUES(ctr),
                                position=VALUES(position)
        """
        cursor.execute(sql, (record['date'], record['clicks'], record['impressions'], record['ctr'], record['position']))

    # Insert query-level stats
    for r in query_rows:
        query = r['keys'][0][:400]  # Truncate to 400 chars max if needed
        sql = """
        INSERT INTO gsc_queries (query, clicks, impressions, ctr, position)
        VALUES (%s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE clicks=VALUES(clicks),
                                impressions=VALUES(impressions),
                                ctr=VALUES(ctr),
                                position=VALUES(position)
        """
        cursor.execute(sql, (
            query, r['clicks'], r['impressions'],
            round(r['ctr'] * 100, 2), round(r['position'], 2)
        ))

    db.commit()
    cursor.close()
    db.close()
    print("Done ✅")