import datetime
import logging
import os
from typing import Dict, List, Tuple

import psycopg2
import redis

# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# Database connection details (replace with your actual credentials)
DB_HOST = "postgresql-genesis-u50607.vm.elestio.app"
DB_PORT = 25432
DB_NAME = "your_db_name"  # Replace with your database name
DB_USER = "your_db_user"  # Replace with your database user
DB_PASSWORD = "your_db_password"  # Replace with your database password

# Redis connection details
REDIS_HOST = "redis-genesis-u50607.vm.elestio.app"
REDIS_PORT = 26379

# Report output directory
REPORT_DIR = "/mnt/e/genesis-system/logs/daily_costs/"

class DatabaseConnectionError(Exception):
    """Custom exception for database connection errors."""
    pass

class DataQueryError(Exception):
    """Custom exception for data query errors."""
    pass

class ReportGenerationError(Exception):
    """Custom exception for report generation errors."""
    pass


def get_db_connection():
    """Establishes a connection to the PostgreSQL database.

    Returns:
        psycopg2.extensions.connection: A database connection object.

    Raises:
        DatabaseConnectionError: If a connection to the database cannot be established.
    """
    try:
        conn = psycopg2.connect(
            host=DB_HOST,
            port=DB_PORT,
            database=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD
        )
        return conn
    except psycopg2.Error as e:
        logging.error(f"Error connecting to the database: {e}")
        raise DatabaseConnectionError(f"Failed to connect to the database: {e}")


def get_total_spend(conn: psycopg2.extensions.connection, date: datetime.date) -> float:
    """Retrieves the total spend for a given date from the database.

    Args:
        conn: A database connection object.
        date: The date for which to retrieve the total spend.

    Returns:
        The total spend for the given date.

    Raises:
        DataQueryError: If there is an error querying the database.
    """
    try:
        cur = conn.cursor()
        query = "SELECT SUM(cost) FROM tasks WHERE date(start_time) = %s;"
        cur.execute(query, (date,))
        result = cur.fetchone()[0]
        return float(result) if result else 0.0
    except psycopg2.Error as e:
        logging.error(f"Error querying total spend: {e}")
        raise DataQueryError(f"Failed to query total spend: {e}")


def get_spend_by_provider(conn: psycopg2.extensions.connection, date: datetime.date) -> Dict[str, float]:
    """Retrieves the spend breakdown by provider for a given date from the database.

    Args:
        conn: A database connection object.
        date: The date for which to retrieve the spend breakdown.

    Returns:
        A dictionary containing the spend breakdown by provider.

    Raises:
        DataQueryError: If there is an error querying the database.
    """
    try:
        cur = conn.cursor()
        query = "SELECT provider, SUM(cost) FROM tasks WHERE date(start_time) = %s GROUP BY provider;"
        cur.execute(query, (date,))
        results = cur.fetchall()
        return {provider: float(cost) for provider, cost in results}
    except psycopg2.Error as e:
        logging.error(f"Error querying spend by provider: {e}")
        raise DataQueryError(f"Failed to query spend by provider: {e}")


def get_top_expensive_tasks(conn: psycopg2.extensions.connection, date: datetime.date, limit: int = 5) -> List[Tuple[str, float]]:
    """Retrieves the top N most expensive tasks for a given date from the database.

    Args:
        conn: A database connection object.
        date: The date for which to retrieve the top tasks.
        limit: The maximum number of tasks to retrieve.

    Returns:
        A list of tuples containing the task name and cost, sorted by cost in descending order.

    Raises:
        DataQueryError: If there is an error querying the database.
    """
    try:
        cur = conn.cursor()
        query = "SELECT name, cost FROM tasks WHERE date(start_time) = %s ORDER BY cost DESC LIMIT %s;"
        cur.execute(query, (date, limit))
        results = cur.fetchall()
        return [(name, float(cost)) for name, cost in results]
    except psycopg2.Error as e:
        logging.error(f"Error querying top expensive tasks: {e}")
        raise DataQueryError(f"Failed to query top expensive tasks: {e}")


