#!/usr/bin/env python3
# Google Places bulk scraper for Python 3.6+
# Commands:
#   python3 scraper.py import --csv file.csv
#   python3 scraper.py run --limit 20 --qps 1 --no-socials
#   python3 scraper.py export --out results.csv

import argparse
import csv
import json
import os
import re
import sqlite3
import sys
import time
from urllib.parse import urljoin

try:
    import requests
except ImportError:
    print("Missing dependency: requests. Install with: python3 -m pip install --user requests python-dotenv")
    sys.exit(1)

try:
    from dotenv import load_dotenv
    load_dotenv()
except Exception:
    pass

DEFAULT_DB = os.getenv("DB_PATH", "places_bulk.sqlite3")
API_KEY = os.getenv("GOOGLE_PLACES_API_KEY", "") or os.getenv("GOOGLE_API_KEY", "")
DEFAULT_INPUT_CSV = os.getenv("INPUT_CSV", "2026-05-29_-_Worker_and_Temporary_Worker.csv")
DEFAULT_OUTPUT_CSV = os.getenv("OUTPUT_CSV", "results.csv")
SEARCH_URL = "https://places.googleapis.com/v1/places:searchText"
DETAILS_URL = "https://places.googleapis.com/v1/places/{place_id}"

SEARCH_FIELD_MASK = "places.id,places.displayName,places.formattedAddress,places.location,places.businessStatus,places.primaryType,places.types,places.googleMapsUri"
DETAILS_FIELD_MASK = "id,displayName,formattedAddress,addressComponents,location,nationalPhoneNumber,internationalPhoneNumber,websiteUri,googleMapsUri,businessStatus,rating,userRatingCount,primaryType,types"
SOCIAL_RE = re.compile(r'https?://[^\s\"\'<>]+(?:facebook\.com|instagram\.com|linkedin\.com|youtube\.com|youtu\.be|twitter\.com|x\.com|tiktok\.com)[^\s\"\'<>]*', re.I)


