-- Step 37 verification: customer user account foundation
-- Safe to run on hosting dev/prod after docs/step-37-add-customer-user-accounts.sql.
-- This script is read-only.

SELECT
  TABLE_NAME,
  TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    'customerUsers',
    'customerUserSessions',
    'customerUserAuthChallenges',
    'customerUserSecurityEvents'
  )
ORDER BY TABLE_NAME;

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  COLUMN_TYPE,
  IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('onlineOrders', 'reservations', 'sessionCustomers')
  AND COLUMN_NAME = 'customerUserId'
ORDER BY TABLE_NAME;

SELECT
  TABLE_NAME,
  INDEX_NAME,
  GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS indexedColumns
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND INDEX_NAME IN (
    'idx_onlineOrders_customer_time',
    'idx_reservations_customer_date',
    'idx_sessionCustomers_customer_session'
  )
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME, INDEX_NAME;

SELECT
  TABLE_NAME,
  CONSTRAINT_NAME,
  REFERENCED_TABLE_NAME,
  DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME IN (
    'fk_customerUserSessions_userId',
    'fk_customerUserAuthChallenges_userId',
    'fk_customerUserSecurityEvents_userId',
    'fk_onlineOrders_customerUserId',
    'fk_reservations_customerUserId',
    'fk_sessionCustomers_customerUserId'
  )
ORDER BY TABLE_NAME, CONSTRAINT_NAME;