-- =============================================================================
-- Backfill columntitles for missing required columns
--
-- For each (shortname, required_column) that is missing, inserts one row:
--   (shortname, newTitle = required_column, existingTitle = required_column)
-- So the bank's file can keep using the exact same header (e.g. "MaturityDate");
-- nothing changes for them, but the required-column check will pass and future
-- onboarding/remapping will prompt them if they ever change their column names.
--
-- Run find_shortnames_missing_required_columntitles.sql first to review.
-- Table name: columntitles or columnTitles per your schema.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- PREVIEW (Option A): Run this first to see exactly what would be inserted.
--                     Same logic as the INSERT below; no rows are changed.
-- -----------------------------------------------------------------------------
SELECT b.shortname, r.req AS newTitle, r.req AS existingTitle
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
ORDER BY b.shortname, r.req;

-- -----------------------------------------------------------------------------
-- INSERT (Option A): Backfill only for shortnames that already have at least
--                    one columntitles row. Run after reviewing the PREVIEW.
-- -----------------------------------------------------------------------------
-- INSERT INTO columntitles (shortname, newTitle, existingTitle)
-- SELECT b.shortname, r.req, r.req
-- 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;

-- -----------------------------------------------------------------------------
-- PREVIEW (Option B): Same as above but for peer_groups shortnames. Uncomment
--                    to preview before running the Option B INSERT.
-- -----------------------------------------------------------------------------
-- SELECT b.shortname, r.req AS newTitle, r.req AS existingTitle
-- 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
-- ORDER BY b.shortname, r.req;

-- -----------------------------------------------------------------------------
-- INSERT (Option B): Backfill for all peer_groups shortnames missing any
--                    required column. Uncomment to run after previewing.
-- -----------------------------------------------------------------------------
-- INSERT INTO columntitles (shortname, newTitle, existingTitle)
-- SELECT b.shortname, r.req, r.req
-- 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;