"""
SQLite database schema for Garmin Health Sync system.

Defines tables for sync tracking and data indexing.
"""

import sqlite3
from pathlib import Path
from typing import Optional

from health.config import DB_PATH
from health.utils.logging_config import setup_logger

logger = setup_logger(__name__)


# SQL statements for creating tables
CREATE_SYNC_RECORDS_TABLE = """
CREATE TABLE IF NOT EXISTS sync_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    data_type TEXT NOT NULL,
    start_date TEXT NOT NULL,
    end_date TEXT NOT NULL,
    status TEXT NOT NULL,  -- 'success', 'failed', 'partial'
    records_synced INTEGER DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
"""

CREATE_LAST_SYNC_STATE_TABLE = """
CREATE TABLE IF NOT EXISTS last_sync_state (
    data_type TEXT PRIMARY KEY,
    last_sync_date TEXT NOT NULL,
    total_records INTEGER DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
"""

CREATE_DAILY_METRICS_INDEX_TABLE = """
CREATE TABLE IF NOT EXISTS daily_metrics_index (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    metric_type TEXT NOT NULL,
    date TEXT NOT NULL,
    file_path TEXT NOT NULL,
    has_data BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(metric_type, date)
);
"""

CREATE_ACTIVITY_INDEX_TABLE = """
CREATE TABLE IF NOT EXISTS activity_index (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    activity_id TEXT UNIQUE NOT NULL,
    activity_type TEXT,
    date TEXT NOT NULL,
    duration_seconds INTEGER,
    distance_meters REAL,
    file_path TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
"""

# Indexes for performance
CREATE_INDEXES = [
    "CREATE INDEX IF NOT EXISTS idx_sync_records_type_date ON sync_records(data_type, start_date);",
    "CREATE INDEX IF NOT EXISTS idx_daily_metrics_type_date ON daily_metrics_index(metric_type, date);",
    "CREATE INDEX IF NOT EXISTS idx_activity_date ON activity_index(date);",
    "CREATE INDEX IF NOT EXISTS idx_activity_type ON activity_index(activity_type);",
]


def get_connection(db_path: Optional[Path] = None) -> sqlite3.Connection:
    """Get a database connection.

    Args:
        db_path: Optional path to database file (defaults to config.DB_PATH)

    Returns:
        SQLite connection object
    """
    path = db_path or DB_PATH
    conn = sqlite3.connect(str(path))
    conn.row_factory = sqlite3.Row  # Enable column access by name
    return conn


def init_database(db_path: Optional[Path] = None) -> None:
    """Initialize database schema.

    Creates all necessary tables and indexes if they don't exist.

    Args:
        db_path: Optional path to database file (defaults to config.DB_PATH)
    """
    path = db_path or DB_PATH
    logger.info(f"Initializing database at {path}")

    with get_connection(path) as conn:
        cursor = conn.cursor()

        # Create tables
        cursor.execute(CREATE_SYNC_RECORDS_TABLE)
        cursor.execute(CREATE_LAST_SYNC_STATE_TABLE)
        cursor.execute(CREATE_DAILY_METRICS_INDEX_TABLE)
        cursor.execute(CREATE_ACTIVITY_INDEX_TABLE)

        # Create indexes
        for index_sql in CREATE_INDEXES:
            cursor.execute(index_sql)

        conn.commit()

    logger.info("Database initialization complete")


def reset_database(db_path: Optional[Path] = None) -> None:
    """Reset database by dropping all tables and reinitializing.

    WARNING: This will delete all sync history and indexes!

    Args:
        db_path: Optional path to database file (defaults to config.DB_PATH)
    """
    path = db_path or DB_PATH
    logger.warning(f"Resetting database at {path}")

    with get_connection(path) as conn:
        cursor = conn.cursor()

        # Drop all tables
        cursor.execute("DROP TABLE IF EXISTS sync_records")
        cursor.execute("DROP TABLE IF EXISTS last_sync_state")
        cursor.execute("DROP TABLE IF EXISTS daily_metrics_index")
        cursor.execute("DROP TABLE IF EXISTS activity_index")

        conn.commit()

    # Reinitialize
    init_database(path)
    logger.info("Database reset complete")


if __name__ == "__main__":
    # Allow running as script to initialize database
    init_database()
    print(f"Database initialized at {DB_PATH}")