#!/usr/bin/env python3
"""Dashboard v2 — coherencia:
  - Date filter en `tpv_orders_quote.created_at` (creacion de presupuesto) para A B C.
  - A = quotes CC -> factura (mismo order_id).
  - B = (A) ∪ (quotes regenerados en centro fisico desde Q0 CC dentro 60d) -> facturas.
  - C = invoices de centros fisicos cuyo order tiene AL MENOS un quote en la ventana
       (98.7% de invoices tienen quote, solo cae 3.7% del valor — aceptable).
"""
import subprocess
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

F_QUOTE_CREATED_AT = 16588
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=180)


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,
    }

TAGS_AB = {
    "date":     field_filter_tag("date",     F_QUOTE_CREATED_AT, "date/range",   "Fecha presupuesto"),
    "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"),
}
TAGS_C = {k: v for k, v in TAGS_AB.items() if k != "agente"}


# ---- SQL ----
# Convention: tabla cuya columna se filtra via field-filter NO lleva alias.
# Demas tablas pueden usar alias o full ref.

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
"""

# B: union de Q-CC directos + Q1 regenerados (desde Q0-CC dentro 60d).
# Q1 vive en una CTE clon de la tabla quote para no chocar con field-filter de Q-CC.
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)
),
quotes_q1 AS (
  SELECT id, order_id, created_at, deleted_at
  FROM `psql_dcpublic.tpv_orders_quote`
),
orders_q1 AS (
  SELECT id, customer_id, vehicle_id, terminal_id
  FROM `psql_dcpublic.tpv_orders_order`
),
terminals_q1 AS (
  SELECT id, center_id FROM `psql_dcpublic.tpv_terminals`
),
-- Q0 (CC) anchor; field-filters (date/agente) aplican sobre la quote CC
cc_anchored AS (
  SELECT
    `psql_dcpublic.tpv_orders_quote`.id AS q0_id,
    `psql_dcpublic.tpv_orders_quote`.order_id AS q0_order,
    `psql_dcpublic.tpv_orders_quote`.created_at AS q0_ts,
    `psql_dcpublic.tpv_orders_quote`.created_by_id AS cc_agent_id,
    `psql_dcpublic.tpv_orders_order`.customer_id AS cust_id,
    `psql_dcpublic.tpv_orders_order`.vehicle_id AS veh_id
  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
  LEFT JOIN `psql_dcpublic.tpv_authorization_tpvuser` ON `psql_dcpublic.tpv_orders_quote`.created_by_id = `psql_dcpublic.tpv_authorization_tpvuser`.id
  WHERE `psql_dcpublic.tpv_orders_quote`.deleted_at IS NULL
    [[AND {{date}}]]
    [[AND {{agente}}]]
),
-- Orders incluidos: Q-CC directos + Q1 regenerados
b_orders AS (
  SELECT q0_order AS order_id FROM cc_anchored
  UNION DISTINCT
  SELECT q1.order_id AS order_id
  FROM cc_anchored a
  JOIN quotes_q1 q1
    ON q1.deleted_at IS NULL
   AND q1.created_at > a.q0_ts
   AND q1.created_at <= TIMESTAMP_ADD(a.q0_ts, INTERVAL 60 DAY)
   AND q1.order_id != a.q0_order
  JOIN orders_q1 o1 ON q1.order_id = o1.id
  LEFT JOIN terminals_q1 t1 ON o1.terminal_id = t1.id
  WHERE o1.customer_id = a.cust_id
    AND o1.vehicle_id  = a.veh_id
    AND t1.center_id IS NOT NULL
    AND t1.center_id NOT 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 b_orders
  JOIN `psql_dcpublic.tpv_orders_order` ON b_orders.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_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 {{centro}}]]
    [[AND {{compania}}]]
    [[AND {{producto}}]]
)
SELECT __AGG__ AS valor FROM filtered
"""

# C: total facturado en centros fisicos cuyos orders tienen al menos un quote en la ventana
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_quote`
  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_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 {{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, sql_skel, agg_key, tags, display, currency):
    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()


specs = [
    ("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']}")

print("\ncreating dashboard v2")
dash = client.post("/api/dashboard", json={
    "name": "Call Center — Presupuestos · KPIs (v2)",
    "collection_id": COLLECTION_ID,
    "description": "A=Quotes CC · B=CC + Regenerados · C=Total facturado (orders con quote). Filtros: fecha-presupuesto/centro/agente/compañía/producto",
}).json()
DID = dash["id"]
print(f"  dashboard id={DID}")

DPARAMS = [
    {"id": "p_date",     "name": "Fecha presupuesto", "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"},
]

def mapping_for_card(card_id, tag_keys):
    return [{"parameter_id": f"p_{k}", "card_id": card_id,
             "target": ["dimension", ["template-tag", k]]} for k in tag_keys]

DASHCARDS = []
layout = [
    ("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

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()