Activity Reminder Bot

import base64
import json
import logging
from datetime import date, datetime, time
from functools import lru_cache
from typing import Any

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

from activity_reminder_bot.core.config import get_settings
from activity_reminder_bot.models import Reminder, ReminderDraft, ReminderRepeat, ReminderStatus

logger = logging.getLogger(__name__)

SHEET_COLUMNS = [
    "ID",
    "User ID",
    "Nama User",
    "Nama Kegiatan",
    "Deskripsi",
    "Tanggal",
    "Jam",
    "Repeat",
    "Status",
    "Created At",
    "Updated At",
    "Notified At",
    "Alarm Message ID",
    "Alarm Count",
]


def spreadsheet_column_letter(column_number: int) -> str:
    result = ""
    current = column_number
    while current:
        current, remainder = divmod(current - 1, 26)
        result = chr(65 + remainder) + result
    return result


def parse_datetime_value(value: str) -> datetime | None:
    value = str(value or "").strip()
    if not value:
        return None
    for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%dT%H:%M:%S"):
        try:
            return datetime.strptime(value, fmt)
        except ValueError:
            continue
    return None


class GoogleSheetsReminderRepository:
    def __init__(self) -> None:
        self.settings = get_settings()
        self._layout_ready = False

    @property
    def spreadsheet_id(self) -> str:
        return self.settings.google_sheets_spreadsheet_id.strip()

    @property
    def worksheet_name(self) -> str:
        return self.settings.google_sheets_worksheet_name.strip() or "Reminders"

    def is_configured(self) -> bool:
        return self.settings.is_sheets_configured

    def ensure_layout(self) -> None:
        if not self.is_configured():
            raise RuntimeError("Google Sheets belum dikonfigurasi.")
        if self._layout_ready:
            return

        service = self._build_sheets_service()
        sheet_id = self._ensure_worksheet_exists(service)
        end_column = spreadsheet_column_letter(len(SHEET_COLUMNS))
        service.spreadsheets().values().update(
            spreadsheetId=self.spreadsheet_id,
            range=f"{self.worksheet_name}!A1:{end_column}1",
            valueInputOption="RAW",
            body={"values": [SHEET_COLUMNS]},
        ).execute()

        try:
            service.spreadsheets().batchUpdate(
                spreadsheetId=self.spreadsheet_id,
                body={
                    "requests": [
                        {
                            "updateSheetProperties": {
                                "properties": {"sheetId": sheet_id, "gridProperties": {"frozenRowCount": 1}},
                                "fields": "gridProperties.frozenRowCount",
                            }
                        },
                        {
                            "repeatCell": {
                                "range": {
                                    "sheetId": sheet_id,
                                    "startRowIndex": 0,
                                    "endRowIndex": 1,
                                    "startColumnIndex": 0,
                                    "endColumnIndex": len(SHEET_COLUMNS),
                                },
                                "cell": {
                                    "userEnteredFormat": {
                                        "backgroundColor": {"red": 0.9, "green": 0.95, "blue": 1.0},
                                        "horizontalAlignment": "CENTER",
                                        "textFormat": {"bold": True},
                                    }
                                },
                                "fields": "userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)",
                            }
                        },
                        {
                            "autoResizeDimensions": {
                                "dimensions": {
                                    "sheetId": sheet_id,
                                    "dimension": "COLUMNS",
                                    "startIndex": 0,
                                    "endIndex": len(SHEET_COLUMNS),
                                }
                            }
                        },
                    ]
                },
            ).execute()
        except Exception as exc:
            logger.warning("Sheet formatting skipped: %s", exc)
        self._layout_ready = True

    def create(self, reminder: ReminderDraft, public_id: str, now: datetime) -> Reminder:
        self.ensure_layout()
        created = Reminder(
            public_id=public_id,
            user_id=reminder.user_id,
            user_name=reminder.user_name,
            activity_name=reminder.activity_name,
            description=reminder.description,
            reminder_date=reminder.reminder_date,
            reminder_time=reminder.reminder_time,
            repeat=reminder.repeat,
            status=ReminderStatus.active,
            created_at=now,
            updated_at=now,
        )
        service = self._build_sheets_service()
        service.spreadsheets().values().append(
            spreadsheetId=self.spreadsheet_id,
            range=f"{self.worksheet_name}!A:{spreadsheet_column_letter(len(SHEET_COLUMNS))}",
            valueInputOption="RAW",
            insertDataOption="INSERT_ROWS",
            body={"values": [self._row_values(created)]},
        ).execute()
        return created

    def list_all(self, include_deleted: bool = False) -> list[Reminder]:
        if not self.is_configured():
            return []
        self.ensure_layout()
        service = self._build_sheets_service()
        response = service.spreadsheets().values().get(
            spreadsheetId=self.spreadsheet_id,
            range=f"{self.worksheet_name}!A:{spreadsheet_column_letter(len(SHEET_COLUMNS))}",
        ).execute()
        values = response.get("values", [])
        if len(values) <= 1:
            return []
        reminders: list[Reminder] = []
        for row_number, raw_row in enumerate(values[1:], start=2):
            if self._is_non_data_row(raw_row):
                continue
            try:
                reminder = self._reminder_from_row(raw_row, row_number)
            except ValueError as exc:
                logger.warning("Skipping invalid sheet row %s: %s", row_number, exc)
                continue
            if not include_deleted and reminder.status == ReminderStatus.deleted:
                continue
            reminders.append(reminder)
        return reminders

    def get_by_id(self, public_id: str) -> Reminder | None:
        for reminder in self.list_all(include_deleted=True):
            if reminder.public_id == public_id:
                return reminder
        return None

    def list_for_user(self, user_id: int, include_history: bool = False) -> list[Reminder]:
        statuses = {ReminderStatus.active, ReminderStatus.snoozed, ReminderStatus.notified}
        if include_history:
            statuses.update({ReminderStatus.done, ReminderStatus.deleted})
        return [
            reminder
            for reminder in self.list_all(include_deleted=include_history)
            if reminder.user_id == user_id and reminder.status in statuses
        ]

    def search(self, user_id: int, query: str, limit: int = 10) -> list[Reminder]:
        keywords = [word.lower() for word in query.split() if word.strip()]
        if not keywords:
            return []
        results: list[Reminder] = []
        for reminder in reversed(self.list_for_user(user_id, include_history=True)):
            haystack = " ".join(
                [
                    reminder.activity_name,
                    reminder.description,
                    reminder.reminder_date.strftime("%d %B %Y"),
                    reminder.reminder_date.isoformat(),
                    reminder.status.value,
                ]
            ).lower()
            if all(keyword in haystack for keyword in keywords):
                results.append(reminder)
            if len(results) >= limit:
                break
        return results

    def cleanup_user_history(self, user_id: int) -> dict[str, int]:
        return self._cleanup_rows(user_id=user_id, statuses={ReminderStatus.done, ReminderStatus.deleted})

    def cleanup_invalid_rows(self) -> dict[str, int]:
        return self._cleanup_rows(include_invalid=True)

    def _cleanup_rows(
        self,
        user_id: int | None = None,
        statuses: set[ReminderStatus] | None = None,
        include_invalid: bool = False,
    ) -> dict[str, int]:
        if not self.is_configured():
            return {"deleted": 0}
        self.ensure_layout()
        service = self._build_sheets_service()
        response = service.spreadsheets().values().get(
            spreadsheetId=self.spreadsheet_id,
            range=f"{self.worksheet_name}!A:{spreadsheet_column_letter(len(SHEET_COLUMNS))}",
        ).execute()
        values = response.get("values", [])
        row_numbers_to_delete: list[int] = []

        for row_number, raw_row in enumerate(values[1:], start=2):
            if self._is_non_data_row(raw_row):
                if include_invalid:
                    row_numbers_to_delete.append(row_number)
                continue
            try:
                reminder = self._reminder_from_row(raw_row, row_number)
            except ValueError:
                if include_invalid:
                    row_numbers_to_delete.append(row_number)
                continue
            if user_id is not None and reminder.user_id != user_id:
                continue
            if statuses and reminder.status in statuses:
                row_numbers_to_delete.append(row_number)

        if row_numbers_to_delete:
            self._delete_row_numbers(service, row_numbers_to_delete)
        return {"deleted": len(row_numbers_to_delete)}

    def _delete_row_numbers(self, service, row_numbers: list[int]) -> None:
        sheet_id = self._get_sheet_id()
        requests = [
            {
                "deleteDimension": {
                    "range": {
                        "sheetId": sheet_id,
                        "dimension": "ROWS",
                        "startIndex": row_number - 1,
                        "endIndex": row_number,
                    }
                }
            }
            for row_number in sorted(set(row_numbers), reverse=True)
        ]
        service.spreadsheets().batchUpdate(
            spreadsheetId=self.spreadsheet_id,
            body={"requests": requests},
        ).execute()

    def _is_non_data_row(self, raw_row: list[Any]) -> bool:
        if not raw_row or not any(str(value).strip() for value in raw_row):
            return True
        first_cell = str(raw_row[0]).strip().lower()
        return first_cell in {"", "id", "public id", "reminder id"}

    def update(self, public_id: str, updates: dict[str, Any], now: datetime) -> Reminder:
        reminder = self.get_by_id(public_id)
        if not reminder or not reminder.row_number:
            raise ValueError("Reminder tidak ditemukan.")
        data = reminder.model_dump()
        data.update(updates)
        data["updated_at"] = now
        updated = Reminder(**data)

        end_column = spreadsheet_column_letter(len(SHEET_COLUMNS))
        service = self._build_sheets_service()
        service.spreadsheets().values().update(
            spreadsheetId=self.spreadsheet_id,
            range=f"{self.worksheet_name}!A{reminder.row_number}:{end_column}{reminder.row_number}",
            valueInputOption="RAW",
            body={"values": [self._row_values(updated)]},
        ).execute()
        return updated

    def _row_values(self, reminder: Reminder) -> list[Any]:
        return [
            reminder.public_id,
            str(reminder.user_id),
            reminder.user_name,
            reminder.activity_name,
            reminder.description,
            reminder.reminder_date.isoformat(),
            reminder.reminder_time.strftime("%H:%M"),
            reminder.repeat.value,
            reminder.status.value,
            reminder.created_at.strftime("%Y-%m-%d %H:%M:%S"),
            reminder.updated_at.strftime("%Y-%m-%d %H:%M:%S"),
            reminder.notified_at.strftime("%Y-%m-%d %H:%M:%S") if reminder.notified_at else "",
            str(reminder.alarm_message_id or ""),
            str(reminder.alarm_count),
        ]

    def _reminder_from_row(self, raw_row: list[Any], row_number: int) -> Reminder:
        row = list(raw_row) + [""] * (len(SHEET_COLUMNS) - len(raw_row))
        try:
            reminder_date = date.fromisoformat(str(row[5]).strip())
            reminder_time = time.fromisoformat(str(row[6]).strip())
        except ValueError as exc:
            raise ValueError("Tanggal/Jam tidak valid") from exc

        created_at = parse_datetime_value(str(row[9])) or datetime.combine(reminder_date, reminder_time)
        updated_at = parse_datetime_value(str(row[10])) or created_at
        notified_at = parse_datetime_value(str(row[11]))
        alarm_message_id = int(str(row[12]).strip()) if str(row[12]).strip().isdigit() else None
        alarm_count = int(str(row[13]).strip()) if str(row[13]).strip().isdigit() else 0

        return Reminder(
            public_id=str(row[0]).strip(),
            user_id=int(str(row[1]).strip()),
            user_name=str(row[2]).strip(),
            activity_name=str(row[3]).strip(),
            description=str(row[4]).strip(),
            reminder_date=reminder_date,
            reminder_time=reminder_time,
            repeat=ReminderRepeat(str(row[7]).strip() or ReminderRepeat.none.value),
            status=ReminderStatus(str(row[8]).strip() or ReminderStatus.active.value),
            created_at=created_at,
            updated_at=updated_at,
            notified_at=notified_at,
            alarm_message_id=alarm_message_id,
            alarm_count=alarm_count,
            row_number=row_number,
        )

    def _get_sheet_id(self) -> int:
        spreadsheet = self._build_sheets_service().spreadsheets().get(
            spreadsheetId=self.spreadsheet_id,
            fields="sheets.properties",
        ).execute()
        for sheet in spreadsheet.get("sheets", []):
            properties = sheet.get("properties", {})
            if properties.get("title") == self.worksheet_name:
                return int(properties["sheetId"])
        raise RuntimeError(f"Worksheet tidak ditemukan: {self.worksheet_name}")

    def _ensure_worksheet_exists(self, service) -> int:
        spreadsheet = service.spreadsheets().get(
            spreadsheetId=self.spreadsheet_id,
            fields="sheets.properties",
        ).execute()
        for sheet in spreadsheet.get("sheets", []):
            properties = sheet.get("properties", {})
            if properties.get("title") == self.worksheet_name:
                return int(properties["sheetId"])

        response = service.spreadsheets().batchUpdate(
            spreadsheetId=self.spreadsheet_id,
            body={"requests": [{"addSheet": {"properties": {"title": self.worksheet_name}}}]},
        ).execute()
        replies = response.get("replies", [])
        if not replies:
            raise RuntimeError(f"Gagal membuat worksheet: {self.worksheet_name}")
        return int(replies[0]["addSheet"]["properties"]["sheetId"])

    def _build_sheets_service(self):
        service_account_info = json.loads(
            base64.b64decode(self.settings.google_service_account_json_base64).decode("utf-8")
        )
        credentials = Credentials.from_service_account_info(
            service_account_info,
            scopes=["https://www.googleapis.com/auth/spreadsheets"],
        )
        return build("sheets", "v4", credentials=credentials, cache_discovery=False)


@lru_cache(maxsize=1)
def get_reminder_repository() -> GoogleSheetsReminderRepository:
    return GoogleSheetsReminderRepository()