#!/usr/bin/env python3
"""Build Metabase dashboard 'Presupuestos Call Center → Facturación'.

5 dashboard filters: fecha (date/range), centro, agente CC, compañía, producto.
3 KPIs × 3 cards each = 9 scalar cards:
  A — Presupuestos Call center      (quote CC → invoice mismo order_id)
  B — Presupuestos CallC regenerados (Q0 CC → Q1 en centro físico → invoice)
  C — Total facturado               (invoice en centros, excluye 159/162)

Métricas por KPI:
  - Total facturado (€)
  - # Facturas
  - Ticket medio (€) = Total / #
"""
import json
import os
import subprocess
import sys

import httpx

API_KEY = subprocess.check_output(["pass", "show", "metabase/aurgi-api-key"], text=True).strip().splitlines()[0]
BASE = "https://reports.autingo.es"
DB_ID = 6
COLLECTION_ID = 559  # "Datos de call center"

# Field IDs (precomputados via /api/table/<id>/query_metadata)
F_INVOICE_CREATED_AT = 16235
F_CENTER_ID = 17327
F_TPVUSER_ID = 17965
F_COMPANY_ID = 17157
F_PRODUCT_ID = 16698

client = httpx.Client(base_url=BASE, headers={"x-api-key": API_KEY}, timeout=120)


# ---------- Template tags ----------
def field_filter_tag(name: str, field_id: int, widget: str, display_name: str):
    return {
        "id": name + "-tag",
        "name": name,
        "display-name": display_name,
        "type": "dimension",
        "dimension": ["field", field_id, None],
        "widget-type": widget,
        "default": None,
    }

# Tag set para A y B (5 filtros)
TAGS_AB = {
    "date":     field_filter_tag("date",     F_INVOICE_CREATED_AT, "date/range",   "Fecha"),
    "centro":   field_filter_tag("centro",   F_CENTER_ID,          "id",           "Centro"),
    "agente":   field_filter_tag("agente",   F_TPVUSER_ID,         "id",           "Agente CC"),
    "compania": field_filter_tag("compania", F_COMPANY_ID,         "id",           "Compañía"),
    "producto": field_filter_tag("producto", F_PRODUCT_ID,         "id",           "Producto"),
}

# Tag set para C (4 filtros — sin agente)
TAGS_C = {k: v for k, v in TAGS_AB.items() if k != "agente"}


# ---------- SQL skeletons ----------
SQL_A = """
WITH
cc_users AS (
  SELECT DISTINCT tpvuser_id AS user_id
  FROM `psql_dcpublic.tpv_authorization_tpvuser_centers`
  WHERE dccenter_id IN (159, 162)
),
filtered AS (
  SELECT DISTINCT
    `psql_dcpublic.tpv_orders_invoice`.id AS invoice_id,
    `psql_dcpublic.tpv_orders_order`.total_cost AS total_cost
  FROM `psql_dcpublic.tpv_orders_quote`
  JOIN cc_users ON `psql_dcpublic.tpv_orders_quote`.created_by_id = cc_users.user_id
  JOIN `psql_dcpublic.tpv_orders_order` ON `psql_dcpublic.tpv_orders_quote`.order_id = `psql_dcpublic.tpv_orders_order`.id
  JOIN `psql_dcpublic.tpv_orders_invoice` ON `psql_dcpublic.tpv_orders_invoice`.order_id = `psql_dcpublic.tpv_orders_order`.id
  LEFT JOIN `psql_dcpublic.tpv_terminals` ON `psql_dcpublic.tpv_orders_order`.terminal_id = `psql_dcpublic.tpv_terminals`.id
  LEFT JOIN `psql_dcpublic.centers` ON `psql_dcpublic.tpv_terminals`.center_id = `psql_dcpublic.centers`.id
  LEFT JOIN `psql_dcpublic.tpv_authorization_tpvuser` ON `psql_dcpublic.tpv_orders_quote`.created_by_id = `psql_dcpublic.tpv_authorization_tpvuser`.id
  LEFT JOIN `psql_dcpublic.tpv_customers` ON `psql_dcpublic.tpv_orders_order`.customer_id = `psql_dcpublic.tpv_customers`.id
  LEFT JOIN `psql_dcpublic.companies` ON `psql_dcpublic.tpv_customers`.company_id = `psql_dcpublic.companies`.id
  LEFT JOIN `psql_dcpublic.tpv_orders_orderitem` ON `psql_dcpublic.tpv_orders_orderitem`.order_id = `psql_dcpublic.tpv_orders_order`.id
  WHERE `psql_dcpublic.tpv_orders_quote`.deleted_at IS NULL
    AND COALESCE(`psql_dcpublic.centers`.id, 0) NOT IN (159, 162)
    [[AND {{date}}]]
    [[AND {{centro}}]]
    [[AND {{agente}}]]
    [[AND {{compania}}]]
    [[AND {{producto}}]]
)
SELECT __AGG__ AS valor FROM filtered
"""

