#!/usr/bin/env python3
"""B = (A) UNION (regenerados): vuelve la union al SQL de cards B.

Mantiene los field-filters por NAME (multi-select dropdown) ya aplicados en rewire_filters_by_name.
"""
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

F_QUOTE_CREATED_AT = 16588
F_CENTER_NAME = 17330
F_TPVUSER_NAME = 17958
F_COMPANY_NAME = 17158
F_PRODUCT_DESC = 16795

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


def field_filter_tag(name, field_id, widget, display_name):
    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_NAME,      "string/=",        "Centro"),
    "agente":   field_filter_tag("agente",   F_TPVUSER_NAME,     "string/=",        "Agente CC"),
    "compania": field_filter_tag("compania", F_COMPANY_NAME,     "string/=",        "Compañía"),
    "producto": field_filter_tag("producto", F_PRODUCT_DESC,     "string/contains", "Producto"),
}


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`),
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}}]]
),
b_orders AS (
  -- A: orders directos del Q-CC
  SELECT q0_order AS order_id FROM cc_anchored
  UNION DISTINCT
  -- Regenerados: Q1 dentro 60d desde Q0-CC, distinto order, centro NO-CC, mismo cliente+vehiculo
  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
  LEFT JOIN `psql_dcpublic.products` ON `psql_dcpublic.tpv_orders_orderitem`.product_id = `psql_dcpublic.products`.id
  WHERE COALESCE(`psql_dcpublic.centers`.id, 0) NOT IN (159, 162)
    [[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)",
}

CARDS = {
    10251: ("total",  "B · Presupuestos CallC + Regenerados — Total facturado"),
    10252: ("count",  "B · Presupuestos CallC + Regenerados — # Facturas"),
    10253: ("ticket", "B · Presupuestos CallC + Regenerados — Ticket medio"),
}


for cid, (agg, name) in CARDS.items():
    cur = client.get(f"/api/card/{cid}").json()
    viz = cur.get("visualization_settings", {})
    sql = SQL_B.replace("__AGG__", AGG[agg])
    body = {
        "name": name,
        "description": name,
        "dataset_query": {
            "type": "native", "database": DB_ID,
            "native": {"query": sql, "template-tags": TAGS_AB},
        },
        "visualization_settings": viz,
    }
    r = client.put(f"/api/card/{cid}", json=body)
    r.raise_for_status()
    print(f"  card {cid} ({agg}) updated -> name='{name}'")

client.close()
print("done")