"""
OMS Data Service
Service for extracting time series data from OMS database tables
"""

import re
from typing import List, Dict, Any, Optional, Tuple
import pandas as pd
from datetime import datetime, timedelta
from app.services.database_service import DatabaseService
from app.utils.helpers import get_logger

# Filter keys must be plain column identifiers — anything else is rejected
# before it can reach the SQL string.
_IDENTIFIER_RE = re.compile(r'^[A-Za-z_][A-Za-z0-9_]*$')

_ALLOWED_AGGREGATIONS = ('daily', 'weekly', 'monthly')


class OMSDataService:
    """Service for extracting time series data from OMS database"""

    def __init__(self):
        self.db_service = DatabaseService()
        self.logger = get_logger("oms_data_service")

        # Each entry defines how to extract a time series from a single OMS table.
        #
        # Fields:
        #   table            - physical table name in oms_production
        #   date_column      - column used as the time axis (aggregated to ds)
        #   value_column     - column aggregated into y
        #   value_func       - SQL aggregate: 'SUM' or 'COUNT'
        #   extra_conditions - list of raw SQL predicates always included in WHERE
        #                      (static business rules, soft-delete guards, status filters)
        #   filters          - dict of col=value pairs; callers may extend at request time
        #                      (e.g. {"company_id": 1} for per-company training)
        #
        # company_id column is present in: orders, transaction_sales, collection,
        # transaction_cn, purchase_order, client_ledgers.
        # Pass {"company_id": <id>} via the filters parameter to scope to one company.
        # Omit it to get data across all companies (collective forecast).

        self.table_configs = {

            # ------------------------------------------------------------------
            # Order value — sum of order_total on placed orders
            # company_id supported: YES
            # ------------------------------------------------------------------
            'orders': {
                'table': 'orders',
                'date_column': 'order_date',
                'value_column': 'order_total',
                'value_func': 'SUM',
                'extra_conditions': ['deleted_at IS NULL'],
                'filters': {}
            },

            # ------------------------------------------------------------------
            # Order count — number of orders placed per period
            # company_id supported: YES
            # ------------------------------------------------------------------
            'order_count': {
                'table': 'orders',
                'date_column': 'order_date',
                'value_column': 'id',
                'value_func': 'COUNT',
                'extra_conditions': ['deleted_at IS NULL'],
                'filters': {}
            },

            # ------------------------------------------------------------------
            # Revenue — sum of billed invoice totals from transaction_sales
            # status=5 (Dispatched) is the billed-sales rule everywhere in the
            # OMS (ChartService, ExcessStockService); 9=Deleted, 4=QC pending.
            # deleted_by is 0 for live rows (never NULL), admin id when deleted
            # company_id supported: YES
            # ------------------------------------------------------------------
            'revenue': {
                'table': 'transaction_sales',
                'date_column': 'bill_date',
                'value_column': 'order_total',
                'value_func': 'SUM',
                'extra_conditions': ['status = 5', 'deleted_by = 0'],
                'filters': {}
            },

            # ------------------------------------------------------------------
            # Collections — cash actually credited to bank (status=17 Credited)
            # company_id supported: YES
            # ------------------------------------------------------------------
            'collections': {
                'table': 'collection',
                'date_column': 'credited_date',
                'value_column': 'amount',
                'value_func': 'SUM',
                'extra_conditions': ['status = 17', 'credited_date IS NOT NULL'],
                'filters': {}
            },

            # ------------------------------------------------------------------
            # Credit notes — billed CN value from the immutable transaction_cn
            # ledger. All rows carry status=1 (active); the 12/13/14 workflow
            # statuses live on credit_note_requests, not here. bill_date is
            # always '0000-00-00' in this table — created_at is the real CN
            # date and is what Laravel uses (Clients ledger, TransactionCn)
            # company_id supported: YES
            # ------------------------------------------------------------------
            'credit_notes': {
                'table': 'transaction_cn',
                'date_column': 'created_at',
                'value_column': 'order_total',
                'value_func': 'SUM',
                'extra_conditions': ['status = 1'],
                'filters': {}
            },

            # ------------------------------------------------------------------
            # Stock outward — units dispatched via order (entity_type=2, stock_type=2)
            # company_id supported: NO (product_stock has no company_id column)
            # ------------------------------------------------------------------
            'stock_outward': {
                'table': 'product_stock',
                'date_column': 'created_at',
                'value_column': 'change_stock',
                'value_func': 'SUM',
                'extra_conditions': ['stock_type = 2', 'entity_type = 2'],
                'filters': {}
            },

            # ------------------------------------------------------------------
            # Purchase orders — quantity raised per period (excludes closed=9)
            # company_id supported: YES
            # ------------------------------------------------------------------
            'purchase_orders': {
                'table': 'purchase_order',
                'date_column': 'created_at',
                'value_column': 'quantity',
                'value_func': 'SUM',
                'extra_conditions': ['status != 9'],
                'filters': {}
            },
        }

    def get_time_series_data(
        self,
        table_key: str,
        start_date: Optional[str] = None,
        end_date: Optional[str] = None,
        filters: Optional[Dict[str, Any]] = None,
        aggregation: Optional[str] = 'daily'
    ) -> List[Dict[str, Any]]:
        """
        Extract time series data from an OMS table.

        Args:
            table_key:   Key in table_configs (e.g. 'revenue', 'collections')
            start_date:  Inclusive lower bound on the date column (YYYY-MM-DD)
            end_date:    Inclusive upper bound on the date column (YYYY-MM-DD)
            filters:     Additional col=value conditions merged at request time.
                         Pass {"company_id": <id>} for per-company scoping.
                         Omit or pass {} for all-companies (collective) data.
            aggregation: 'daily' | 'weekly' | 'monthly'

        Returns:
            List of {"ds": <date_str>, "y": <float>} dicts ready for Prophet.
        """
        try:
            if table_key not in self.table_configs:
                raise ValueError(f"Unknown table configuration: '{table_key}'. "
                                 f"Available: {list(self.table_configs.keys())}")

            # Shallow-copy the config dict; deep-copy the filters dict so per-request
            # filters never mutate the shared table_configs state.
            config = self.table_configs[table_key].copy()
            config['filters'] = {**config.get('filters', {}), **(filters or {})}

            query, params = self._build_query(config, start_date, end_date, aggregation)

            self.logger.info(f"Executing query for '{table_key}': {query} | params={params}")
            results = self.db_service.execute_mysql_query(query, params)

            if not results:
                self.logger.warning(f"No data found for '{table_key}' with the given filters")
                return []

            self.logger.info(f"Retrieved {len(results)} records for '{table_key}'")
            return results

        except Exception as e:
            self.logger.error(f"Error extracting time series data for '{table_key}': {str(e)}")
            raise

    def _build_query(
        self,
        config: Dict[str, Any],
        start_date: Optional[str],
        end_date: Optional[str],
        aggregation: str
    ) -> Tuple[str, Optional[Tuple]]:
        """
        Build a parameterised aggregation query for Prophet input.

        Identifiers (table, columns, aggregate function) come from the whitelisted
        table_configs; all request-supplied values (date bounds, filter values) are
        bound as query parameters, never interpolated.

        Aggregation levels:
            daily   → DATE(date_col)                        GROUP BY DATE(date_col)
            weekly  → Monday of ISO week                    GROUP BY YEARWEEK(date_col)
            monthly → first day of month                    GROUP BY YEAR, MONTH

        Returns:
            (query, params) ready for cursor.execute().
        """
        table      = config['table']
        date_col   = config['date_column']
        value_col  = config['value_column']
        value_func = config.get('value_func', 'SUM')

        if aggregation not in _ALLOWED_AGGREGATIONS:
            raise ValueError(f"Unknown aggregation: '{aggregation}'. "
                             f"Allowed: {list(_ALLOWED_AGGREGATIONS)}")

        if aggregation == 'daily':
            select_clause = f"DATE({date_col}) AS ds, {value_func}({value_col}) AS y"
            group_clause  = f"GROUP BY DATE({date_col})"
        elif aggregation == 'weekly':
            # Group by the same expression as ds so only_full_group_by is satisfied.
            select_clause = (
                f"DATE(DATE_SUB({date_col}, INTERVAL WEEKDAY({date_col}) DAY)) AS ds, "
                f"{value_func}({value_col}) AS y"
            )
            group_clause = f"GROUP BY DATE(DATE_SUB({date_col}, INTERVAL WEEKDAY({date_col}) DAY))"
        else:  # 'monthly' — aggregation already validated above
            select_clause = (
                f"DATE_FORMAT({date_col}, '%Y-%m-01') AS ds, "
                f"{value_func}({value_col}) AS y"
            )
            group_clause = f"GROUP BY DATE_FORMAT({date_col}, '%Y-%m-01')"

        query = f"SELECT {select_clause} FROM {table}"

        where_conditions = []
        params = []

        # Static business-rule predicates baked into the config
        for cond in config.get('extra_conditions', []):
            where_conditions.append(cond)

        # Date range bounds
        if start_date:
            where_conditions.append(f"{date_col} >= %s")
            params.append(start_date)
        if end_date:
            where_conditions.append(f"{date_col} <= %s")
            params.append(end_date)

        # Dynamic per-request filters (e.g. company_id, product_id).
        # Keys must be plain column identifiers; values are always bound.
        for key, value in config.get('filters', {}).items():
            if not _IDENTIFIER_RE.match(str(key)):
                raise ValueError(f"Invalid filter key: '{key}'. "
                                 "Filter keys must be plain column names.")
            if isinstance(value, list):
                if not value:
                    raise ValueError(f"Filter '{key}' received an empty list.")
                placeholders = ', '.join(['%s'] * len(value))
                where_conditions.append(f"{key} IN ({placeholders})")
                params.extend(value)
            else:
                where_conditions.append(f"{key} = %s")
                params.append(value)

        if where_conditions:
            query += " WHERE " + " AND ".join(where_conditions)

        if group_clause:
            query += f" {group_clause}"

        query += " ORDER BY ds"

        return query, tuple(params) if params else None

    def get_available_tables(self) -> List[str]:
        """Return the list of configured table keys."""
        return list(self.table_configs.keys())

    def get_table_info(self, table_key: str) -> Optional[Dict[str, Any]]:
        """Return a copy of the config for a given table key."""
        if table_key in self.table_configs:
            return self.table_configs[table_key].copy()
        return None

    def add_table_config(
        self,
        key: str,
        table: str,
        date_column: str,
        value_column: str,
        value_func: str = 'SUM',
        extra_conditions: Optional[List[str]] = None,
        group_by: Optional[str] = None,
        filters: Optional[Dict[str, Any]] = None
    ):
        """
        Register a custom table configuration at runtime.

        Args:
            key:               Lookup key used in API calls
            table:             Physical table name
            date_column:       Column used as the time axis
            value_column:      Column to aggregate
            value_func:        'SUM' or 'COUNT'
            extra_conditions:  List of raw SQL predicates always added to WHERE
            group_by:          Unused legacy parameter (kept for compatibility)
            filters:           Default col=value filters
        """
        self.table_configs[key] = {
            'table': table,
            'date_column': date_column,
            'value_column': value_column,
            'value_func': value_func,
            'extra_conditions': extra_conditions or [],
            'filters': filters or {}
        }
        self.logger.info(f"Registered table configuration: '{key}' → {table}")

    def validate_table_config(self, table_key: str) -> Tuple[bool, str]:
        """
        Validate that a table key exists and its underlying table is reachable.

        Returns:
            (True, "") on success, (False, reason) on failure.
        """
        try:
            if table_key not in self.table_configs:
                available = list(self.table_configs.keys())
                return False, (f"Table configuration '{table_key}' not found. "
                               f"Available: {available}")

            config = self.table_configs[table_key]
            result = self.db_service.execute_mysql_query(
                f"SELECT COUNT(*) AS cnt FROM {config['table']}"
            )

            if not result:
                return False, f"Table '{config['table']}' does not exist or returned no result"

            return True, ""

        except Exception as e:
            return False, f"Validation error: {str(e)}"

    def get_data_preview(
        self,
        table_key: str,
        limit: int = 10,
        start_date: Optional[str] = None,
        end_date: Optional[str] = None
    ) -> List[Dict[str, Any]]:
        """
        Return a raw (non-aggregated) preview of the most recent rows for a table key.
        extra_conditions from the config are applied so the preview reflects the same
        filtered dataset that would be used for training.

        Args:
            table_key:   Configured table key
            limit:       Number of rows to return (1–100)
            start_date:  Optional lower bound on the date column
            end_date:    Optional upper bound on the date column

        Returns:
            List of {"ds": <value>, "y": <value>} dicts (raw, not aggregated).
        """
        try:
            if table_key not in self.table_configs:
                raise ValueError(f"Unknown table configuration: '{table_key}'")

            config = self.table_configs[table_key]

            where_conditions = list(config.get('extra_conditions', []))
            params = []

            if start_date:
                where_conditions.append(f"{config['date_column']} >= %s")
                params.append(start_date)
            if end_date:
                where_conditions.append(f"{config['date_column']} <= %s")
                params.append(end_date)

            query = (
                f"SELECT {config['date_column']} AS ds, {config['value_column']} AS y "
                f"FROM {config['table']}"
            )

            if where_conditions:
                query += " WHERE " + " AND ".join(where_conditions)

            query += f" ORDER BY {config['date_column']} DESC LIMIT %s"
            params.append(int(limit))

            results = self.db_service.execute_mysql_query(query, tuple(params))
            return results or []

        except Exception as e:
            self.logger.error(f"Error getting data preview for '{table_key}': {str(e)}")
            raise