-- =============================================================================
-- Find shortnames that are missing one or more required column titles
-- Required columns: CallReportCode, CurrentBalance, CurrentInterestRate,
--                   NotePaymtAmt, MaturityDate, Acct#
-- Use this before enabling onboarding auto-redirect to see which active banks
-- would be sent to onboarding (they may be using exact column names and never
-- had a row in columntitles).
-- =============================================================================

-- Table name: use columntitles or columnTitles depending on your schema
-- (MySQL table names can be case-sensitive on Linux.)
-- For "active" CECL banks (have peer group), use Option B; it includes banks
-- with zero columntitles rows as well as those missing only some required ones.

-- -----------------------------------------------------------------------------
-- Option A: Shortnames missing required columns (among banks that have ANY
--           columntitles row). Lists each shortname and which required columns
--           are missing. Excludes banks with no columntitles at all.
-- -----------------------------------------------------------------------------
SELECT
  b.shortname,
  GROUP_CONCAT(r.req ORDER BY r.req) AS missing_required_columns
FROM (SELECT DISTINCT shortname FROM columntitles) b
CROSS JOIN (
  SELECT 'Acct#' AS req UNION ALL SELECT 'CallReportCode' UNION ALL SELECT 'CurrentBalance'
  UNION ALL SELECT 'CurrentInterestRate' UNION ALL SELECT 'MaturityDate' UNION ALL SELECT 'NotePaymtAmt'
) r
LEFT JOIN columntitles c ON c.shortname = b.shortname AND c.newTitle = r.req
WHERE c.columnID IS NULL
GROUP BY b.shortname
ORDER BY b.shortname;

-- -----------------------------------------------------------------------------
-- Option B: Same but restricted to banks that have a peer group (likely
--           "active" CECL banks). Uncomment and run if you prefer this.
-- -----------------------------------------------------------------------------
-- SELECT
--   b.shortname,
--   GROUP_CONCAT(r.req ORDER BY r.req) AS missing_required_columns
-- FROM (SELECT DISTINCT shortname FROM peer_groups) b
-- CROSS JOIN (
--   SELECT 'Acct#' AS req UNION ALL SELECT 'CallReportCode' UNION ALL SELECT 'CurrentBalance'
--   UNION ALL SELECT 'CurrentInterestRate' UNION ALL SELECT 'MaturityDate' UNION ALL SELECT 'NotePaymtAmt'
-- ) r
-- LEFT JOIN columntitles c ON c.shortname = b.shortname AND c.newTitle = r.req
-- WHERE c.columnID IS NULL
-- GROUP BY b.shortname
-- ORDER BY b.shortname;

-- -----------------------------------------------------------------------------
-- Option C: Simple list of shortnames that have fewer than 6 required columns
--           (no detail of which are missing). Handy for a quick count.
-- -----------------------------------------------------------------------------
-- SELECT shortname,
--        COUNT(DISTINCT CASE WHEN newTitle IN (
--          'Acct#','CallReportCode','CurrentBalance','CurrentInterestRate','MaturityDate','NotePaymtAmt'
--        ) THEN newTitle END) AS required_count,
--        6 AS required_total
-- FROM columntitles
-- GROUP BY shortname
-- HAVING required_count < 6
-- ORDER BY shortname;