def init_db(conn):
    conn.execute("""
    CREATE TABLE IF NOT EXISTS companies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        organisation_name TEXT NOT NULL,
        town_city TEXT,
        county TEXT,
        type_rating TEXT,
        route TEXT,
        source_row_json TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(organisation_name, town_city, county)
    )
    """)
    conn.execute("""
    CREATE TABLE IF NOT EXISTS places_results (
        company_id INTEGER PRIMARY KEY,
        input_company TEXT,
        input_city TEXT,
        input_county TEXT,
        status TEXT DEFAULT 'pending',
        error TEXT,
        place_id TEXT,
        found_name TEXT,
        formatted_address TEXT,
        national_phone TEXT,
        international_phone TEXT,
        website TEXT,
        google_maps_url TEXT,
        rating TEXT,
        user_rating_count TEXT,
        business_status TEXT,
        primary_type TEXT,
        types TEXT,
        latitude TEXT,
        longitude TEXT,
        facebook TEXT,
        instagram TEXT,
        linkedin TEXT,
        youtube TEXT,
        twitter_x TEXT,
        tiktok TEXT,
        raw_json TEXT,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_places_results_status ON places_results(status)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_companies_name ON companies(organisation_name)")
    conn.commit()


def open_db(path):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    init_db(conn)
    return conn


def pick(row, names):
    for n in names:
        if n in row and row[n] is not None:
            return str(row[n]).strip()
    lower = {k.lower().strip(): k for k in row.keys()}
    for n in names:
        key = lower.get(n.lower().strip())
        if key:
            return str(row[key]).strip()
    return ""


def import_csv(csv_path, db_path):
    conn = open_db(db_path)
    inserted = 0
    kept = 0
    skipped = 0
    with open(csv_path, "r", encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        for row in reader:
            name = pick(row, ["Organisation Name", "Organization Name", "company_name", "Company", "Name"])
            if not name:
                skipped += 1
                continue
            city = pick(row, ["Town/City", "Town", "City", "town_city"])
            county = pick(row, ["County", "county"])
            type_rating = pick(row, ["Type & Rating", "Type and Rating", "type_rating"])
            route = pick(row, ["Route", "route"])
            try:
                conn.execute("""
                    INSERT INTO companies (organisation_name, town_city, county, type_rating, route, source_row_json)
                    VALUES (?, ?, ?, ?, ?, ?)
                """, (name, city, county, type_rating, route, json.dumps(row, ensure_ascii=False)))
                inserted += 1
            except sqlite3.IntegrityError:
                kept += 1
    conn.commit()
    total = conn.execute("SELECT COUNT(*) FROM companies").fetchone()[0]
    print("Import complete. Inserted: %s. Already kept: %s. Skipped blank rows: %s. Total unique: %s." % (inserted, kept, skipped, total))


def api_headers(mask):
    return {"Content-Type": "application/json", "X-Goog-Api-Key": API_KEY, "X-Goog-FieldMask": mask}


def search_place(name, city, county):
    text = " ".join([x for x in [name, city, county, "United Kingdom"] if x])
    payload = {"textQuery": text, "regionCode": "GB", "languageCode": "en"}
    r = requests.post(SEARCH_URL, headers=api_headers(SEARCH_FIELD_MASK), json=payload, timeout=30)
    if r.status_code >= 400:
        raise Exception("Search API %s: %s" % (r.status_code, r.text[:500]))
    data = r.json()
    places = data.get("places") or []
    if not places:
        return None
    return places[0]


def details_place(place_id):
    r = requests.get(DETAILS_URL.format(place_id=place_id), headers=api_headers(DETAILS_FIELD_MASK), timeout=30)
    if r.status_code >= 400:
        raise Exception("Details API %s: %s" % (r.status_code, r.text[:500]))
    return r.json()


def textval(v):
    if isinstance(v, dict):
        return v.get("text", "") or v.get("name", "") or ""
    if v is None:
        return ""
    return str(v)


def crawl_socials(website):
    out = {"facebook":"", "instagram":"", "linkedin":"", "youtube":"", "twitter_x":"", "tiktok":""}
    if not website:
        return out
    try:
        resp = requests.get(website, timeout=12, headers={"User-Agent":"Mozilla/5.0"}, allow_redirects=True)
        html = resp.text[:700000]
        links = list(dict.fromkeys([x.rstrip('/).,;') for x in SOCIAL_RE.findall(html)]))
        for link in links:
            l = link.lower()
            if "facebook.com" in l and not out["facebook"]:
                out["facebook"] = link
            elif "instagram.com" in l and not out["instagram"]:
                out["instagram"] = link
            elif "linkedin.com" in l and not out["linkedin"]:
                out["linkedin"] = link
            elif ("youtube.com" in l or "youtu.be" in l) and not out["youtube"]:
                out["youtube"] = link
            elif ("twitter.com" in l or "x.com" in l) and not out["twitter_x"]:
                out["twitter_x"] = link
            elif "tiktok.com" in l and not out["tiktok"]:
                out["tiktok"] = link
    except Exception:
        pass
    return out


def save_result(conn, company, data, status, error=""):
    loc = data.get("location") or {}
    socials = data.get("socials") or {}
    vals = (
        company["id"], company["organisation_name"], company["town_city"], company["county"], status, error,
        data.get("id", ""), textval(data.get("displayName")), data.get("formattedAddress", ""),
        data.get("nationalPhoneNumber", ""), data.get("internationalPhoneNumber", ""), data.get("websiteUri", ""),
        data.get("googleMapsUri", ""), str(data.get("rating", "")), str(data.get("userRatingCount", "")),
        data.get("businessStatus", ""), data.get("primaryType", ""), ", ".join(data.get("types") or []),
        str(loc.get("latitude", "")), str(loc.get("longitude", "")),
        socials.get("facebook", ""), socials.get("instagram", ""), socials.get("linkedin", ""), socials.get("youtube", ""), socials.get("twitter_x", ""), socials.get("tiktok", ""),
        json.dumps(data, ensure_ascii=False),
    )
    conn.execute("""
    INSERT OR REPLACE INTO places_results (
      company_id,input_company,input_city,input_county,status,error,place_id,found_name,formatted_address,
      national_phone,international_phone,website,google_maps_url,rating,user_rating_count,business_status,
      primary_type,types,latitude,longitude,facebook,instagram,linkedin,youtube,twitter_x,tiktok,raw_json,updated_at
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)
    """, vals)
    conn.commit()


def process_one(conn, company, crawl):
    if not API_KEY:
        save_result(conn, company, {}, "error", "GOOGLE_PLACES_API_KEY is missing in .env")
        return "ERROR", "GOOGLE_PLACES_API_KEY is missing"
    try:
        found = search_place(company["organisation_name"], company["town_city"], company["county"])
        if not found:
            save_result(conn, company, {}, "not_found", "")
            return "NOT_FOUND", company["organisation_name"]
        place_id = found.get("id")
        detail = details_place(place_id) if place_id else found
        if crawl:
            detail["socials"] = crawl_socials(detail.get("websiteUri", ""))
        save_result(conn, company, detail, "found", "")
        return "FOUND", "%s | %s" % (company["organisation_name"], company["town_city"])
    except Exception as e:
        save_result(conn, company, {}, "error", str(e))
        return "ERROR", "%s | %s" % (company["organisation_name"], str(e))


def run(limit, qps, db_path, no_socials, retry_errors):
    conn = open_db(db_path)
    crawl = not no_socials
    if retry_errors:
        where = "r.company_id IS NULL OR r.status IN ('pending','error')"
    else:
        where = "r.company_id IS NULL OR r.status='pending'"
    sql = """
    SELECT c.* FROM companies c
    LEFT JOIN places_results r ON r.company_id = c.id
    WHERE %s
    ORDER BY c.id ASC
    """ % where
    if limit:
        sql += " LIMIT %d" % int(limit)
    rows = conn.execute(sql).fetchall()
    print("Processing %s rows | qps=%s | details=ON | crawl_socials=%s" % (len(rows), qps, "ON" if crawl else "OFF"))
    delay = 1.0 / float(qps) if float(qps) > 0 else 0
    for i, row in enumerate(rows, 1):
        status, msg = process_one(conn, row, crawl)
        print("[%s] %s: %s" % (i, status, msg))
        if delay:
            time.sleep(delay)
    print("Run complete. Processed this run: %s" % len(rows))


def export_csv(out_path, db_path):
    conn = open_db(db_path)
    rows = conn.execute("""
    SELECT c.id, c.organisation_name, c.town_city, c.county, c.type_rating, c.route,
           r.status, r.error, r.place_id, r.found_name, r.formatted_address,
           r.national_phone, r.international_phone, r.website, r.google_maps_url,
           r.rating, r.user_rating_count, r.business_status, r.primary_type, r.types,
           r.latitude, r.longitude, r.facebook, r.instagram, r.linkedin, r.youtube, r.twitter_x, r.tiktok
    FROM companies c
    LEFT JOIN places_results r ON r.company_id = c.id
    ORDER BY c.id ASC
    """).fetchall()
    headers = [d[0] for d in conn.execute("""
    SELECT c.id, c.organisation_name, c.town_city, c.county, c.type_rating, c.route,
           r.status, r.error, r.place_id, r.found_name, r.formatted_address,
           r.national_phone, r.international_phone, r.website, r.google_maps_url,
           r.rating, r.user_rating_count, r.business_status, r.primary_type, r.types,
           r.latitude, r.longitude, r.facebook, r.instagram, r.linkedin, r.youtube, r.twitter_x, r.tiktok
    FROM companies c LEFT JOIN places_results r ON r.company_id = c.id LIMIT 1
    """).description]
    with open(out_path, "w", encoding="utf-8", newline="") as f:
        writer = csv.writer(f)
        writer.writerow(headers)
        for r in rows:
            writer.writerow([r[h] for h in headers])
    print("Exported %s rows to %s" % (len(rows), out_path))


def stats(db_path):
    conn = open_db(db_path)
    total = conn.execute("SELECT COUNT(*) FROM companies").fetchone()[0]
    print("Companies:", total)
    for row in conn.execute("SELECT status, COUNT(*) n FROM places_results GROUP BY status"):
        print(row["status"], row["n"])


def main():
    p = argparse.ArgumentParser()
    p.add_argument("--db", default=DEFAULT_DB)
    sub = p.add_subparsers(dest="cmd")
    a = sub.add_parser("import")
    a.add_argument("--csv", default=DEFAULT_INPUT_CSV)
    r = sub.add_parser("run")
    r.add_argument("--limit", type=int, default=0)
    r.add_argument("--qps", type=float, default=1.0)
    r.add_argument("--no-socials", action="store_true")
    r.add_argument("--retry-errors", action="store_true")
    e = sub.add_parser("export")
    e.add_argument("--out", default=DEFAULT_OUTPUT_CSV)
    sub.add_parser("stats")

    args = p.parse_args()
    if args.cmd == "import":
        import_csv(args.csv, args.db)
    elif args.cmd == "run":
        run(args.limit, args.qps, args.db, args.no_socials, args.retry_errors)
    elif args.cmd == "export":
        export_csv(args.out, args.db)
    elif args.cmd == "stats":
        stats(args.db)
    else:
        p.print_help()

if __name__ == "__main__":
    main()