"""Writes People, Campaigns, GA4 Conversions, and Run Log tabs to Google Sheets."""

import os
from datetime import datetime, timezone
from typing import Optional
from zoneinfo import ZoneInfo

import gspread
from google.oauth2.service_account import Credentials

from models import CampaignRollup, GA4ConversionRow, Person

SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
]


def write_people(people: list[Person], sheets_config: dict, tz: ZoneInfo) -> None:
    sheet = _open_sheet()
    header = [
        "Email",
        "First touch",
        "First touch source",
        "Newsletter subscribed",
        "Days first-touch -> newsletter",
        "Stripe customer created",
        "Newspaper subscribed",
        "Newspaper subscription status",
        "Days first-touch -> newspaper",
        "FB lead at",
        "FB campaign name",
    ]
    rows = [header]
    for p in _sort_newest_first(people):
        rows.append(
            [
                p.email,
                _fmt_dt(p.first_touch_at, tz),
                p.first_touch_source,
                _fmt_dt(p.newsletter_subscribed_at, tz),
                _fmt_days(p.days_to_newsletter),
                _fmt_dt(p.stripe_customer_created_at, tz),
                _fmt_dt(p.newspaper_subscribed_at, tz),
                p.newspaper_subscription_status or "",
                _fmt_days(p.days_to_newspaper),
                _fmt_dt(p.facebook_lead_at, tz),
                p.facebook_campaign_name or "",
            ]
        )
    _replace_tab(sheet, sheets_config["people_tab"], rows)


def write_campaigns(
    campaigns: list[CampaignRollup], sheets_config: dict, tz: ZoneInfo
) -> None:
    sheet = _open_sheet()
    header = [
        "Campaign ID",
        "Campaign",
        "Spend (USD)",
        "Leads",
        "Newsletter subscribers",
        "Newspaper subscribers",
        "Cost per lead",
        "Cost per newsletter subscriber",
        "Cost per newspaper subscriber",
    ]
    rows = [header]
    for c in campaigns:
        rows.append(
            [
                c.campaign_id,
                c.campaign_name,
                _fmt_money(c.spend_usd),
                c.leads_count,
                c.newsletter_subscribers,
                c.newspaper_subscribers,
                _fmt_money(c.cost_per_lead),
                _fmt_money(c.cost_per_newsletter_subscriber),
                _fmt_money(c.cost_per_newspaper_subscriber),
            ]
        )
    _replace_tab(sheet, sheets_config["campaigns_tab"], rows)


def write_ga4_conversions(
    rows_in: list[GA4ConversionRow], sheets_config: dict, tz: ZoneInfo
) -> None:
    sheet = _open_sheet()
    header = [
        "Event name",
        "Source",
        "Medium",
        "Campaign",
        "Event count",
        "Conversions",
        "Sessions",
    ]
    rows = [header]
    for r in rows_in:
        rows.append(
            [
                r.event_name,
                r.source,
                r.medium,
                r.campaign,
                r.event_count,
                r.conversions,
                r.sessions,
            ]
        )
    _replace_tab(sheet, sheets_config["ga4_conversions_tab"], rows)


def write_run_log(
    started_at: datetime,
    people_count: int,
    campaigns_count: int,
    ga4_rows_count: int,
    sheets_config: dict,
    tz: ZoneInfo,
) -> None:
    """Append one audit row, preserving history. Always ensures the header is row 1."""
    sheet = _open_sheet()
    ws = _get_or_create_tab(sheet, sheets_config["log_tab"], rows=1000, cols=10)
    header = ["Run started", "People", "Campaigns", "GA4 rows"]

    existing = [
        row for row in ws.get_all_values() if any((c or "").strip() for c in row)
    ]
    if existing and existing[0] == header:
        data_rows = existing[1:]
    else:
        data_rows = existing

    new_row = [
        _fmt_dt(started_at, tz),
        people_count,
        campaigns_count,
        ga4_rows_count,
    ]
    final = [header] + data_rows + [new_row]

    ws.clear()
    ws.update(values=final, range_name="A1")


def _open_sheet():
    creds_path = os.environ.get("GOOGLE_SERVICE_ACCOUNT_JSON")
    sheet_id = os.environ.get("GOOGLE_SHEET_ID")
    if not creds_path or not os.path.exists(creds_path):
        raise RuntimeError(
            f"Service account JSON not found at {creds_path!r}. "
            "Set GOOGLE_SERVICE_ACCOUNT_JSON in .env."
        )
    if not sheet_id:
        raise RuntimeError("GOOGLE_SHEET_ID not set in .env.")
    creds = Credentials.from_service_account_file(creds_path, scopes=SCOPES)
    client = gspread.authorize(creds)
    return client.open_by_key(sheet_id)


def _get_or_create_tab(sheet, name: str, rows: int = 1000, cols: int = 26):
    try:
        return sheet.worksheet(name)
    except gspread.WorksheetNotFound:
        return sheet.add_worksheet(title=name, rows=rows, cols=cols)


def _replace_tab(sheet, name: str, rows: list[list]) -> None:
    """Clear the tab and replace its contents with `rows` (rows[0] is the header)."""
    needed_rows = max(len(rows) + 50, 1000)
    ws = _get_or_create_tab(sheet, name, rows=needed_rows)
    if ws.row_count < needed_rows:
        ws.add_rows(needed_rows - ws.row_count)
    ws.clear()
    if rows:
        ws.update(values=rows, range_name="A1")


def _sort_newest_first(people: list[Person]) -> list[Person]:
    with_touch = [p for p in people if p.first_touch_at is not None]
    without_touch = [p for p in people if p.first_touch_at is None]
    with_touch.sort(key=lambda p: p.first_touch_at, reverse=True)
    return with_touch + without_touch


def _fmt_dt(dt: Optional[datetime], tz: ZoneInfo) -> str:
    if dt is None:
        return ""
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(tz).strftime("%Y-%m-%d %H:%M:%S")


def _fmt_days(days: Optional[float]) -> str:
    return f"{days:.1f}" if days is not None else ""


def _fmt_money(amount: Optional[float]) -> str:
    return f"{amount:.2f}" if amount is not None else ""