"""
Data access layer for health database.

Provides CRUD operations for sync records, sync state, and data indexes.
"""

import sqlite3
from datetime import date, datetime
from typing import List, Optional, Dict, Any
from pathlib import Path

from health.db.schema import get_connection
from health.utils.logging_config import setup_logger

logger = setup_logger(__name__)


class HealthRepository:
    """Repository for health data database operations."""

    def __init__(self, db_path: Optional[Path] = None) -> None:
        """Initialize repository.

        Args:
            db_path: Optional path to database file
        """
        self.db_path = db_path

    def _get_conn(self) -> sqlite3.Connection:
        """Get database connection.

        Returns:
            SQLite connection
        """
        return get_connection(self.db_path)

    # ============ Sync Records ============

    def create_sync_record(
        self,
        data_type: str,
        start_date: date,
        end_date: date,
        status: str,
        records_synced: int = 0,
        error_message: Optional[str] = None,
    ) -> int:
        """Create a new sync record.

        Args:
            data_type: Type of data synced
            start_date: Start date of sync range
            end_date: End date of sync range
            status: Sync status ('success', 'failed', 'partial')
            records_synced: Number of records successfully synced
            error_message: Optional error message if sync failed

        Returns:
            ID of created record
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT INTO sync_records
                (data_type, start_date, end_date, status, records_synced, error_message)
                VALUES (?, ?, ?, ?, ?, ?)
                """,
                (
                    data_type,
                    start_date.isoformat(),
                    end_date.isoformat(),
                    status,
                    records_synced,
                    error_message,
                ),
            )
            conn.commit()
            return cursor.lastrowid

    def get_sync_records(
        self,
        data_type: Optional[str] = None,
        limit: int = 100,
    ) -> List[Dict[str, Any]]:
        """Get sync records, optionally filtered by data type.

        Args:
            data_type: Optional data type filter
            limit: Maximum number of records to return

        Returns:
            List of sync record dictionaries
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()

            if data_type:
                cursor.execute(
                    """
                    SELECT * FROM sync_records
                    WHERE data_type = ?
                    ORDER BY created_at DESC
                    LIMIT ?
                    """,
                    (data_type, limit),
                )
            else:
                cursor.execute(
                    """
                    SELECT * FROM sync_records
                    ORDER BY created_at DESC
                    LIMIT ?
                    """,
                    (limit,),
                )

            return [dict(row) for row in cursor.fetchall()]

    # ============ Last Sync State ============

    def get_last_sync_date(self, data_type: str) -> Optional[date]:
        """Get the last sync date for a data type.

        Args:
            data_type: Type of data

        Returns:
            Last sync date, or None if never synced
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT last_sync_date FROM last_sync_state WHERE data_type = ?",
                (data_type,),
            )
            row = cursor.fetchone()

            if row:
                return datetime.fromisoformat(row["last_sync_date"]).date()
            return None

    def update_last_sync_state(
        self,
        data_type: str,
        last_sync_date: date,
        total_records: Optional[int] = None,
    ) -> None:
        """Update the last sync state for a data type.

        Args:
            data_type: Type of data
            last_sync_date: Last successfully synced date
            total_records: Optional total record count to set
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()

            # Check if record exists
            cursor.execute(
                "SELECT total_records FROM last_sync_state WHERE data_type = ?",
                (data_type,),
            )
            row = cursor.fetchone()

            if row:
                # Update existing
                if total_records is not None:
                    cursor.execute(
                        """
                        UPDATE last_sync_state
                        SET last_sync_date = ?, total_records = ?, updated_at = CURRENT_TIMESTAMP
                        WHERE data_type = ?
                        """,
                        (last_sync_date.isoformat(), total_records, data_type),
                    )
                else:
                    cursor.execute(
                        """
                        UPDATE last_sync_state
                        SET last_sync_date = ?, updated_at = CURRENT_TIMESTAMP
                        WHERE data_type = ?
                        """,
                        (last_sync_date.isoformat(), data_type),
                    )
            else:
                # Insert new
                cursor.execute(
                    """
                    INSERT INTO last_sync_state (data_type, last_sync_date, total_records)
                    VALUES (?, ?, ?)
                    """,
                    (data_type, last_sync_date.isoformat(), total_records or 0),
                )

            conn.commit()

    def get_all_last_sync_states(self) -> Dict[str, Dict[str, Any]]:
        """Get last sync state for all data types.

        Returns:
            Dictionary mapping data_type to state info
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM last_sync_state")

            result = {}
            for row in cursor.fetchall():
                result[row["data_type"]] = {
                    "last_sync_date": row["last_sync_date"],
                    "total_records": row["total_records"],
                    "updated_at": row["updated_at"],
                }

            return result

    # ============ Daily Metrics Index ============

    def index_daily_metric(
        self,
        metric_type: str,
        metric_date: date,
        file_path: Path,
        has_data: bool = True,
    ) -> None:
        """Index a daily metric file.

        Args:
            metric_type: Type of metric
            metric_date: Date of the metric
            file_path: Path to JSON file
            has_data: Whether the file contains valid data
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT OR REPLACE INTO daily_metrics_index
                (metric_type, date, file_path, has_data)
                VALUES (?, ?, ?, ?)
                """,
                (
                    metric_type,
                    metric_date.isoformat(),
                    str(file_path),
                    has_data,
                ),
            )
            conn.commit()

    def get_daily_metric_path(
        self, metric_type: str, metric_date: date
    ) -> Optional[str]:
        """Get file path for a daily metric.

        Args:
            metric_type: Type of metric
            metric_date: Date of the metric

        Returns:
            File path string, or None if not found
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT file_path FROM daily_metrics_index
                WHERE metric_type = ? AND date = ?
                """,
                (metric_type, metric_date.isoformat()),
            )
            row = cursor.fetchone()

            return row["file_path"] if row else None

    # ============ Activity Index ============

    def index_activity(
        self,
        activity_id: str,
        activity_type: str,
        activity_date: date,
        file_path: Path,
        duration_seconds: Optional[int] = None,
        distance_meters: Optional[float] = None,
    ) -> None:
        """Index an activity file.

        Args:
            activity_id: Unique activity ID
            activity_type: Type of activity
            activity_date: Date of the activity
            file_path: Path to JSON file
            duration_seconds: Optional activity duration
            distance_meters: Optional activity distance
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT OR REPLACE INTO activity_index
                (activity_id, activity_type, date, duration_seconds, distance_meters, file_path)
                VALUES (?, ?, ?, ?, ?, ?)
                """,
                (
                    activity_id,
                    activity_type,
                    activity_date.isoformat(),
                    duration_seconds,
                    distance_meters,
                    str(file_path),
                ),
            )
            conn.commit()

    def get_activities_by_date_range(
        self, start_date: date, end_date: date
    ) -> List[Dict[str, Any]]:
        """Get all activities within a date range.

        Args:
            start_date: Start date
            end_date: End date

        Returns:
            List of activity index records
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT * FROM activity_index
                WHERE date >= ? AND date <= ?
                ORDER BY date DESC
                """,
                (start_date.isoformat(), end_date.isoformat()),
            )

            return [dict(row) for row in cursor.fetchall()]

    def activity_exists(self, activity_id: str) -> bool:
        """Check if an activity is already indexed.

        Args:
            activity_id: Activity ID to check

        Returns:
            True if activity exists in index
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT 1 FROM activity_index WHERE activity_id = ?",
                (activity_id,),
            )
            return cursor.fetchone() is not None