#!/usr/bin/env bash
# Counts every major migrated dataset on the D7 source, READ-ONLY.
#
# ⚠️ `order_items_migrated` IS THE ONE TO COMPARE D10 AGAINST, not
# `order_items_product` and not `order_items_all`.
#
# Commerce 1 modelled discounts as line items; Commerce 3 has no such concept.
# The 3,047 `commerce_discount` lines were deliberately not migrated - their
# value lives on the order's adjustments instead - but the 154
# `product_discount` lines WERE: they are real purchased courses carrying a zero
# unit price, and dropping them would have emptied four orders. Comparing D10's
# order-item count against `product` alone therefore reports 154 phantom
# "created in D10" rows. See ADMIN_MIGRATION_REPORT.md, Round 4.
#
# Nothing here writes: it is a list of SELECT COUNT(*) statements. It never
# renders a D7 page, so it cannot trip the exposed-form submit handler that
# writes {report_history}.
#
# Run from the D7 directory:
#   bash ../ceonline-d10/scripts/reconcile/d7_dataset_counts.sh > d7_counts.json
set -euo pipefail

q() { ddev mysql --batch --skip-column-names -e "$1" 2>/dev/null | head -1; }

# The migration freeze. Rows created after it are legitimately absent from D10,
# so a raw D7 total overstates what D10 should hold. Every dataset that carries
# a creation timestamp is therefore counted twice: as it stands today, and as it
# stood at the freeze.
FREEZE=1787558508

cat <<JSON
{
  "measured": "$(date -u +%Y-%m-%dT%H:%M:%SZ)",
  "source": "ceonline (Drupal 7), read-only",
  "counts": {
    "users_all":            $(q "SELECT COUNT(*) FROM users"),
    "users_real":           $(q "SELECT COUNT(*) FROM users WHERE uid > 0"),
    "users_active":         $(q "SELECT COUNT(*) FROM users WHERE uid > 0 AND status = 1"),
    "users_blocked":        $(q "SELECT COUNT(*) FROM users WHERE uid > 0 AND status = 0"),
    "roles":                $(q "SELECT COUNT(*) FROM role"),
    "role_permissions":     $(q "SELECT COUNT(*) FROM role_permission"),
    "orders_all":           $(q "SELECT COUNT(*) FROM commerce_order"),
    "orders_admin_list":    $(q "SELECT COUNT(*) FROM commerce_order WHERE status IN ('completed','canceled','pending')"),
    "orders_cart_flag":     $(q "SELECT COUNT(*) FROM commerce_order WHERE status IN ('cart','checkout_checkout','checkout_review')"),
    "order_items_all":      $(q "SELECT COUNT(*) FROM commerce_line_item"),
    "order_items_product":  $(q "SELECT COUNT(*) FROM commerce_line_item WHERE type = 'product'"),
    "order_items_migrated": $(q "SELECT COUNT(*) FROM commerce_line_item WHERE type IN ('product','product_discount')"),
    "products":             $(q "SELECT COUNT(*) FROM commerce_product"),
    "coupons":              $(q "SELECT COUNT(*) FROM commerce_coupon"),
    "payments":             $(q "SELECT COUNT(*) FROM commerce_payment_transaction"),
    "nodes":                $(q "SELECT COUNT(*) FROM node"),
    "nodes_published":      $(q "SELECT COUNT(*) FROM node WHERE status = 1"),
    "files":                $(q "SELECT COUNT(*) FROM file_managed"),
    "taxonomy_terms":       $(q "SELECT COUNT(*) FROM taxonomy_term_data"),
    "quiz_results":         $(q "SELECT COUNT(*) FROM quiz_node_results"),
    "quiz_unevaluated":     $(q "SELECT COUNT(*) FROM quiz_node_results WHERE is_evaluated = 0 AND is_invalid = 0"),
    "quiz_result_answers":  $(q "SELECT COUNT(*) FROM quiz_node_results_answers"),
    "report_history":       $(q "SELECT COUNT(*) FROM report_history"),
    "webform_submissions":  $(q "SELECT COUNT(*) FROM webform_submissions"),
    "webform_evaluations":  $(q "SELECT COUNT(*) FROM webform_submissions WHERE nid = 27"),
    "flaggings":            $(q "SELECT COUNT(*) FROM flagging"),
    "license_audit":        $(q "SELECT COUNT(*) FROM license_number_update"),
    "url_aliases":          $(q "SELECT COUNT(*) FROM url_alias")
  },
  "freeze_epoch": ${FREEZE},
  "at_freeze": {
    "users_real":           $(q "SELECT COUNT(*) FROM users WHERE uid > 0 AND created <= ${FREEZE}"),
    "users_active":         $(q "SELECT COUNT(*) FROM users WHERE uid > 0 AND status = 1 AND created <= ${FREEZE}"),
    "users_blocked":        $(q "SELECT COUNT(*) FROM users WHERE uid > 0 AND status = 0 AND created <= ${FREEZE}"),
    "orders_all":           $(q "SELECT COUNT(*) FROM commerce_order WHERE created <= ${FREEZE}"),
    "order_items_product":  $(q "SELECT COUNT(*) FROM commerce_line_item WHERE type = 'product' AND created <= ${FREEZE}"),
    "order_items_migrated": $(q "SELECT COUNT(*) FROM commerce_line_item WHERE type IN ('product','product_discount') AND created <= ${FREEZE}"),
    "payments":             $(q "SELECT COUNT(*) FROM commerce_payment_transaction WHERE created <= ${FREEZE}"),
    "nodes":                $(q "SELECT COUNT(*) FROM node WHERE created <= ${FREEZE}"),
    "files":                $(q "SELECT COUNT(*) FROM file_managed WHERE timestamp <= ${FREEZE}"),
    "quiz_results":         $(q "SELECT COUNT(*) FROM quiz_node_results WHERE time_start <= ${FREEZE}"),
    "webform_submissions":  $(q "SELECT COUNT(*) FROM webform_submissions WHERE submitted <= ${FREEZE}"),
    "flaggings":            $(q "SELECT COUNT(*) FROM flagging WHERE timestamp <= ${FREEZE}"),
    "quiz_unevaluated":     $(q "SELECT COUNT(*) FROM quiz_node_results WHERE is_evaluated = 0 AND is_invalid = 0 AND time_start <= ${FREEZE}"),
    "report_history":       $(q "SELECT COUNT(*) FROM report_history WHERE h_id <= 779"),
    "quiz_result_answers":  $(q "SELECT COUNT(*) FROM quiz_node_results_answers a JOIN quiz_node_results r ON r.result_id = a.result_id WHERE r.time_start <= ${FREEZE}")
  }
}
JSON