"""Per-business minute allocation history and incremental top-ups."""

from __future__ import annotations

import math
from decimal import Decimal, InvalidOperation
from typing import Any, Dict, List, Optional, Tuple

from usage_allocation_util import get_monthly_minute_limit


def _column_exists(cursor, table_name: str, column_name: str) -> bool:
    cursor.execute(
        """
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = %s
          AND column_name = %s
        LIMIT 1
        """,
        (table_name, column_name),
    )
    return cursor.fetchone() is not None


def _table_exists(cursor, table_name: str) -> bool:
    cursor.execute(
        """
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = %s
        LIMIT 1
        """,
        (table_name,),
    )
    return cursor.fetchone() is not None


def ensure_allocation_billing_columns(cursor) -> None:
    if not _column_exists(cursor, "pca_business_allocations", "default_cost_per_minute"):
        cursor.execute(
            """
            ALTER TABLE pca_business_allocations
            ADD COLUMN default_cost_per_minute DECIMAL(12, 4) NULL
            """
        )


def ensure_allocation_validity_columns(cursor) -> None:
    if not _column_exists(cursor, "pca_business_allocations", "validity_months"):
        cursor.execute(
            """
            ALTER TABLE pca_business_allocations
            ADD COLUMN validity_months SMALLINT NULL DEFAULT 0
            """
        )
    if not _column_exists(cursor, "pca_business_allocations", "usage_alert_sent_at"):
        cursor.execute(
            """
            ALTER TABLE pca_business_allocations
            ADD COLUMN usage_alert_sent_at DATETIME NULL DEFAULT NULL
            """
        )
    if not _column_exists(cursor, "pca_business_allocations", "validity_alert_sent_at"):
        cursor.execute(
            """
            ALTER TABLE pca_business_allocations
            ADD COLUMN validity_alert_sent_at DATETIME NULL DEFAULT NULL
            """
        )


def _ensure_history_billing_columns(cursor) -> None:
    if not _column_exists(cursor, "pca_allocation_history", "amount_paid"):
        cursor.execute(
            """
            ALTER TABLE pca_allocation_history
            ADD COLUMN amount_paid DECIMAL(14, 2) NULL,
            ADD COLUMN cost_per_minute DECIMAL(12, 4) NULL,
            ADD COLUMN currency VARCHAR(8) NULL DEFAULT 'INR'
            """
        )


