"""
Custom Database Connector - Connect directly to client databases

Supports:
- MySQL / MariaDB
- PostgreSQL
- Microsoft SQL Server
- SQLite

The client configures SQL queries for each data type,
allowing AI-360 to work with any database schema.
"""
from typing import Any, Dict, List, Optional, Union
from datetime import date, timedelta
import logging
import re

from app.connectors.base import (
    BaseConnector,
    ConnectorConfig,
    ConnectorType,
    ConnectionStatus,
)

logger = logging.getLogger(__name__)


class CustomDatabaseConnector(BaseConnector):
    """
    Connector for direct database access.

    Query Configuration Example:
    {
        "queries": {
            "sales": "SELECT SUM(total) as total_revenue, COUNT(*) as order_count FROM orders WHERE created_at BETWEEN :start_date AND :end_date",
            "orders": "SELECT * FROM orders WHERE created_at BETWEEN :start_date AND :end_date ORDER BY created_at DESC LIMIT :limit",
            "order_details": "SELECT o.*, c.name as customer_name, c.email FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.id = :order_id",
            "products": "SELECT * FROM products WHERE status = 'active' LIMIT :limit",
            "product_details": "SELECT * FROM products WHERE id = :product_id",
            "inventory": "SELECT p.id, p.name, i.quantity FROM products p JOIN inventory i ON p.id = i.product_id",
            "low_stock": "SELECT * FROM products WHERE stock_quantity <= :threshold",
            "customers": "SELECT * FROM customers ORDER BY created_at DESC LIMIT :limit",
            "customer_details": "SELECT c.*, SUM(o.total) as total_spent, COUNT(o.id) as order_count FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE c.id = :customer_id GROUP BY c.id",
            "top_customers": "SELECT c.*, SUM(o.total) as total_spent FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id ORDER BY total_spent DESC LIMIT :limit"
        },
        "field_mappings": {
            "order": {
                "id": "order_id",
                "total": "grand_total"
            }
        }
    }
    """

    def __init__(self, config: ConnectorConfig, client_id: int):
        super().__init__(config, client_id)
        self._connection = None
        self._engine = None

    @property
    def connector_type(self) -> ConnectorType:
        return ConnectorType.CUSTOM_DATABASE

    async def connect(self) -> bool:
        """Establish database connection"""
        try:
            db_type = self.config.db_type.lower()

            if db_type in ("mysql", "mariadb"):
                await self._connect_mysql()
            elif db_type == "postgresql":
                await self._connect_postgresql()
            elif db_type in ("sqlserver", "mssql"):
                await self._connect_sqlserver()
            elif db_type == "sqlite":
                await self._connect_sqlite()
            else:
                raise ValueError(f"Unsupported database type: {db_type}")

            self.status = ConnectionStatus.CONNECTED
            logger.info(f"Connected to {db_type} database: {self.config.db_name}")
            return True

        except Exception as e:
            self._last_error = str(e)
            self.status = ConnectionStatus.ERROR
            logger.error(f"Failed to connect to database: {e}")
            return False

    async def _connect_mysql(self):
        """Connect to MySQL/MariaDB"""
        try:
            import aiomysql
            self._connection = await aiomysql.connect(
                host=self.config.db_host,
                port=self.config.db_port or 3306,
                user=self.config.db_user,
                password=self.config.db_password,
                db=self.config.db_name,
                ssl=self.config.db_ssl
            )
        except ImportError:
            # Fallback to SQLAlchemy
            await self._connect_sqlalchemy("mysql+aiomysql")

    async def _connect_postgresql(self):
        """Connect to PostgreSQL"""
        try:
            import asyncpg
            self._connection = await asyncpg.connect(
                host=self.config.db_host,
                port=self.config.db_port or 5432,
                user=self.config.db_user,
                password=self.config.db_password,
                database=self.config.db_name,
                ssl=self.config.db_ssl
            )
        except ImportError:
            await self._connect_sqlalchemy("postgresql+asyncpg")

    async def _connect_sqlserver(self):
        """Connect to SQL Server"""
        await self._connect_sqlalchemy("mssql+aioodbc")

    async def _connect_sqlite(self):
        """Connect to SQLite"""
        import aiosqlite
        self._connection = await aiosqlite.connect(self.config.db_name)

    async def _connect_sqlalchemy(self, driver: str):
        """Fallback connection using SQLAlchemy"""
        from sqlalchemy.ext.asyncio import create_async_engine

        url = f"{driver}://{self.config.db_user}:{self.config.db_password}@{self.config.db_host}:{self.config.db_port}/{self.config.db_name}"
        self._engine = create_async_engine(url)

    async def disconnect(self) -> None:
        """Close database connection"""
        if self._connection:
            await self._connection.close()
            self._connection = None
        if self._engine:
            await self._engine.dispose()
            self._engine = None
        self.status = ConnectionStatus.DISCONNECTED

    async def test_connection(self) -> Dict[str, Any]:
        """Test database connection"""
        try:
            if not self._connection and not self._engine:
                await self.connect()

            # Simple test query
            result = await self._execute_query("SELECT 1")
            return {
                "success": True,
                "message": "Connection successful",
                "details": {"database": self.config.db_name}
            }
        except Exception as e:
            return {
                "success": False,
                "message": str(e),
                "details": {}
            }

    async def _execute_query(
        self,
        query: str,
        params: Dict[str, Any] = None
    ) -> List[Dict[str, Any]]:
        """
        Execute a SQL query and return results as list of dicts.

        Args:
            query: SQL query with :param placeholders
            params: Parameter values

        Returns:
            List of row dicts
        """
        if not self._connection and not self._engine:
            await self.connect()

        params = params or {}

        # Convert :param to appropriate format based on DB type
        db_type = self.config.db_type.lower()

        if db_type in ("mysql", "mariadb"):
            # MySQL uses %s placeholders
            query, values = self._convert_params_mysql(query, params)
            return await self._execute_mysql(query, values)

        elif db_type == "postgresql":
            # PostgreSQL uses $1, $2, etc.
            query, values = self._convert_params_postgres(query, params)
            return await self._execute_postgres(query, values)

        elif db_type == "sqlite":
            # SQLite uses ? or :name
            return await self._execute_sqlite(query, params)

        else:
            # Use SQLAlchemy for others
            return await self._execute_sqlalchemy(query, params)

    def _convert_params_mysql(
        self,
        query: str,
        params: Dict[str, Any]
    ) -> tuple[str, list]:
        """Convert :param to %s for MySQL"""
        values = []
        param_names = re.findall(r':(\w+)', query)

        for name in param_names:
            if name in params:
                values.append(params[name])
                query = query.replace(f":{name}", "%s", 1)

        return query, values

    def _convert_params_postgres(
        self,
        query: str,
        params: Dict[str, Any]
    ) -> tuple[str, list]:
        """Convert :param to $1, $2, etc. for PostgreSQL"""
        values = []
        param_names = re.findall(r':(\w+)', query)
        seen = {}
        counter = 1

        for name in param_names:
            if name not in seen:
                seen[name] = f"${counter}"
                values.append(params.get(name))
                counter += 1
            query = query.replace(f":{name}", seen[name], 1)

        return query, values

    async def _execute_mysql(self, query: str, values: list) -> List[Dict]:
        """Execute MySQL query"""
        async with self._connection.cursor() as cursor:
            await cursor.execute(query, values)
            columns = [desc[0] for desc in cursor.description] if cursor.description else []
            rows = await cursor.fetchall()
            return [dict(zip(columns, row)) for row in rows]

    async def _execute_postgres(self, query: str, values: list) -> List[Dict]:
        """Execute PostgreSQL query"""
        rows = await self._connection.fetch(query, *values)
        return [dict(row) for row in rows]

    async def _execute_sqlite(self, query: str, params: Dict) -> List[Dict]:
        """Execute SQLite query"""
        self._connection.row_factory = lambda c, r: dict(zip([col[0] for col in c.description], r))
        async with self._connection.execute(query, params) as cursor:
            return await cursor.fetchall()

    async def _execute_sqlalchemy(self, query: str, params: Dict) -> List[Dict]:
        """Execute query using SQLAlchemy"""
        from sqlalchemy import text
        async with self._engine.connect() as conn:
            result = await conn.execute(text(query), params)
            return [dict(row._mapping) for row in result]

    def _get_query(self, query_key: str) -> str:
        """Get configured query by key"""
        query = self.config.queries.get(query_key)
        if not query:
            raise ValueError(f"Query not configured: {query_key}")
        return query

    # ==================== SALES METHODS ====================

    async def get_sales(
        self,
        period: str = "today",
        start_date: Optional[str] = None,
        end_date: Optional[str] = None
    ) -> Dict[str, Any]:
        """Get sales data"""
        if start_date and end_date:
            s_date, e_date = start_date, end_date
        else:
            s, e = self._parse_period(period)
            s_date, e_date = str(s), str(e)

        try:
            query = self._get_query("sales")
            rows = await self._execute_query(query, {
                "start_date": s_date,
                "end_date": e_date
            })

            if rows:
                result = self._map_fields(rows[0], "sales")
                result["period"] = period
                return result

            return {
                "period": period,
                "total_revenue": 0,
                "order_count": 0
            }
        except Exception as e:
            logger.error(f"Failed to get sales: {e}")
            return {"period": period, "total_revenue": 0, "order_count": 0, "error": str(e)}

    async def get_sales_comparison(
        self,
        current_period: str = "this_month",
        compare_to: str = "previous"
    ) -> Dict[str, Any]:
        """Compare sales between periods"""
        current_start, current_end = self._parse_period(current_period)

        if compare_to == "previous":
            delta = current_end - current_start
            prev_end = current_start - timedelta(days=1)
            prev_start = prev_end - delta
        else:
            prev_start = current_start.replace(year=current_start.year - 1)
            prev_end = current_end.replace(year=current_end.year - 1)

        current = await self.get_sales(start_date=str(current_start), end_date=str(current_end))
        previous = await self.get_sales(start_date=str(prev_start), end_date=str(prev_end))

        curr_rev = current.get("total_revenue", 0) or 0
        prev_rev = previous.get("total_revenue", 0) or 0
        growth = ((curr_rev - prev_rev) / prev_rev * 100) if prev_rev else 0

        return {
            "current_period": current,
            "previous_period": previous,
            "growth": {"revenue_percent": round(growth, 2)}
        }

    async def get_top_products(
        self,
        period: str = "this_month",
        sort_by: str = "revenue",
        limit: int = 10
    ) -> Dict[str, Any]:
        """Get top selling products"""
        start, end = self._parse_period(period)

        try:
            query = self._get_query("top_products")
            rows = await self._execute_query(query, {
                "start_date": str(start),
                "end_date": str(end),
                "sort_by": sort_by,
                "limit": limit
            })
            return {"products": self._map_list(rows, "product")}
        except Exception as e:
            return {"products": [], "error": str(e)}

    async def get_revenue_breakdown(
        self,
        period: str = "this_month",
        group_by: str = "category"
    ) -> Dict[str, Any]:
        """Get revenue breakdown"""
        start, end = self._parse_period(period)

        try:
            query = self._get_query(f"revenue_by_{group_by}")
            rows = await self._execute_query(query, {
                "start_date": str(start),
                "end_date": str(end)
            })
            return {"breakdown": rows}
        except Exception as e:
            return {"breakdown": [], "error": str(e)}

    # ==================== ORDER METHODS ====================

    async def get_orders(
        self,
        status: Optional[str] = None,
        period: str = "this_week",
        customer_id: Optional[str] = None,
        limit: int = 20
    ) -> Dict[str, Any]:
        """Get list of orders"""
        start, end = self._parse_period(period)

        try:
            query = self._get_query("orders")
            params = {
                "start_date": str(start),
                "end_date": str(end),
                "limit": limit
            }

            # Add optional filters to query if configured
            if status and status != "all":
                params["status"] = status
            if customer_id:
                params["customer_id"] = customer_id

            rows = await self._execute_query(query, params)
            return {
                "orders": self._map_list(rows, "order"),
                "total_count": len(rows)
            }
        except Exception as e:
            return {"orders": [], "total_count": 0, "error": str(e)}

    async def get_order_details(self, order_id: str) -> Optional[Dict[str, Any]]:
        """Get order details"""
        try:
            query = self._get_query("order_details")
            rows = await self._execute_query(query, {"order_id": order_id})
            if rows:
                return self._map_fields(rows[0], "order")
            return None
        except Exception as e:
            logger.error(f"Failed to get order {order_id}: {e}")
            return None

    async def get_order_status(self, order_id: str) -> Optional[Dict[str, Any]]:
        """Get order status"""
        order = await self.get_order_details(order_id)
        if order:
            return {
                "order_id": order_id,
                "status": order.get("status", "unknown"),
                "tracking_number": order.get("tracking_number")
            }
        return None

    # ==================== PRODUCT METHODS ====================

    async def get_products(
        self,
        category: Optional[str] = None,
        status: Optional[str] = None,
        in_stock: Optional[bool] = None,
        limit: int = 20
    ) -> Dict[str, Any]:
        """Get list of products"""
        try:
            query = self._get_query("products")
            params = {"limit": limit}
            if category:
                params["category"] = category
            if status:
                params["status"] = status

            rows = await self._execute_query(query, params)
            return {"products": self._map_list(rows, "product")}
        except Exception as e:
            return {"products": [], "error": str(e)}

    async def get_product_details(self, product_id: str) -> Optional[Dict[str, Any]]:
        """Get product details"""
        try:
            query = self._get_query("product_details")
            rows = await self._execute_query(query, {"product_id": product_id})
            if rows:
                return self._map_fields(rows[0], "product")
            return None
        except Exception as e:
            logger.error(f"Failed to get product {product_id}: {e}")
            return None

    async def search_products(self, query_str: str, limit: int = 10) -> Dict[str, Any]:
        """Search products"""
        try:
            query = self._get_query("product_search")
            rows = await self._execute_query(query, {
                "query": f"%{query_str}%",
                "limit": limit
            })
            return {"products": self._map_list(rows, "product"), "query": query_str}
        except Exception as e:
            return {"products": [], "query": query_str, "error": str(e)}

    async def get_low_stock_products(
        self,
        threshold: int = 10,
        include_out_of_stock: bool = True
    ) -> Dict[str, Any]:
        """Get low stock products"""
        try:
            query = self._get_query("low_stock")
            rows = await self._execute_query(query, {"threshold": threshold})
            return {"products": self._map_list(rows, "product")}
        except Exception as e:
            return {"products": [], "error": str(e)}

    # ==================== INVENTORY METHODS ====================

    async def get_inventory(
        self,
        product_ids: Optional[List[str]] = None,
        location: Optional[str] = None
    ) -> Dict[str, Any]:
        """Get inventory levels"""
        try:
            query = self._get_query("inventory")
            params = {}
            if location:
                params["location"] = location

            rows = await self._execute_query(query, params)
            return {"inventory": rows}
        except Exception as e:
            return {"inventory": [], "error": str(e)}

    async def get_inventory_alerts(self, alert_type: str = "all") -> Dict[str, Any]:
        """Get inventory alerts"""
        try:
            # Get low stock and out of stock items
            low_stock = await self.get_low_stock_products(threshold=10)
            alerts = []

            for product in low_stock.get("products", []):
                stock = product.get("stock", product.get("quantity", 0))
                if stock == 0:
                    alerts.append({
                        "type": "out_of_stock",
                        "product_id": product.get("id"),
                        "title": product.get("title", product.get("name")),
                        "current": 0
                    })
                else:
                    alerts.append({
                        "type": "low_stock",
                        "product_id": product.get("id"),
                        "title": product.get("title", product.get("name")),
                        "current": stock,
                        "threshold": 10
                    })

            if alert_type != "all":
                alerts = [a for a in alerts if a["type"] == alert_type]

            return {"alerts": alerts}
        except Exception as e:
            return {"alerts": [], "error": str(e)}

    # ==================== CUSTOMER METHODS ====================

    async def get_customers(
        self,
        status: Optional[str] = None,
        created_after: Optional[str] = None,
        has_orders: Optional[bool] = None,
        limit: int = 20
    ) -> Dict[str, Any]:
        """Get list of customers"""
        try:
            query = self._get_query("customers")
            params = {"limit": limit}
            if created_after:
                params["created_after"] = created_after

            rows = await self._execute_query(query, params)
            return {"customers": self._map_list(rows, "customer")}
        except Exception as e:
            return {"customers": [], "error": str(e)}

    async def get_customer_details(
        self,
        customer_id: Optional[str] = None,
        email: Optional[str] = None,
        phone: Optional[str] = None
    ) -> Optional[Dict[str, Any]]:
        """Get customer details"""
        try:
            if customer_id:
                query = self._get_query("customer_details")
                rows = await self._execute_query(query, {"customer_id": customer_id})
            elif email:
                query = self._get_query("customer_by_email")
                rows = await self._execute_query(query, {"email": email})
            elif phone:
                query = self._get_query("customer_by_phone")
                rows = await self._execute_query(query, {"phone": phone})
            else:
                return None

            if rows:
                return self._map_fields(rows[0], "customer")
            return None
        except Exception as e:
            logger.error(f"Failed to get customer: {e}")
            return None

    async def get_top_customers(
        self,
        period: str = "all_time",
        sort_by: str = "revenue",
        limit: int = 10
    ) -> Dict[str, Any]:
        """Get top customers"""
        try:
            query = self._get_query("top_customers")
            params = {"limit": limit}

            if period != "all_time":
                start, end = self._parse_period(period)
                params["start_date"] = str(start)
                params["end_date"] = str(end)

            rows = await self._execute_query(query, params)
            return {"customers": self._map_list(rows, "customer")}
        except Exception as e:
            return {"customers": [], "error": str(e)}

    async def search_customers(self, query_str: str, limit: int = 10) -> Dict[str, Any]:
        """Search customers"""
        try:
            query = self._get_query("customer_search")
            rows = await self._execute_query(query, {
                "query": f"%{query_str}%",
                "limit": limit
            })
            return {"customers": self._map_list(rows, "customer"), "query": query_str}
        except Exception as e:
            return {"customers": [], "query": query_str, "error": str(e)}