SQL_B = """
WITH
cc_users AS (
  SELECT DISTINCT tpvuser_id AS user_id
  FROM `psql_dcpublic.tpv_authorization_tpvuser_centers`
  WHERE dccenter_id IN (159, 162)
),
q0 AS (
  SELECT q.id AS q0_id, q.order_id AS q0_order, q.created_at AS q0_ts,
         q.created_by_id AS cc_agent_id,
         o.customer_id, o.vehicle_id
  FROM `psql_dcpublic.tpv_orders_quote` q
  JOIN cc_users cc ON q.created_by_id = cc.user_id
  JOIN `psql_dcpublic.tpv_orders_order` o ON q.order_id = o.id
  WHERE q.deleted_at IS NULL
    AND o.customer_id IS NOT NULL AND o.vehicle_id IS NOT NULL
),
qN AS (
  SELECT q.id AS qn_id, q.order_id AS qn_order, q.created_at AS qn_ts,
         o.customer_id, o.vehicle_id, t.center_id
  FROM `psql_dcpublic.tpv_orders_quote` q
  JOIN `psql_dcpublic.tpv_orders_order` o ON q.order_id = o.id
  LEFT JOIN `psql_dcpublic.tpv_terminals` t ON o.terminal_id = t.id
  WHERE q.deleted_at IS NULL
    AND t.center_id IS NOT NULL AND t.center_id NOT IN (159, 162)
),
pairs AS (
  SELECT DISTINCT q0.cc_agent_id, qN.qn_order
  FROM q0
  JOIN qN
    ON q0.customer_id = qN.customer_id
   AND q0.vehicle_id = qN.vehicle_id
   AND qN.qn_ts > q0.q0_ts
   AND qN.qn_ts <= TIMESTAMP_ADD(q0.q0_ts, INTERVAL 60 DAY)
   AND qN.qn_order != q0.q0_order
),
filtered AS (
  SELECT DISTINCT
    `psql_dcpublic.tpv_orders_invoice`.id AS invoice_id,
    `psql_dcpublic.tpv_orders_order`.total_cost AS total_cost
  FROM pairs
  JOIN `psql_dcpublic.tpv_orders_order` ON pairs.qn_order = `psql_dcpublic.tpv_orders_order`.id
  JOIN `psql_dcpublic.tpv_orders_invoice` ON `psql_dcpublic.tpv_orders_invoice`.order_id = `psql_dcpublic.tpv_orders_order`.id
  LEFT JOIN `psql_dcpublic.tpv_terminals` ON `psql_dcpublic.tpv_orders_order`.terminal_id = `psql_dcpublic.tpv_terminals`.id
  LEFT JOIN `psql_dcpublic.centers` ON `psql_dcpublic.tpv_terminals`.center_id = `psql_dcpublic.centers`.id
  LEFT JOIN `psql_dcpublic.tpv_authorization_tpvuser` ON pairs.cc_agent_id = `psql_dcpublic.tpv_authorization_tpvuser`.id
  LEFT JOIN `psql_dcpublic.tpv_customers` ON `psql_dcpublic.tpv_orders_order`.customer_id = `psql_dcpublic.tpv_customers`.id
  LEFT JOIN `psql_dcpublic.companies` ON `psql_dcpublic.tpv_customers`.company_id = `psql_dcpublic.companies`.id
  LEFT JOIN `psql_dcpublic.tpv_orders_orderitem` ON `psql_dcpublic.tpv_orders_orderitem`.order_id = `psql_dcpublic.tpv_orders_order`.id
  WHERE 1=1
    [[AND {{date}}]]
    [[AND {{centro}}]]
    [[AND {{agente}}]]
    [[AND {{compania}}]]
    [[AND {{producto}}]]
)
SELECT __AGG__ AS valor FROM filtered
"""