def get_previous_day_spend(conn: psycopg2.extensions.connection, date: datetime.date) -> float:
    """Retrieves the total spend for the previous day from the database.

    Args:
        conn: A database connection object.
        date: The date for which to retrieve the previous day's spend.

    Returns:
        The total spend for the previous day.

    Raises:
        DataQueryError: If there is an error querying the database.
    """
    previous_date = date - datetime.timedelta(days=1)
    return get_total_spend(conn, previous_date)


def get_weekly_average_spend(conn: psycopg2.extensions.connection, date: datetime.date) -> float:
    """Retrieves the average spend for the past week from the database.

    Args:
        conn: A database connection object.
        date: The date for which to calculate the weekly average spend.

    Returns:
        The average spend for the past week.

    Raises:
        DataQueryError: If there is an error querying the database.
    """
    try:
        cur = conn.cursor()
        query = """
            SELECT AVG(daily_spend)
            FROM (
                SELECT date(start_time) AS dt, SUM(cost) AS daily_spend
                FROM tasks
                WHERE date(start_time) BETWEEN %s AND %s
                GROUP BY dt
            ) AS daily_spends;
        """
        start_date = date - datetime.timedelta(days=6)
        cur.execute(query, (start_date, date))
        result = cur.fetchone()[0]
        return float(result) if result else 0.0
    except psycopg2.Error as e:
        logging.error(f"Error querying weekly average spend: {e}")
        raise DataQueryError(f"Failed to query weekly average spend: {e}")


def generate_markdown_report(date: datetime.date) -> str:
    """Generates a markdown report for the daily cost summary.

    Args:
        date: The date for which to generate the report.

    Returns:
        A string containing the markdown report.

    Raises:
        ReportGenerationError: If there is an error generating the report.
    """
    try:
        conn = get_db_connection()
        total_spend = get_total_spend(conn, date)
        spend_by_provider = get_spend_by_provider(conn, date)
        top_tasks = get_top_expensive_tasks(conn, date)
        previous_day_spend = get_previous_day_spend(conn, date)
        weekly_average_spend = get_weekly_average_spend(conn, date)

        report = f"""
# Daily Cost Report - {date.strftime("%Y-%m-%d")}

## Summary

- **Total Spend:** ${total_spend:.2f}
- **Previous Day Spend:** ${previous_day_spend:.2f}
- **Weekly Average Spend:** ${weekly_average_spend:.2f}

## Spend by Provider

"""
        for provider, cost in spend_by_provider.items():
            report += f"- **{provider}:** ${cost:.2f}\n"

        report += """

## Top 5 Most Expensive Tasks

"""
        for task, cost in top_tasks:
            report += f"- **{task}:** ${cost:.2f}\n"

        conn.close()
        return report

    except (DatabaseConnectionError, DataQueryError) as e:
        raise ReportGenerationError(f"Failed to generate report: {e}")
    except Exception as e:
        logging.error(f"Unexpected error during report generation: {e}")
        raise ReportGenerationError(f"Unexpected error during report generation: {e}")


def save_report_to_file(report: str, date: datetime.date) -> None:
    """Saves the markdown report to a file.

    Args:
        report: The markdown report to save.
        date: The date for which the report was generated.
    """
    os.makedirs(REPORT_DIR, exist_ok=True)  # Ensure directory exists
    filename = os.path.join(REPORT_DIR, f"daily_cost_report_{date.strftime('%Y-%m-%d')}.md")
    try:
        with open(filename, "w") as f:
            f.write(report)
        logging.info(f"Report saved to {filename}")
    except IOError as e:
        logging.error(f"Error saving report to file: {e}")


def generate_and_save_daily_report(date: datetime.date) -> None:
    """Generates and saves the daily cost report for a given date.

    Args:
        date: The date for which to generate the report.
    """
    try:
        report = generate_markdown_report(date)
        save_report_to_file(report, date)
    except ReportGenerationError as e:
        logging.error(f"Failed to generate and save daily report: {e}")


if __name__ == '__main__':
    # Example usage: Generate and save the report for today
    today = datetime.date.today()
    generate_and_save_daily_report(today)

    # Example Usage: Generate and save for a specific date (for testing)
    # specific_date = datetime.date(2024, 1, 1)
    # generate_and_save_daily_report(specific_date)