-- ===========================================================================
-- Synchron Permits → HeavyHaul Agent : STEP 1, DISCOVERY (read-only)
--
-- Run against synchrontms_app_prod (MariaDB 10.6) with a READ-ONLY user.
-- Nothing here writes. Save each result set; they are the input to the
-- field-mapping document, which cannot be written without them.
--
-- Why this exists: the structure dump we were given has 0 rows. od_orders
-- carries almost no trip detail — the real payload lives in od_order_meta,
-- a key/value (EAV) table with ~10.3M rows. The key NAMES are data, not
-- schema, so they are absent from the dump. Until we have them we cannot
-- map origin, destination, commodity, dimensions, weight, VIN or unit.
-- ===========================================================================

-- --- 0. Find PAN LOGISTICS -------------------------------------------------
SELECT id, name, mc, dot, billing_company_name, billing_email,
       billing_city, billing_state, created_at
FROM carriers
WHERE name LIKE '%PAN%' OR billing_company_name LIKE '%PAN%';
-- Record the id. Everything below uses @carrier_id.

SET @carrier_id := 0;  -- <<< paste the PAN LOGISTICS id here before continuing

-- --- 1. How big is this import, really? ------------------------------------
SELECT o.status,
       YEAR(COALESCE(o.order_created_date, o.created_at)) AS yr,
       COUNT(*) AS orders
FROM od_orders o
WHERE o.carrier_id = @carrier_id
GROUP BY o.status, yr
ORDER BY yr DESC, orders DESC;

-- Permits (items) and routes for the same scope.
SELECT COUNT(DISTINCT o.id)  AS orders,
       COUNT(DISTINCT i.id)  AS items,
       COUNT(DISTINCT r.id)  AS routes,
       COUNT(DISTINCT t.id)  AS route_tokens
FROM od_orders o
LEFT JOIN od_order_items i      ON i.order_id = o.id
LEFT JOIN od_item_routes r      ON r.item_id  = i.id
LEFT JOIN od_item_route_tokens t ON t.item_id = i.id
WHERE o.carrier_id = @carrier_id
  AND o.status = 'Completed'
  AND YEAR(COALESCE(o.order_created_date, o.created_at)) = 2026;

-- --- 2. THE IMPORTANT ONE: what keys exist in od_order_meta? ---------------
-- This is the blocker. Without it there is no field mapping.
SELECT m.`key`,
       COUNT(*)                     AS occurrences,
       COUNT(DISTINCT m.order_id)   AS orders_using_it,
       MIN(CHAR_LENGTH(m.value))    AS min_len,
       MAX(CHAR_LENGTH(m.value))    AS max_len,
       SUBSTRING(MAX(m.value), 1, 120) AS sample_value
FROM od_order_meta m
JOIN od_orders o ON o.id = m.order_id
WHERE o.carrier_id = @carrier_id
GROUP BY m.`key`
ORDER BY orders_using_it DESC, occurrences DESC;

-- Same for item-level meta.
SELECT im.`key`,
       COUNT(*)                      AS occurrences,
       COUNT(DISTINCT im.item_id)    AS items_using_it,
       MAX(CHAR_LENGTH(im.value))    AS max_len,
       SUBSTRING(MAX(im.value), 1, 120) AS sample_value
FROM od_item_meta im
JOIN od_order_items i ON i.id = im.item_id
JOIN od_orders o      ON o.id = i.order_id
WHERE o.carrier_id = @carrier_id
GROUP BY im.`key`
ORDER BY items_using_it DESC;

-- --- 3. One complete order, end to end -------------------------------------
-- Pick a single completed 2026 PAN order and read EVERYTHING about it.
-- Compare the output against the order-details PDF for the same order to
-- confirm the mapping is right before writing any importer code.
SET @order_id := 0;  -- <<< paste one completed PAN order id here

SELECT * FROM od_orders       WHERE id = @order_id \G
SELECT `key`, value FROM od_order_meta WHERE order_id = @order_id ORDER BY `key`;
SELECT i.*, s.state_short_name
  FROM od_order_items i
  LEFT JOIN all_states s ON s.id = i.state_id
 WHERE i.order_id = @order_id;
SELECT im.item_id, im.`key`, im.value
  FROM od_item_meta im
  JOIN od_order_items i ON i.id = im.item_id
 WHERE i.order_id = @order_id ORDER BY im.item_id, im.`key`;
SELECT r.* FROM od_item_routes r
  JOIN od_order_items i ON i.id = r.item_id WHERE i.order_id = @order_id;
SELECT t.item_id, t.route_token, SUBSTRING(t.texts, 1, 300) AS texts_sample
  FROM od_item_route_tokens t
  JOIN od_order_items i ON i.id = t.item_id WHERE i.order_id = @order_id;

-- --- 4. Where are the permit PDFs? -----------------------------------------
-- `media` stores only a file NAME plus a polymorphic owner. We need the
-- storage root (local disk path or S3 bucket + prefix) from Synchron before
-- any file can be fetched.
SELECT model_type, COUNT(*) AS rows_, MIN(name) AS sample_name
FROM media GROUP BY model_type ORDER BY rows_ DESC;

-- Media attached to this carrier's order items.
SELECT md.id, md.model_type, md.model_id, md.name, md.created_at
FROM media md
JOIN od_order_items i ON i.id = md.model_id
JOIN od_orders o      ON o.id = i.order_id
WHERE o.carrier_id = @carrier_id
  AND md.model_type LIKE '%Item%'
LIMIT 50;

-- --- 5. People and companies on these orders -------------------------------
SELECT DISTINCT
       u.id AS user_id, u.name, u.last_name, u.email, u.phone,
       u.email_verified_at, u.status
FROM od_orders o
JOIN users u ON u.id = o.user_id
WHERE o.carrier_id = @carrier_id;

SELECT DISTINCT b.id, b.broker_name, b.broker_mc, b.dot, b.broker_email, b.broker_phone
FROM od_orders o
JOIN broker_infos b ON b.id = o.broker_id
WHERE o.carrier_id = @carrier_id;

SELECT cc.* FROM carrier_contacts cc WHERE cc.carrier_id = @carrier_id;

-- Drivers / trucks / trailers referenced by these orders.
SELECT DISTINCT o.driver_id, o.truck, o.trailer, o.client_id, o.agent_id
FROM od_orders o WHERE o.carrier_id = @carrier_id;

-- --- 6. State knowledge (sync separately, weekly — not per permit) ---------
-- NOTE: all_states also has `username` and `password` columns holding state
-- DOT portal credentials. NEVER select or export those. Columns listed
-- explicitly below for exactly that reason.
SELECT id, state_short_name, state_name, provision, updated_at
FROM all_states ORDER BY state_short_name;

SELECT all_state_id, COUNT(*) AS notes, MAX(updated_at) AS last_updated
FROM all_states_notes WHERE status = 1 GROUP BY all_state_id;