SQL_C = """
WITH filtered AS (
  SELECT DISTINCT
    `psql_dcpublic.tpv_orders_invoice`.id AS invoice_id,
    `psql_dcpublic.tpv_orders_order`.total_cost AS total_cost
  FROM `psql_dcpublic.tpv_orders_invoice`
  JOIN `psql_dcpublic.tpv_orders_order` ON `psql_dcpublic.tpv_orders_invoice`.order_id = `psql_dcpublic.tpv_orders_order`.id
  LEFT JOIN `psql_dcpublic.tpv_terminals` ON `psql_dcpublic.tpv_orders_order`.terminal_id = `psql_dcpublic.tpv_terminals`.id
  LEFT JOIN `psql_dcpublic.centers` ON `psql_dcpublic.tpv_terminals`.center_id = `psql_dcpublic.centers`.id
  LEFT JOIN `psql_dcpublic.tpv_customers` ON `psql_dcpublic.tpv_orders_order`.customer_id = `psql_dcpublic.tpv_customers`.id
  LEFT JOIN `psql_dcpublic.companies` ON `psql_dcpublic.tpv_customers`.company_id = `psql_dcpublic.companies`.id
  LEFT JOIN `psql_dcpublic.tpv_orders_orderitem` ON `psql_dcpublic.tpv_orders_orderitem`.order_id = `psql_dcpublic.tpv_orders_order`.id
  WHERE COALESCE(`psql_dcpublic.centers`.id, 0) NOT IN (159, 162)
    [[AND {{date}}]]
    [[AND {{centro}}]]
    [[AND {{compania}}]]
    [[AND {{producto}}]]
)
SELECT __AGG__ AS valor FROM filtered
"""

AGG = {
    "total":  "ROUND(SUM(total_cost), 2)",
    "count":  "COUNT(*)",
    "ticket": "ROUND(SAFE_DIVIDE(SUM(total_cost), NULLIF(COUNT(*), 0)), 2)",
}


def make_card(name: str, sql_skel: str, agg_key: str, tags: dict, display: str, currency: bool):
    sql = sql_skel.replace("__AGG__", AGG[agg_key])
    viz = {}
    if currency:
        viz = {
            "column_settings": {
                '["name","valor"]': {
                    "number_style": "currency",
                    "currency": "EUR",
                    "currency_style": "symbol",
                    "currency_in_header": False,
                    "decimals": 0,
                }
            }
        }
    body = {
        "name": name,
        "type": "question",
        "display": display,
        "visualization_settings": viz,
        "dataset_query": {
            "type": "native",
            "database": DB_ID,
            "native": {"query": sql, "template-tags": tags},
        },
        "collection_id": COLLECTION_ID,
        "description": name,
        "result_metadata": None,
    }
    r = client.post("/api/card", json=body)
    if r.status_code >= 400:
        print(r.status_code, r.text[:500])
        r.raise_for_status()
    return r.json()


