#!/usr/bin/env python3
"""
FLHIP Gov Leads Archive Helper
-------------------------------
Called at the START of each weekly scraper run (before new data comes in).

Process:
  1. Copy all current rows from restaurant_leads_gov_usa → restaurant_leads_gov_usa_archive
  2. Truncate restaurant_leads_gov_usa (clear for fresh weekly data)

This gives you a full weekly snapshot in the archive table.
Each archived row gets archived_at = timestamp of when it was archived.

Usage (standalone):
    python3 gov_leads_archive.py

Called automatically by flhip_gov_leads.py at start of run.
"""

import os, sys, mysql.connector
from datetime import datetime
from dotenv import load_dotenv
from pathlib import Path

load_dotenv(Path(__file__).resolve().parent.parent / '.env')

DB_CONFIG = {
    "host":     os.getenv("DB_HOST", "localhost"),
    "user":     os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME", "restaurant_openings"),
    "charset":  "utf8mb4",
}


def archive_gov_leads(conn, cursor):
    """
    Archive current gov leads then clear the live table for fresh weekly data.
    Returns count of rows archived.
    """
    print("\nArchiving gov leads...")

    # Count what we're about to archive
    cursor.execute("SELECT COUNT(*) FROM restaurant_leads_gov_usa")
    count = cursor.fetchone()[0]
    print(f"  Rows in live table: {count}")

    if count == 0:
        print("  Nothing to archive — live table is empty")
        return 0

    # Copy all rows into archive, adding archived_at timestamp
    # Use INSERT INTO ... SELECT to copy all matching columns
    cursor.execute("""
        INSERT INTO restaurant_leads_gov_usa_archive
            (county, region, business, address, city, state, source_state, zip,
             lat, lng, phone, contact_name, contact_email,
             info_source, internet_info,
             notes, gov_status, plan_review_type, facility_type,
             license_number, application_number,
             created_at, article_date, review_date, last_updated, sent_to_wp,
             archived_at)
        SELECT
             county, region, business, address, city, state, source_state, zip,
             lat, lng, phone, contact_name, contact_email,
             info_source, internet_info,
             notes, gov_status, plan_review_type, facility_type,
             license_number, application_number,
             created_at, article_date, review_date, last_updated, sent_to_wp,
             NOW()
        FROM restaurant_leads_gov_usa
    """)
    archived = cursor.rowcount
    conn.commit()
    print(f"  Archived {archived} rows to restaurant_leads_gov_usa_archive")

    # Clear the live table for fresh weekly data
    cursor.execute("TRUNCATE TABLE restaurant_leads_gov_usa")
    conn.commit()
    print(f"  Live table cleared — ready for new weekly data")

    return archived


if __name__ == "__main__":
    print("=" * 60)
    print("FLHIP Gov Leads Archive")
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    print("=" * 60)

    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        print(f"DB: connected as {DB_CONFIG['user']}@{DB_CONFIG['host']}")
    except mysql.connector.Error as e:
        print(f"DB ERROR: {e}"); sys.exit(1)

    archived = archive_gov_leads(conn, cursor)

    cursor.close()
    conn.close()
    print(f"\nDone. {archived} rows archived.\n")