import json
import uuid
from decimal import Decimal, InvalidOperation
from typing import Any, Dict, List, Optional

from sqlalchemy import text

from app.db import engine


_PRODUCT_SCHEMA_READY = False


DEFAULT_PRODUCTS = [
    {
        "sku": "EV-AC-7KW",
        "jasmin_sales_item": "CARREGADOR_MONO_7KW",
        "name": "Carregador EV 7.4kW monofásico",
        "category": "Carregadores",
        "description": "Carregador AC monofásico para moradias e pequenas instalações.",
        "default_unit_price": "590.00",
        "vat_rate": "23.00",
    },
    {
        "sku": "EV-AC-22KW",
        "jasmin_sales_item": "CARREGADOR_TRIF_22KW",
        "name": "Carregador EV 22kW trifásico",
        "category": "Carregadores",
        "description": "Carregador AC trifásico para empresas, condomínios e instalações com maior potência.",
        "default_unit_price": "790.00",
        "vat_rate": "23.00",
    },
    {
        "sku": "CAB-T2-5M",
        "jasmin_sales_item": "CABO_VE",
        "name": "Cabo Type 2 5m",
        "category": "Acessórios",
        "description": "Cabo Type 2 para carregamento de veículos elétricos.",
        "default_unit_price": "120.00",
        "vat_rate": "23.00",
    },
    {
        "sku": "CAB-T2-7M",
        "jasmin_sales_item": "CABO_VE",
        "name": "Cabo Type 2 7m",
        "category": "Acessórios",
        "description": "Cabo Type 2 de 7 metros para carregamento de veículos elétricos.",
        "default_unit_price": "150.00",
        "vat_rate": "23.00",
    },
    {
        "sku": "INST-BASIC",
        "jasmin_sales_item": "INSTALACAO",
        "name": "Instalação básica",
        "category": "Serviços",
        "description": "Serviço de instalação básica sujeito a validação técnica.",
        "default_unit_price": "350.00",
        "vat_rate": "23.00",
    },
    {
        "sku": "SHIP-STD",
        "jasmin_sales_item": "TRANSPORTE",
        "name": "Transporte nacional",
        "category": "Serviços",
        "description": "Envio nacional standard.",
        "default_unit_price": "15.00",
        "vat_rate": "23.00",
    },
]


def _json(value: Any) -> str:
    return json.dumps(value or {}, ensure_ascii=False, default=str)


def _uuid_or_none(value: Optional[str]) -> Optional[str]:
    value = str(value or "").strip()
    return value or None


def _decimal(value: Any, default: str = "0") -> Decimal:
    try:
        if value is None or str(value).strip() == "":
            return Decimal(default)
        return Decimal(str(value).replace(",", ".").strip())
    except (InvalidOperation, ValueError):
        return Decimal(default)


def _money(value: Any) -> str:
    return f"{_decimal(value):.2f}"


def _strict_decimal(value: Any, field_name: str) -> Decimal:
    try:
        if value is None or str(value).strip() == "":
            raise InvalidOperation()
        return Decimal(str(value).replace(",", ".").strip())
    except (InvalidOperation, ValueError):
        raise ValueError(f"{field_name} inválido.")


def _non_negative_decimal(value: Any, field_name: str, default: str = "0") -> Decimal:
    if value is None or str(value).strip() == "":
        value = default
    d = _strict_decimal(value, field_name)
    if d < 0:
        raise ValueError(f"{field_name} não pode ser negativo.")
    return d


def _positive_decimal(value: Any, field_name: str, default: str = "1") -> Decimal:
    if value is None or str(value).strip() == "":
        value = default
    d = _strict_decimal(value, field_name)
    if d <= 0:
        raise ValueError(f"{field_name} deve ser superior a zero.")
    return d


def _bool(value: Any) -> bool:
    return str(value or "").lower() in {"1", "true", "yes", "on", "sim", "ativo"}


