from __future__ import annotations

import logging
from datetime import date
from typing import Any

logger = logging.getLogger("dscons.postgres.project_crud.financial_war_room_queries")


class FinancialWarRoomQueriesMixin:
    """Queries for fetching real financial transactions for cash flow forecasting."""

    def fetch_planned_transactions(
        self, start_date: date, end_date: date
    ) -> list[dict[str, Any]]:
        """Fetch all planned financial transactions within a date range."""
        sql = """
            SELECT 
                transaction_date,
                transaction_type,
                direction,
                amount
            FROM erp_financial_transactions
            WHERE accounting_status = 'planned'
              AND transaction_date >= %(start_date)s
              AND transaction_date <= %(end_date)s
            ORDER BY transaction_date ASC;
        """
        try:
            with self.get_connection() as conn, conn.cursor() as cur:
                cur.execute(sql, {"start_date": start_date, "end_date": end_date})
                return cur.fetchall()
        except Exception as e:
            logger.error(f"Error fetching planned transactions: {e}")
            return []