from __future__ import annotations

import asyncio
from datetime import datetime, timezone
import logging
import re
from urllib.parse import quote

import httpx

from app.core.config import get_settings
from app.services.google_workspace_client import GOOGLE_DRIVE_ENDPOINT, GOOGLE_SHEETS_ENDPOINT
from app.services.google_workspace_session import get_active_google_workspace_connection
from app.services.tenant_store import SessionIdentity, get_tenant_store


logger = logging.getLogger(__name__)
_REPORTS_SHEET_LOCKS: dict[tuple[str, int], asyncio.Lock] = {}

GOOGLE_SPREADSHEET_MIME_TYPE = "application/vnd.google-apps.spreadsheet"
REPORTS_SHEET_TAB_NAME = "Segnalazioni"
REPORTS_SHEET_HEADERS = [
    "ID",
    "Data segnalazione",
    "Dipendente",
    "Email dipendente",
    "Categoria",
    "Priorita",
    "Stato",
    "Titolo",
    "Descrizione",
    "Luogo",
    "Nota admin",
    "Aggiornato da",
    "Data risoluzione",
    "Ultimo aggiornamento",
]

REPORT_CATEGORY_LABELS = {
    "problem": "Problema",
    "damage": "Danno",
    "breakage": "Rottura",
    "malfunction": "Malfunzionamento",
    "purchase_request": "Richiesta acquisto",
    "other": "Altro",
}

REPORT_PRIORITY_LABELS = {
    "low": "Bassa",
    "normal": "Normale",
    "high": "Alta",
    "urgent": "Urgente",
}

REPORT_STATUS_LABELS = {
    "new": "Nuova",
    "reviewed": "Visionata",
    "in_progress": "In lavorazione",
    "reported_to_owner": "Segnalata alla proprieta",
    "resolved": "Risolta",
}


def _escape_drive_query_value(value: str) -> str:
    return value.replace("\\", "\\\\").replace("'", "\\'")


def _sheet_range(sheet_title: str, range_ref: str) -> str:
    escaped_title = sheet_title.replace("'", "''")
    return f"'{escaped_title}'!{range_ref}"


def _column_name(index: int) -> str:
    value = index
    result = ""
    while value > 0:
        value, remainder = divmod(value - 1, 26)
        result = chr(65 + remainder) + result
    return result or "A"


def _report_sync_year(report: dict[str, object] | None) -> int:
    created_at = str((report or {}).get("created_at") or "")
    match = re.match(r"^(\d{4})", created_at)
    if match:
        return int(match.group(1))
    return datetime.now(timezone.utc).year


def _cell(value: object) -> str:
    if value is None:
        return ""
    return str(value)


def _reporter_label(report: dict[str, object]) -> str:
    for key in ("reporter_name", "reporter_username", "reporter_email"):
        value = str(report.get(key) or "").strip()
        if value:
            return value
    return "Dipendente"


def _rows_for_reports(reports: list[dict[str, object]]) -> list[list[str]]:
    rows: list[list[str]] = []
    for report in reports:
        rows.append(
            [
                _cell(report.get("id")),
                _cell(report.get("created_at")),
                _reporter_label(report),
                _cell(report.get("reporter_email")),
                REPORT_CATEGORY_LABELS.get(str(report.get("category") or ""), _cell(report.get("category"))),
                REPORT_PRIORITY_LABELS.get(str(report.get("priority") or ""), _cell(report.get("priority"))),
                REPORT_STATUS_LABELS.get(str(report.get("status") or ""), _cell(report.get("status"))),
                _cell(report.get("title")),
                _cell(report.get("description")),
                _cell(report.get("location")),
                _cell(report.get("admin_note")),
                _cell(report.get("status_updated_by_name")),
                _cell(report.get("resolved_at")),
                _cell(report.get("updated_at")),
            ]
        )
    return rows


async def _find_reports_spreadsheet(
    client: httpx.AsyncClient,
    *,
    access_token: str,
    title: str,
) -> str | None:
    response = await client.get(
        f"{GOOGLE_DRIVE_ENDPOINT}/files",
        headers={"Authorization": f"Bearer {access_token}"},
        params={
            "q": (
                "trashed = false "
                f"and mimeType = '{GOOGLE_SPREADSHEET_MIME_TYPE}' "
                f"and name = '{_escape_drive_query_value(title)}' "
                "and 'root' in parents"
            ),
            "pageSize": 1,
            "orderBy": "createdTime asc",
            "fields": "files(id, name, createdTime)",
            "supportsAllDrives": "true",
            "includeItemsFromAllDrives": "true",
        },
    )
    response.raise_for_status()
    files = response.json().get("files", [])
    if isinstance(files, list) and files:
        first = files[0]
        if isinstance(first, dict) and first.get("id"):
            return str(first["id"])
    return None


async def _create_reports_spreadsheet(
    client: httpx.AsyncClient,
    *,
    access_token: str,
    title: str,
) -> str:
    response = await client.post(
        f"{GOOGLE_SHEETS_ENDPOINT}/spreadsheets",
        headers={
            "Authorization": f"Bearer {access_token}",
            "Content-Type": "application/json",
        },
        json={
            "properties": {"title": title},
            "sheets": [{"properties": {"title": REPORTS_SHEET_TAB_NAME}}],
        },
    )
    response.raise_for_status()
    return str(response.json()["spreadsheetId"])


