"""
Google Sheets sync via gspread + google-auth.

Writes to three tabs in the finance spreadsheet:
  "Raw Pricing Data"  — latest plan snapshot per competitor (overwritten each run)
  "Changes Log"       — append-only log of detected changes
  "AI Summaries"      — append-only Gemini summaries per competitor per run
"""

import json
import os

import gspread
from google.oauth2.service_account import Credentials

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

RAW_SHEET = "Raw Pricing Data"
CHANGES_SHEET = "Changes Log"
SUMMARY_SHEET = "AI Summaries"

RAW_HEADERS = [
    "Competitor", "Plan", "Monthly Price", "Annual Price",
    "Key Features", "Limits", "Last Updated",
]
CHANGES_HEADERS = [
    "Date", "Competitor", "Plan", "Change Type", "Field", "Old Value", "New Value",
]
SUMMARY_HEADERS = ["Date", "Competitor", "Summary", "Has Changes"]


def _build_client() -> gspread.Client:
    """
    Builds a gspread client from service account credentials.

    Looks for credentials in this order:
      1. GOOGLE_SERVICE_ACCOUNT_JSON_CONTENT — full JSON string (used in GitHub Actions)
      2. GOOGLE_SERVICE_ACCOUNT_JSON         — path to a JSON key file (used locally)
    """
    content = os.environ.get("GOOGLE_SERVICE_ACCOUNT_JSON_CONTENT")
    path = os.environ.get("GOOGLE_SERVICE_ACCOUNT_JSON")

    if content:
        info = json.loads(content)
        creds = Credentials.from_service_account_info(info, scopes=SCOPES)
    elif path and os.path.exists(path):
        creds = Credentials.from_service_account_file(path, scopes=SCOPES)
    else:
        raise EnvironmentError(
            "No Google credentials found. Set either:\n"
            "  GOOGLE_SERVICE_ACCOUNT_JSON_CONTENT  (JSON string, for CI)\n"
            "  GOOGLE_SERVICE_ACCOUNT_JSON          (file path, for local dev)"
        )

    return gspread.authorize(creds)


class SheetSync:
    def __init__(self, spreadsheet_id: str):
        client = _build_client()
        self.ss = client.open_by_key(spreadsheet_id)
        self._ensure_tabs()

    # ------------------------------------------------------------------
    # Internal helpers
    # ------------------------------------------------------------------

    def _ensure_tabs(self):
        """Create the three required tabs with header rows if they don't exist."""
        existing = {ws.title for ws in self.ss.worksheets()}
        for title, headers in [
            (RAW_SHEET, RAW_HEADERS),
            (CHANGES_SHEET, CHANGES_HEADERS),
            (SUMMARY_SHEET, SUMMARY_HEADERS),
        ]:
            if title not in existing:
                ws = self.ss.add_worksheet(
                    title=title, rows=2000, cols=len(headers)
                )
                ws.append_row(headers, value_input_option="RAW")
                print(f"  [Sheets] Created tab: {title}")

    def _tab(self, title: str) -> gspread.Worksheet:
        return self.ss.worksheet(title)

    # ------------------------------------------------------------------
    # Public write methods
    # ------------------------------------------------------------------

    def write_raw_pricing(self, competitor: str, plans: list[dict], date: str):
        """
        Replace all rows for `competitor` in Raw Pricing Data with the latest plans.
        Rows for other competitors are preserved.
        """
        if not plans:
            return

        ws = self._tab(RAW_SHEET)
        all_values = ws.get_all_values()

        # Keep header + rows that belong to other competitors
        kept = [all_values[0]] if all_values else [RAW_HEADERS]
        for row in all_values[1:]:
            if row and row[0] != competitor:
                kept.append(row)

        # Build new rows for this competitor
        new_rows = []
        for plan in plans:
            features = " | ".join(plan.get("key_features", []))
            new_rows.append([
                competitor,
                plan.get("plan_name", ""),
                plan.get("monthly_price", ""),
                plan.get("annual_price", ""),
                features,
                plan.get("limits", ""),
                date,
            ])

        ws.clear()
        ws.update(kept + new_rows, value_input_option="RAW")
        print(f"  [Sheets] Raw Pricing Data: wrote {len(new_rows)} rows for {competitor}")

    def append_changes_log(self, competitor: str, changes: list[dict], date: str):
        """Append one row per detected change to the Changes Log tab."""
        if not changes:
            return

        ws = self._tab(CHANGES_SHEET)
        rows = [
            [
                date,
                competitor,
                change.get("plan", ""),
                change.get("type", ""),
                change.get("field", ""),
                str(change.get("old", "")),
                str(change.get("new", "")),
            ]
            for change in changes
        ]
        ws.append_rows(rows, value_input_option="RAW")
        print(f"  [Sheets] Changes Log: appended {len(rows)} change(s) for {competitor}")

    def append_ai_summary(
        self, competitor: str, summary: str, has_changes: bool, date: str
    ):
        """Append one summary row per competitor per run to AI Summaries."""
        ws = self._tab(SUMMARY_SHEET)
        ws.append_row(
            [date, competitor, summary, "Yes" if has_changes else "No"],
            value_input_option="RAW",
        )
        print(f"  [Sheets] AI Summaries: wrote summary for {competitor}")