{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "36ae91c9",
   "metadata": {},
   "source": [
    "# 02 — 3 KPI principales\n",
    "\n",
    "Por **centro real de facturación** (`tpv_terminals.center_id` del invoice) y ventana temporal:\n",
    "\n",
    "1. **A — Valor facturado de quotes call_center que CONVIRTIERON**  \n",
    "   Quote creado por usuario call_center + existe `tpv_orders_invoice` con el mismo `order_id`. Sumamos `tpv_orders_order.total_cost` o líneas. Centro = centro del invoice.\n",
    "\n",
    "2. **B — Valor facturado total a esos mismos clientes en centros**  \n",
    "   Misma identidad cliente (`customer_id` y/o `vehicle_id` y/o `tlf`+`matricula` normalizados). Todas las facturas del cliente en ese centro en la misma ventana. Debe ser ≥ A.\n",
    "\n",
    "3. **C — Facturación total del centro**  \n",
    "   Suma de invoices del centro en la ventana, todos los clientes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7938921f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "import pandas as pd\n",
    "\n",
    "PROJECT = \"autingo-159109\"\n",
    "DATASET = \"psql_dcpublic\"\n",
    "bq = bigquery.Client(project=PROJECT)\n",
    "\n",
    "WINDOW_DAYS = 90\n",
    "\n",
    "def q(sql):\n",
    "    return bq.query(sql).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7cfa785",
   "metadata": {},
   "source": [
    "## Setup: CTEs base reutilizables\n",
    "\n",
    "Construimos una query maestra con CTEs para A, B, C juntos por centro."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e06df33",
   "metadata": {},
   "outputs": [],
   "source": [
    "SQL_KPI = f\"\"\"\n",
    "DECLARE window_days INT64 DEFAULT {WINDOW_DAYS};\n",
    "DECLARE t_start TIMESTAMP DEFAULT TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL window_days DAY);\n",
    "\n",
    "WITH\n",
    "cc_users AS (\n",
    "  SELECT DISTINCT tpvuser_id AS user_id\n",
    "  FROM `{PROJECT}.{DATASET}.tpv_authorization_tpvuser_centers`\n",
    "  WHERE dccenter_id IN (159, 162)\n",
    "),\n",
    "-- Quotes creados por call_center que TIENEN invoice (convertidos)\n",
    "cc_converted AS (\n",
    "  SELECT\n",
    "    q.id AS quote_id,\n",
    "    q.order_id,\n",
    "    q.created_at AS quote_ts,\n",
    "    o.customer_id,\n",
    "    o.vehicle_id,\n",
    "    o.terminal_id,\n",
    "    t.center_id,\n",
    "    o.total_cost,\n",
    "    i.id AS invoice_id,\n",
    "    i.created_at AS invoice_ts\n",
    "  FROM `{PROJECT}.{DATASET}.tpv_orders_quote` q\n",
    "  JOIN cc_users cc ON q.created_by_id = cc.user_id\n",
    "  JOIN `{PROJECT}.{DATASET}.tpv_orders_order` o ON q.order_id = o.id\n",
    "  JOIN `{PROJECT}.{DATASET}.tpv_orders_invoice` i ON i.order_id = o.id\n",
    "  LEFT JOIN `{PROJECT}.{DATASET}.tpv_terminals` t ON o.terminal_id = t.id\n",
    "  WHERE q.created_at >= t_start\n",
    "    AND q.deleted_at IS NULL\n",
    "),\n",
    "-- Clientes \"tocados\" por call_center (customer_id + vehicle_id)\n",
    "cc_clients AS (\n",
    "  SELECT DISTINCT center_id, customer_id, vehicle_id\n",
    "  FROM cc_converted\n",
    "  WHERE customer_id IS NOT NULL\n",
    "),\n",
    "-- Todas las facturas en la ventana, con centro real\n",
    "all_invoices AS (\n",
    "  SELECT\n",
    "    i.id AS invoice_id,\n",
    "    i.order_id,\n",
    "    i.created_at AS invoice_ts,\n",
    "    o.customer_id,\n",
    "    o.vehicle_id,\n",
    "    o.terminal_id,\n",
    "    t.center_id,\n",
    "    o.total_cost\n",
    "  FROM `{PROJECT}.{DATASET}.tpv_orders_invoice` i\n",
    "  JOIN `{PROJECT}.{DATASET}.tpv_orders_order` o ON i.order_id = o.id\n",
    "  LEFT JOIN `{PROJECT}.{DATASET}.tpv_terminals` t ON o.terminal_id = t.id\n",
    "  WHERE i.created_at >= t_start\n",
    "),\n",
    "-- B: facturas de los mismos clientes en cualquier centro NO call_center\n",
    "client_invoices_in_centers AS (\n",
    "  SELECT ai.*\n",
    "  FROM all_invoices ai\n",
    "  JOIN cc_clients cc ON ai.customer_id = cc.customer_id\n",
    "  WHERE ai.center_id NOT IN (159, 162) -- excluye los propios centros call_center\n",
    "),\n",
    "kpi_a AS (\n",
    "  SELECT center_id,\n",
    "         COUNT(DISTINCT quote_id) AS quotes_cc_convertidos,\n",
    "         COUNT(DISTINCT invoice_id) AS invoices_a,\n",
    "         SUM(total_cost) AS valor_a\n",
    "  FROM cc_converted\n",
    "  WHERE center_id IS NOT NULL\n",
    "  GROUP BY center_id\n",
    "),\n",
    "kpi_b AS (\n",
    "  SELECT center_id,\n",
    "         COUNT(DISTINCT invoice_id) AS invoices_b,\n",
    "         SUM(total_cost) AS valor_b\n",
    "  FROM client_invoices_in_centers\n",
    "  GROUP BY center_id\n",
    "),\n",
    "kpi_c AS (\n",
    "  SELECT center_id,\n",
    "         COUNT(DISTINCT invoice_id) AS invoices_c,\n",
    "         SUM(total_cost) AS valor_c\n",
    "  FROM all_invoices\n",
    "  WHERE center_id IS NOT NULL\n",
    "  GROUP BY center_id\n",
    ")\n",
    "SELECT\n",
    "  c.id AS center_id,\n",
    "  c.name AS center_name,\n",
    "  COALESCE(a.quotes_cc_convertidos, 0) AS quotes_cc_convertidos,\n",
    "  ROUND(COALESCE(a.valor_a, 0), 2) AS A_valor_quote_cc_convertido,\n",
    "  ROUND(COALESCE(b.valor_b, 0), 2) AS B_valor_mismo_cliente_centro,\n",
    "  ROUND(COALESCE(c2.valor_c, 0), 2) AS C_valor_total_centro,\n",
    "  ROUND(SAFE_DIVIDE(COALESCE(a.valor_a, 0), c2.valor_c), 4) AS A_sobre_C,\n",
    "  ROUND(SAFE_DIVIDE(COALESCE(b.valor_b, 0), c2.valor_c), 4) AS B_sobre_C\n",
    "FROM `{PROJECT}.{DATASET}.centers` c\n",
    "LEFT JOIN kpi_a a ON c.id = a.center_id\n",
    "LEFT JOIN kpi_b b ON c.id = b.center_id\n",
    "LEFT JOIN kpi_c c2 ON c.id = c2.center_id\n",
    "WHERE c.id NOT IN (159, 162)\n",
    "  AND COALESCE(c2.valor_c, 0) > 0\n",
    "ORDER BY C_valor_total_centro DESC\n",
    "\"\"\"\n",
    "\n",
    "df = q(SQL_KPI)\n",
    "print(f\"Centros con actividad ({WINDOW_DAYS}d): {len(df)}\")\n",
    "df.head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "724baf5c",
   "metadata": {},
   "source": [
    "## Totales globales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75c3297e",
   "metadata": {},
   "outputs": [],
   "source": [
    "totals = df[[\"A_valor_quote_cc_convertido\", \"B_valor_mismo_cliente_centro\", \"C_valor_total_centro\"]].sum()\n",
    "print(totals.to_string())\n",
    "print()\n",
    "print(f\"A/C global: {totals.A_valor_quote_cc_convertido / totals.C_valor_total_centro:.4f}\")\n",
    "print(f\"B/C global: {totals.B_valor_mismo_cliente_centro / totals.C_valor_total_centro:.4f}\")\n",
    "print(f\"Lift B vs A: {totals.B_valor_mismo_cliente_centro / totals.A_valor_quote_cc_convertido:.2f}x\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fba60db",
   "metadata": {},
   "source": [
    "## Top 15 centros por A (valor traído por call_center)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f90abe7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "top = df.sort_values(\"A_valor_quote_cc_convertido\", ascending=False).head(15)\n",
    "fig, ax = plt.subplots(figsize=(10, 6))\n",
    "x = range(len(top))\n",
    "ax.barh(x, top.A_valor_quote_cc_convertido, label=\"A (cc → factura)\")\n",
    "ax.barh(x, top.B_valor_mismo_cliente_centro - top.A_valor_quote_cc_convertido,\n",
    "        left=top.A_valor_quote_cc_convertido, label=\"B−A (mismo cliente extra)\")\n",
    "ax.set_yticks(x)\n",
    "ax.set_yticklabels(top.center_name)\n",
    "ax.invert_yaxis()\n",
    "ax.set_xlabel(\"€ facturados\")\n",
    "ax.legend()\n",
    "ax.set_title(f\"Top 15 centros — quotes call_center ({WINDOW_DAYS}d)\")\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}