def ensure_product_schema(*, seed: bool = True) -> None:
    """Cria catálogo simples de produtos e tabelas de linhas.

    Sem variantes: cada produto/preço configurável fica numa linha em `products`.
    As linhas de oportunidade/encomenda copiam nome, SKU e preço no momento para
    preservar histórico quando o catálogo for alterado.
    """
    global _PRODUCT_SCHEMA_READY
    if _PRODUCT_SCHEMA_READY:
        return

    with engine.begin() as conn:
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS products (
                id UUID PRIMARY KEY,
                sku TEXT UNIQUE NOT NULL,
                jasmin_sales_item TEXT,
                name TEXT NOT NULL,
                category TEXT NOT NULL DEFAULT 'Geral',
                description TEXT,
                default_unit_price NUMERIC(12,2) NOT NULL DEFAULT 0,
                vat_rate NUMERIC(5,2) NOT NULL DEFAULT 23,
                active BOOLEAN NOT NULL DEFAULT TRUE,
                metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
                created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS opportunity_items (
                id UUID PRIMARY KEY,
                opportunity_id UUID NOT NULL REFERENCES opportunities(id) ON DELETE CASCADE,
                product_id UUID REFERENCES products(id) ON DELETE SET NULL,
                sku TEXT,
                jasmin_sales_item TEXT,
                product_name TEXT NOT NULL,
                description TEXT,
                quantity NUMERIC(12,2) NOT NULL DEFAULT 1,
                unit_price NUMERIC(12,2) NOT NULL DEFAULT 0,
                discount_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
                total_price NUMERIC(12,2) NOT NULL DEFAULT 0,
                status TEXT NOT NULL DEFAULT 'INTERESTED',
                metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
                created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS order_items (
                id UUID PRIMARY KEY,
                order_id UUID,
                opportunity_id UUID REFERENCES opportunities(id) ON DELETE SET NULL,
                opportunity_item_id UUID REFERENCES opportunity_items(id) ON DELETE SET NULL,
                product_id UUID REFERENCES products(id) ON DELETE SET NULL,
                sku TEXT,
                jasmin_sales_item TEXT,
                product_name TEXT NOT NULL,
                description TEXT,
                quantity NUMERIC(12,2) NOT NULL DEFAULT 1,
                unit_price NUMERIC(12,2) NOT NULL DEFAULT 0,
                total_price NUMERIC(12,2) NOT NULL DEFAULT 0,
                fulfillment_status TEXT NOT NULL DEFAULT 'PENDING',
                metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
                created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        """))
        for stmt in [
            "ALTER TABLE products ADD COLUMN IF NOT EXISTS jasmin_sales_item TEXT",
            "ALTER TABLE opportunity_items ADD COLUMN IF NOT EXISTS jasmin_sales_item TEXT",
            "ALTER TABLE order_items ADD COLUMN IF NOT EXISTS jasmin_sales_item TEXT",
        ]:
            conn.execute(text(stmt))

        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_products_active ON products(active)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_opportunity_items_opp ON opportunity_items(opportunity_id)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_order_items_opp ON order_items(opportunity_id)"))

        if seed:
            count = conn.execute(text("SELECT count(*) FROM products")).scalar() or 0
            if int(count) == 0:
                for product in DEFAULT_PRODUCTS:
                    conn.execute(text("""
                        INSERT INTO products (
                            id, sku, jasmin_sales_item, name, category, description,
                            default_unit_price, vat_rate, active, metadata
                        ) VALUES (
                            CAST(:id AS UUID), :sku, :jasmin_sales_item, :name, :category, :description,
                            :default_unit_price, :vat_rate, TRUE, CAST(:metadata AS JSONB)
                        )
                        ON CONFLICT (sku) DO NOTHING
                    """), {
                        "id": str(uuid.uuid4()),
                        "sku": product["sku"],
                        "jasmin_sales_item": product.get("jasmin_sales_item"),
                        "name": product["name"],
                        "category": product["category"],
                        "description": product["description"],
                        "default_unit_price": product["default_unit_price"],
                        "vat_rate": product["vat_rate"],
                        "metadata": _json({"seed": True}),
                    })

    _PRODUCT_SCHEMA_READY = True


def list_product_categories() -> List[str]:
    ensure_product_schema()
    with engine.begin() as conn:
        rows = conn.execute(text("""
            SELECT DISTINCT category
            FROM products
            WHERE COALESCE(category, '') <> ''
            ORDER BY category
        """)).all()
    return [str(row[0]) for row in rows]


def list_products(
    *,
    q: Optional[str] = None,
    category: Optional[str] = None,
    active: Optional[str] = "true",
    limit: int = 300,
) -> List[Dict[str, Any]]:
    ensure_product_schema()
    filters = []
    params: Dict[str, Any] = {"limit": int(limit)}
    if q:
        filters.append("(sku ILIKE :q OR COALESCE(jasmin_sales_item, '') ILIKE :q OR name ILIKE :q OR category ILIKE :q OR COALESCE(description, '') ILIKE :q)")
        params["q"] = f"%{str(q).strip()}%"
    if category and category != "all":
        filters.append("category = :category")
        params["category"] = category
    if active in {"true", "false"}:
        filters.append("active = :active")
        params["active"] = active == "true"
    where_sql = "WHERE " + " AND ".join(filters) if filters else ""
    sql = text(f"""
        SELECT
            id::text,
            sku,
            jasmin_sales_item,
            name,
            category,
            description,
            default_unit_price,
            vat_rate,
            active,
            created_at,
            updated_at
        FROM products
        {where_sql}
        ORDER BY active DESC, category ASC, name ASC
        LIMIT :limit
    """)
    with engine.begin() as conn:
        rows = conn.execute(sql, params).mappings().all()
    return [dict(row) for row in rows]


def get_product(product_id: str) -> Optional[Dict[str, Any]]:
    ensure_product_schema()
    sql = text("""
        SELECT
            id::text,
            sku,
            jasmin_sales_item,
            name,
            category,
            description,
            default_unit_price,
            vat_rate,
            active,
            metadata,
            created_at,
            updated_at
        FROM products
        WHERE id = CAST(:product_id AS UUID)
        LIMIT 1
    """)
    with engine.begin() as conn:
        row = conn.execute(sql, {"product_id": product_id}).mappings().first()
    return dict(row) if row else None


def create_product(data: Dict[str, Any]) -> str:
    ensure_product_schema()
    product_id = str(uuid.uuid4())
    sku = str(data.get("sku") or "").strip().upper()
    name = str(data.get("name") or "").strip()
    if not sku:
        raise ValueError("SKU é obrigatório.")
    if not name:
        raise ValueError("Nome do produto é obrigatório.")
    default_unit_price = _non_negative_decimal(data.get("default_unit_price", data.get("price")), "Preço base")
    vat_rate = _non_negative_decimal(data.get("vat_rate", "23"), "IVA")
    with engine.begin() as conn:
        conn.execute(text("""
            INSERT INTO products (
                id, sku, jasmin_sales_item, name, category, description,
                default_unit_price, vat_rate, active, metadata
            ) VALUES (
                CAST(:id AS UUID), :sku, :jasmin_sales_item, :name, :category, :description,
                :default_unit_price, :vat_rate, :active, CAST(:metadata AS JSONB)
            )
        """), {
            "id": product_id,
            "sku": sku,
            "jasmin_sales_item": str(data.get("jasmin_sales_item") or "").strip().upper() or None,
            "name": name,
            "category": str(data.get("category") or "Geral").strip() or "Geral",
            "description": str(data.get("description") or "").strip(),
            "default_unit_price": _money(default_unit_price),
            "vat_rate": _money(vat_rate),
            "active": _bool(data.get("active", "true")),
            "metadata": _json({}),
        })
    return product_id


def update_product(product_id: str, data: Dict[str, Any]) -> bool:
    ensure_product_schema()
    sku = str(data.get("sku") or "").strip().upper()
    name = str(data.get("name") or "").strip()
    if not sku:
        raise ValueError("SKU é obrigatório.")
    if not name:
        raise ValueError("Nome do produto é obrigatório.")
    default_unit_price = _non_negative_decimal(data.get("default_unit_price", data.get("price")), "Preço base")
    vat_rate = _non_negative_decimal(data.get("vat_rate", "23"), "IVA")
    with engine.begin() as conn:
        result = conn.execute(text("""
            UPDATE products
            SET sku = :sku,
                jasmin_sales_item = :jasmin_sales_item,
                name = :name,
                category = :category,
                description = :description,
                default_unit_price = :default_unit_price,
                vat_rate = :vat_rate,
                active = :active,
                updated_at = now()
            WHERE id = CAST(:product_id AS UUID)
        """), {
            "product_id": product_id,
            "sku": sku,
            "jasmin_sales_item": str(data.get("jasmin_sales_item") or "").strip().upper() or None,
            "name": name,
            "category": str(data.get("category") or "Geral").strip() or "Geral",
            "description": str(data.get("description") or "").strip(),
            "default_unit_price": _money(default_unit_price),
            "vat_rate": _money(vat_rate),
            "active": _bool(data.get("active")),
        })
    return result.rowcount > 0


def set_product_active(product_id: str, active: bool) -> bool:
    ensure_product_schema()
    with engine.begin() as conn:
        result = conn.execute(text("""
            UPDATE products
            SET active = :active,
                updated_at = now()
            WHERE id = CAST(:product_id AS UUID)
        """), {"product_id": product_id, "active": bool(active)})
    return result.rowcount > 0


def list_opportunity_items(opportunity_id: str) -> List[Dict[str, Any]]:
    ensure_product_schema()
    sql = text("""
        SELECT
            oi.id::text,
            oi.opportunity_id::text,
            oi.product_id::text,
            oi.sku,
            COALESCE(NULLIF(oi.jasmin_sales_item, ''), p.jasmin_sales_item) AS jasmin_sales_item,
            oi.product_name,
            oi.description,
            oi.quantity,
            oi.unit_price,
            oi.discount_amount,
            oi.total_price,
            oi.status,
            oi.metadata,
            oi.created_at,
            oi.updated_at,
            p.active AS product_active
        FROM opportunity_items oi
        LEFT JOIN products p ON p.id = oi.product_id
        WHERE oi.opportunity_id = CAST(:opportunity_id AS UUID)
        ORDER BY oi.created_at ASC
    """)
    with engine.begin() as conn:
        rows = conn.execute(sql, {"opportunity_id": opportunity_id}).mappings().all()
    return [dict(row) for row in rows]


def _recalculate_opportunity_value(conn, opportunity_id: str) -> None:
    conn.execute(text("""
        UPDATE opportunities
        SET value_amount = COALESCE((
                SELECT SUM(total_price)
                FROM opportunity_items
                WHERE opportunity_id = CAST(:opportunity_id AS UUID)
                  AND status NOT IN ('REJECTED', 'CANCELLED')
            ), 0),
            updated_at = now()
        WHERE id = CAST(:opportunity_id AS UUID)
    """), {"opportunity_id": opportunity_id})


def add_opportunity_item(
    opportunity_id: str,
    *,
    product_id: Optional[str] = None,
    product_name: Optional[str] = None,
    sku: Optional[str] = None,
    jasmin_sales_item: Optional[str] = None,
    description: Optional[str] = None,
    quantity: Any = "1",
    unit_price: Any = None,
    discount_amount: Any = "0",
    status: str = "INTERESTED",
) -> str:
    ensure_product_schema()
    item_id = str(uuid.uuid4())
    product: Optional[Dict[str, Any]] = get_product(product_id) if product_id else None
    name = str(product_name or (product or {}).get("name") or "").strip()
    if not name:
        raise ValueError("Produto é obrigatório.")
    q = _positive_decimal(quantity, "Quantidade", "1")
    price = _non_negative_decimal(unit_price if unit_price not in {None, ""} else (product or {}).get("default_unit_price"), "Preço unitário", "0")
    discount = _non_negative_decimal(discount_amount, "Desconto", "0")
    total = (q * price) - discount
    if total < 0:
        raise ValueError("Total da linha não pode ser negativo.")
    normalized_status = str(status or "INTERESTED").strip().upper()

    with engine.begin() as conn:
        conn.execute(text("""
            INSERT INTO opportunity_items (
                id, opportunity_id, product_id, sku, jasmin_sales_item, product_name, description,
                quantity, unit_price, discount_amount, total_price, status, metadata
            ) VALUES (
                CAST(:id AS UUID), CAST(:opportunity_id AS UUID), CAST(:product_id AS UUID),
                :sku, :jasmin_sales_item, :product_name, :description,
                :quantity, :unit_price, :discount_amount, :total_price, :status, CAST(:metadata AS JSONB)
            )
        """), {
            "id": item_id,
            "opportunity_id": opportunity_id,
            "product_id": _uuid_or_none(product_id),
            "sku": sku or (product or {}).get("sku") or "",
            "jasmin_sales_item": (jasmin_sales_item or (product or {}).get("jasmin_sales_item") or "").strip().upper() or None,
            "product_name": name,
            "description": description if description is not None else (product or {}).get("description") or "",
            "quantity": _money(q),
            "unit_price": _money(price),
            "discount_amount": _money(discount),
            "total_price": _money(total),
            "status": normalized_status,
            "metadata": _json({"source": "admin", "jasmin_sales_item": (jasmin_sales_item or (product or {}).get("jasmin_sales_item") or "").strip().upper()}),
        })
        _recalculate_opportunity_value(conn, opportunity_id)
        conn.execute(text("""
            INSERT INTO opportunity_events (
                id, opportunity_id, event_type, note, payload, created_by
            ) VALUES (
                CAST(:id AS UUID), CAST(:opportunity_id AS UUID), 'opportunity_item_added',
                :note, CAST(:payload AS JSONB), 'operator'
            )
        """), {
            "id": str(uuid.uuid4()),
            "opportunity_id": opportunity_id,
            "note": f"Produto adicionado: {name} x {_money(q)}",
            "payload": _json({"item_id": item_id, "sku": sku or (product or {}).get("sku") or "", "jasmin_sales_item": (jasmin_sales_item or (product or {}).get("jasmin_sales_item") or "").strip().upper()}),
        })
    return item_id


def update_opportunity_item(item_id: str, data: Dict[str, Any]) -> Optional[str]:
    ensure_product_schema()
    q = _positive_decimal(data.get("quantity"), "Quantidade", "1")
    price = _non_negative_decimal(data.get("unit_price"), "Preço unitário", "0")
    discount = _non_negative_decimal(data.get("discount_amount"), "Desconto", "0")
    total = (q * price) - discount
    if total < 0:
        raise ValueError("Total da linha não pode ser negativo.")
    status = str(data.get("status") or "INTERESTED").strip().upper()
    with engine.begin() as conn:
        row = conn.execute(text("""
            UPDATE opportunity_items
            SET jasmin_sales_item = COALESCE(NULLIF(:jasmin_sales_item, ''), jasmin_sales_item),
                quantity = :quantity,
                unit_price = :unit_price,
                discount_amount = :discount_amount,
                total_price = :total_price,
                status = :status,
                updated_at = now()
            WHERE id = CAST(:item_id AS UUID)
            RETURNING opportunity_id::text
        """), {
            "item_id": item_id,
            "jasmin_sales_item": str(data.get("jasmin_sales_item") or "").strip().upper(),
            "quantity": _money(q),
            "unit_price": _money(price),
            "discount_amount": _money(discount),
            "total_price": _money(total),
            "status": status,
        }).first()
        if row:
            _recalculate_opportunity_value(conn, str(row[0]))
            return str(row[0])
    return None


def delete_opportunity_item(item_id: str) -> Optional[str]:
    ensure_product_schema()
    with engine.begin() as conn:
        row = conn.execute(text("""
            DELETE FROM opportunity_items
            WHERE id = CAST(:item_id AS UUID)
            RETURNING opportunity_id::text, product_name
        """), {"item_id": item_id}).first()
        if row:
            opportunity_id = str(row[0])
            _recalculate_opportunity_value(conn, opportunity_id)
            conn.execute(text("""
                INSERT INTO opportunity_events (
                    id, opportunity_id, event_type, note, payload, created_by
                ) VALUES (
                    CAST(:id AS UUID), CAST(:opportunity_id AS UUID), 'opportunity_item_deleted',
                    :note, '{}'::jsonb, 'operator'
                )
            """), {
                "id": str(uuid.uuid4()),
                "opportunity_id": opportunity_id,
                "note": f"Produto removido: {row[1] or 'produto'}",
            })
            return opportunity_id
    return None