def ensure_allocation_history_table(cursor) -> None:
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS pca_allocation_history (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            bid VARCHAR(64) NOT NULL,
            minutes_added INT NOT NULL,
            total_before INT NOT NULL DEFAULT 0,
            total_after INT NOT NULL DEFAULT 0,
            action_type VARCHAR(32) NOT NULL DEFAULT 'add',
            amount_paid DECIMAL(14, 2) NULL,
            cost_per_minute DECIMAL(12, 4) NULL,
            currency VARCHAR(8) NULL DEFAULT 'INR',
            notes TEXT NULL,
            created_by VARCHAR(255) NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_allocation_history_bid_created (bid, created_at DESC)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """
    )
    _ensure_history_billing_columns(cursor)
    ensure_allocation_billing_columns(cursor)
    ensure_allocation_validity_columns(cursor)


def compute_minutes_from_payment(amount_paid: float, cost_per_minute: float) -> int:
    if amount_paid <= 0 or cost_per_minute <= 0:
        raise ValueError("amount_paid and cost_per_minute must be positive")
    return int(math.floor(float(amount_paid) / float(cost_per_minute)))


def resolve_allocation_minutes(payload: Dict[str, Any]) -> Tuple[int, Optional[float], Optional[float], str]:
    """Resolve minutes from direct count or amount ÷ rate (floored)."""
    currency = str(payload.get("currency") or "INR").strip() or "INR"
    amount_raw = payload.get("amount_paid")
    rate_raw = payload.get("cost_per_minute")
    has_amount = amount_raw is not None and str(amount_raw).strip() != ""
    has_rate = rate_raw is not None and str(rate_raw).strip() != ""

    if has_amount or has_rate:
        if not (has_amount and has_rate):
            raise ValueError("amount_paid and cost_per_minute must both be provided")
        try:
            amount_paid = float(Decimal(str(amount_raw)))
            cost_per_minute = float(Decimal(str(rate_raw)))
        except (InvalidOperation, TypeError, ValueError) as exc:
            raise ValueError("amount_paid and cost_per_minute must be valid numbers") from exc
        minutes = compute_minutes_from_payment(amount_paid, cost_per_minute)
        if minutes <= 0:
            raise ValueError("Payment amount is too small for the given rate")
        return minutes, amount_paid, cost_per_minute, currency

    minutes_to_add = int(payload.get("minutes_to_add") or payload.get("minutes") or 0)
    if minutes_to_add <= 0:
        raise ValueError("Provide minutes_to_add or amount_paid with cost_per_minute")
    return minutes_to_add, None, None, currency


def _upsert_allocation_row(
    cursor,
    bid: str,
    new_limit: int,
    notes: Optional[str],
    updated_by: Optional[str],
    *,
    default_cost_per_minute: Optional[float] = None,
    validity_months: Optional[int] = None,
) -> None:
    ensure_allocation_billing_columns(cursor)
    ensure_allocation_validity_columns(cursor)
    cursor.execute(
        """
        INSERT INTO pca_business_allocations (
            bid, monthly_call_limit, monthly_minute_limit, notes, updated_by,
            default_cost_per_minute, validity_months
        )
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            monthly_call_limit = VALUES(monthly_call_limit),
            monthly_minute_limit = VALUES(monthly_minute_limit),
            notes = COALESCE(VALUES(notes), notes),
            updated_by = VALUES(updated_by),
            default_cost_per_minute = COALESCE(VALUES(default_cost_per_minute), default_cost_per_minute),
            validity_months = COALESCE(VALUES(validity_months), validity_months)
        """,
        (
            str(bid),
            int(new_limit),
            int(new_limit),
            notes,
            updated_by,
            default_cost_per_minute,
            validity_months,
        ),
    )


def _insert_history_row(
    cursor,
    bid: str,
    *,
    minutes_added: int,
    total_before: int,
    total_after: int,
    action_type: str,
    notes: Optional[str],
    created_by: Optional[str],
    amount_paid: Optional[float] = None,
    cost_per_minute: Optional[float] = None,
    currency: Optional[str] = None,
) -> int:
    cursor.execute(
        """
        INSERT INTO pca_allocation_history
        (bid, minutes_added, total_before, total_after, action_type,
         amount_paid, cost_per_minute, currency, notes, created_by)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """,
        (
            str(bid),
            int(minutes_added),
            int(total_before),
            int(total_after),
            str(action_type or "add"),
            amount_paid,
            cost_per_minute,
            currency or "INR",
            notes,
            created_by,
        ),
    )
    return int(cursor.lastrowid or 0)


def get_allocation_history(cursor, bid: str, *, limit: int = 100) -> List[Dict[str, Any]]:
    if not _table_exists(cursor, "pca_allocation_history"):
        return []
    safe_limit = max(1, min(int(limit or 100), 500))
    cursor.execute(
        """
        SELECT
            id,
            bid,
            minutes_added,
            total_before,
            total_after,
            action_type,
            amount_paid,
            cost_per_minute,
            currency,
            notes,
            created_by,
            created_at
        FROM pca_allocation_history
        WHERE bid = %s
        ORDER BY created_at DESC, id DESC
        LIMIT %s
        """,
        (str(bid), safe_limit),
    )
    return list(cursor.fetchall() or [])


def get_latest_allocation_event(cursor, bid: str) -> Optional[Dict[str, Any]]:
    rows = get_allocation_history(cursor, bid, limit=1)
    return rows[0] if rows else None


def add_allocation_minutes(
    cursor,
    bid: str,
    minutes_to_add: int,
    *,
    notes: Optional[str] = None,
    created_by: Optional[str] = None,
    amount_paid: Optional[float] = None,
    cost_per_minute: Optional[float] = None,
    currency: Optional[str] = "INR",
    save_default_rate: bool = True,
) -> Dict[str, Any]:
    ensure_allocation_history_table(cursor)
    delta = int(minutes_to_add or 0)
    if delta <= 0:
        raise ValueError("minutes_to_add must be a positive integer")

    total_before = get_monthly_minute_limit(cursor, bid)
    total_after = total_before + delta
    default_rate = cost_per_minute if save_default_rate and cost_per_minute is not None else None
    _upsert_allocation_row(
        cursor,
        bid,
        total_after,
        notes,
        created_by,
        default_cost_per_minute=default_rate,
    )
    history_id = _insert_history_row(
        cursor,
        bid,
        minutes_added=delta,
        total_before=total_before,
        total_after=total_after,
        action_type="add",
        notes=notes,
        created_by=created_by,
        amount_paid=amount_paid,
        cost_per_minute=cost_per_minute,
        currency=currency,
    )
    return {
        "bid": str(bid),
        "minutes_added": delta,
        "amount_paid": amount_paid,
        "cost_per_minute": cost_per_minute,
        "currency": currency or "INR",
        "total_before": total_before,
        "total_after": total_after,
        "monthly_minute_limit": total_after,
        "history_id": history_id,
    }


def set_allocation_minutes(
    cursor,
    bid: str,
    new_limit: int,
    *,
    notes: Optional[str] = None,
    updated_by: Optional[str] = None,
    validity_months: Optional[int] = None,
) -> Dict[str, Any]:
    ensure_allocation_history_table(cursor)
    total_before = get_monthly_minute_limit(cursor, bid)
    total_after = max(0, int(new_limit or 0))
    delta = total_after - total_before

    _upsert_allocation_row(
        cursor, bid, total_after, notes, updated_by, validity_months=validity_months
    )
    history_id = 0
    if delta != 0:
        history_id = _insert_history_row(
            cursor,
            bid,
            minutes_added=delta,
            total_before=total_before,
            total_after=total_after,
            action_type="set",
            notes=notes,
            created_by=updated_by,
        )
    elif notes is not None or validity_months is not None:
        cursor.execute(
            """
            UPDATE pca_business_allocations
            SET notes = COALESCE(%s, notes), updated_by = %s
            WHERE bid = %s
            """,
            (notes, updated_by, str(bid)),
        )

    cursor.execute(
        "SELECT validity_months FROM pca_business_allocations WHERE bid = %s LIMIT 1",
        (str(bid),),
    )
    row = cursor.fetchone() or {}

    return {
        "bid": str(bid),
        "minutes_added": delta,
        "total_before": total_before,
        "total_after": total_after,
        "monthly_minute_limit": total_after,
        "history_id": history_id,
        "validity_months": int(row.get("validity_months") or 0),
    }