WITH scopes AS (
  SELECT 838257186181189 tenant_id,1 factory_id,'A' tenant_code
  UNION ALL SELECT 838257212780613,1,'B'
  UNION ALL SELECT 838257237606469,1,'DEMO'
), expected AS (
  SELECT s.tenant_id,s.factory_id,s.tenant_code,m.ModuleCode module_code,m.MetricCode metric_code
  FROM scopes s
  JOIN ado_smart_ops_kpi_master m ON m.TenantId=s.tenant_id
  WHERE m.MetricLevel=1 AND m.IsEnabled=1 AND m.ModuleCode IN ('S1','S2','S3','S4','S5','S6','S7','S9')
), ranked AS (
  SELECT v.*,ROW_NUMBER() OVER (
    PARTITION BY v.tenant_id,v.factory_id,v.metric_code
    ORDER BY v.biz_date DESC,v.calc_time DESC,v.id DESC
  ) rn
  FROM ado_s9_kpi_value_l1_day v
  JOIN scopes s ON s.tenant_id=v.tenant_id AND s.factory_id=v.factory_id
  WHERE v.is_deleted=0
)
SELECT e.tenant_code,e.module_code,
       COUNT(*) expected_metrics,
       SUM(r.metric_value IS NOT NULL) metrics_with_value,
       GROUP_CONCAT(CASE WHEN r.metric_value IS NULL THEN e.metric_code END ORDER BY e.metric_code) missing_metrics,
       MAX(r.biz_date) max_biz_date
FROM expected e
LEFT JOIN ranked r ON r.tenant_id=e.tenant_id AND r.factory_id=e.factory_id
  AND r.metric_code=e.metric_code AND r.rn=1
GROUP BY e.tenant_code,e.module_code
ORDER BY FIELD(e.tenant_code,'A','B','DEMO'),e.module_code;

SELECT tenant_id,metric_code,metric_value,biz_date,calc_time
FROM ado_s9_kpi_value_l1_day
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
  AND module_code='S6' AND is_deleted=0
ORDER BY tenant_id,metric_code,biz_date DESC;

SELECT tenant_id,factory_id,status,COUNT(*) row_count,MIN(created_at) min_created,MAX(created_at) max_created
FROM ado_s8_exception
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469) AND is_deleted=0
GROUP BY tenant_id,factory_id,status
ORDER BY tenant_id,status;

SELECT TenantId,COUNT(*) plan_count,
       SUM(status IN ('EXECUTING','VERIFYING','CLOSED')) active_or_done
FROM ado_smart_ops_improvement_plan
WHERE TenantId IN (838257186181189,838257212780613,838257237606469)
GROUP BY TenantId;

SELECT tenant_id,COUNT(*) po_line_count
FROM PurOrdDetail
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
GROUP BY tenant_id;

SELECT tenant_id,COUNT(*) receipt_line_count,SUM(RctDate IS NOT NULL) dated_receipts
FROM PurOrdRctDetail
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
GROUP BY tenant_id;

SELECT COLUMN_NAME,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='PurOrdRctDetail'
  AND IS_NULLABLE='NO' AND COLUMN_DEFAULT IS NULL
ORDER BY ORDINAL_POSITION;

SELECT COLUMN_NAME,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='PurOrdRctMaster'
  AND IS_NULLABLE='NO' AND COLUMN_DEFAULT IS NULL
ORDER BY ORDINAL_POSITION;