# ---------- Build 9 cards ----------
specs = [
    # (id, name, skeleton, agg, tags, display, currency)
    ("a_total",  "A · Presupuestos Call center — Total facturado",     SQL_A, "total",  TAGS_AB, "scalar", True),
    ("a_count",  "A · Presupuestos Call center — # Facturas",          SQL_A, "count",  TAGS_AB, "scalar", False),
    ("a_ticket", "A · Presupuestos Call center — Ticket medio",        SQL_A, "ticket", TAGS_AB, "scalar", True),

    ("b_total",  "B · Presupuestos CallC regenerados — Total facturado", SQL_B, "total",  TAGS_AB, "scalar", True),
    ("b_count",  "B · Presupuestos CallC regenerados — # Facturas",      SQL_B, "count",  TAGS_AB, "scalar", False),
    ("b_ticket", "B · Presupuestos CallC regenerados — Ticket medio",    SQL_B, "ticket", TAGS_AB, "scalar", True),

    ("c_total",  "C · Total facturado — Total facturado",  SQL_C, "total",  TAGS_C, "scalar", True),
    ("c_count",  "C · Total facturado — # Facturas",       SQL_C, "count",  TAGS_C, "scalar", False),
    ("c_ticket", "C · Total facturado — Ticket medio",     SQL_C, "ticket", TAGS_C, "scalar", True),
]

cards = {}
for sid, name, skel, agg, tags, display, currency in specs:
    print(f"creating {sid} ... {name}")
    c = make_card(name, skel, agg, tags, display, currency)
    cards[sid] = c
    print(f"  -> id={c['id']}")


# ---------- Build dashboard ----------
print("\ncreating dashboard")
dash = client.post("/api/dashboard", json={
    "name": "Call Center — Presupuestos · KPIs (90d)",
    "collection_id": COLLECTION_ID,
    "description": "A=Quotes CC facturados · B=CallC regenerados · C=Total facturado · Filtros: fecha/centro/agente/compañía/producto",
}).json()
DID = dash["id"]
print(f"  dashboard id={DID}")

# Dashboard-level parameters
DPARAMS = [
    {"id": "p_date",     "name": "Fecha",     "slug": "fecha",    "type": "date/range",                "sectionId": "date"},
    {"id": "p_centro",   "name": "Centro",    "slug": "centro",   "type": "id",                        "sectionId": "id"},
    {"id": "p_agente",   "name": "Agente CC", "slug": "agente",   "type": "id",                        "sectionId": "id"},
    {"id": "p_compania", "name": "Compañía",  "slug": "compania", "type": "id",                        "sectionId": "id"},
    {"id": "p_producto", "name": "Producto",  "slug": "producto", "type": "id",                        "sectionId": "id"},
]

# Parameter mappings: each dashboard param → each card's template-tag target
def mapping_for_card(card_id: int, tag_keys: list[str]):
    return [
        {
            "parameter_id": f"p_{k}",
            "card_id": card_id,
            "target": ["dimension", ["template-tag", k]],
        }
        for k in tag_keys
    ]

# Grid: 24 wide. Columns 0/8/16 = 8 wide. Rows 0/4/8 by KPI band.
DASHCARDS = []
layout = [
    # (sid, row, col, h)
    ("a_total",  0,  0, 4), ("a_count",  0,  8, 4), ("a_ticket",  0, 16, 4),
    ("b_total",  4,  0, 4), ("b_count",  4,  8, 4), ("b_ticket",  4, 16, 4),
    ("c_total",  8,  0, 4), ("c_count",  8,  8, 4), ("c_ticket",  8, 16, 4),
]

neg = -1
for sid, row, col, h in layout:
    card = cards[sid]
    tag_keys = list((TAGS_AB if sid[0] in "ab" else TAGS_C).keys())
    DASHCARDS.append({
        "id": neg,
        "card_id": card["id"],
        "row": row,
        "col": col,
        "size_x": 8,
        "size_y": h,
        "parameter_mappings": mapping_for_card(card["id"], tag_keys),
        "visualization_settings": {},
    })
    neg -= 1

# Update dashboard with params + dashcards
r = client.put(f"/api/dashboard/{DID}", json={
    "parameters": DPARAMS,
    "dashcards": DASHCARDS,
})
if r.status_code >= 400:
    print(r.status_code, r.text[:800])
    r.raise_for_status()

print(f"\nDONE")
print(f"dashboard URL: {BASE}/dashboard/{DID}")
client.close()