async def _resolve_reports_spreadsheet(
    client: httpx.AsyncClient,
    *,
    access_token: str,
    title: str,
) -> str:
    existing = await _find_reports_spreadsheet(client, access_token=access_token, title=title)
    if existing:
        return existing
    return await _create_reports_spreadsheet(client, access_token=access_token, title=title)


async def _first_sheet_properties(
    client: httpx.AsyncClient,
    *,
    access_token: str,
    spreadsheet_id: str,
) -> tuple[int, str]:
    response = await client.get(
        f"{GOOGLE_SHEETS_ENDPOINT}/spreadsheets/{quote(spreadsheet_id, safe='')}",
        headers={"Authorization": f"Bearer {access_token}"},
        params={"fields": "sheets(properties(sheetId,title))"},
    )
    response.raise_for_status()
    sheets = response.json().get("sheets", [])
    if not isinstance(sheets, list) or not sheets:
        raise ValueError("Google Sheet senza tab disponibili.")
    properties = sheets[0].get("properties", {}) if isinstance(sheets[0], dict) else {}
    return int(properties.get("sheetId") or 0), str(properties.get("title") or REPORTS_SHEET_TAB_NAME)


async def _format_reports_sheet(
    client: httpx.AsyncClient,
    *,
    access_token: str,
    spreadsheet_id: str,
    sheet_id: int,
) -> None:
    response = await client.post(
        f"{GOOGLE_SHEETS_ENDPOINT}/spreadsheets/{quote(spreadsheet_id, safe='')}:batchUpdate",
        headers={
            "Authorization": f"Bearer {access_token}",
            "Content-Type": "application/json",
        },
        json={
            "requests": [
                {
                    "updateSheetProperties": {
                        "properties": {"sheetId": sheet_id, "gridProperties": {"frozenRowCount": 1}},
                        "fields": "gridProperties.frozenRowCount",
                    }
                },
                {
                    "repeatCell": {
                        "range": {"sheetId": sheet_id, "startRowIndex": 0, "endRowIndex": 1},
                        "cell": {
                            "userEnteredFormat": {
                                "textFormat": {"bold": True},
                                "backgroundColor": {"red": 0.92, "green": 0.95, "blue": 0.98},
                            }
                        },
                        "fields": "userEnteredFormat(textFormat,backgroundColor)",
                    }
                },
                {
                    "autoResizeDimensions": {
                        "dimensions": {
                            "sheetId": sheet_id,
                            "dimension": "COLUMNS",
                            "startIndex": 0,
                            "endIndex": len(REPORTS_SHEET_HEADERS),
                        }
                    }
                },
            ]
        },
    )
    response.raise_for_status()


async def _sync_reports_google_sheet_locked(session: SessionIdentity, *, year: int) -> None:
    title = f"Segnalazioni {year}"
    reports = get_tenant_store().list_reports_for_google_sheet(session, year=year)
    values = [REPORTS_SHEET_HEADERS, *_rows_for_reports(reports)]
    connection = await get_active_google_workspace_connection(session)
    settings = get_settings()

    async with httpx.AsyncClient(timeout=settings.google_workspace_request_timeout_seconds) as client:
        spreadsheet_id = await _resolve_reports_spreadsheet(client, access_token=connection.access_token, title=title)
        sheet_id, sheet_title = await _first_sheet_properties(
            client,
            access_token=connection.access_token,
            spreadsheet_id=spreadsheet_id,
        )
        last_column = _column_name(len(REPORTS_SHEET_HEADERS))
        clear_range = quote(_sheet_range(sheet_title, f"A:{last_column}"), safe="")
        clear_response = await client.post(
            f"{GOOGLE_SHEETS_ENDPOINT}/spreadsheets/{quote(spreadsheet_id, safe='')}/values/{clear_range}:clear",
            headers={"Authorization": f"Bearer {connection.access_token}"},
            json={},
        )
        clear_response.raise_for_status()

        update_range = quote(_sheet_range(sheet_title, "A1"), safe="")
        update_response = await client.put(
            f"{GOOGLE_SHEETS_ENDPOINT}/spreadsheets/{quote(spreadsheet_id, safe='')}/values/{update_range}",
            headers={
                "Authorization": f"Bearer {connection.access_token}",
                "Content-Type": "application/json",
            },
            params={"valueInputOption": "RAW"},
            json={"range": _sheet_range(sheet_title, "A1"), "majorDimension": "ROWS", "values": values},
        )
        update_response.raise_for_status()
        await _format_reports_sheet(
            client,
            access_token=connection.access_token,
            spreadsheet_id=spreadsheet_id,
            sheet_id=sheet_id,
        )


async def sync_reports_google_sheet(session: SessionIdentity, report: dict[str, object] | None = None) -> None:
    year = _report_sync_year(report)
    lock_key = (session.tenant_id, year)
    lock = _REPORTS_SHEET_LOCKS.setdefault(lock_key, asyncio.Lock())
    async with lock:
        await _sync_reports_google_sheet_locked(session, year=year)


async def sync_reports_google_sheet_safely(session: SessionIdentity, report: dict[str, object] | None = None) -> None:
    try:
        await sync_reports_google_sheet(session, report)
    except Exception:
        logger.exception("Sincronizzazione Google Sheet segnalazioni fallita per tenant %s", session.tenant_id)