-- =============================================================================
-- Bank account form: add representative + branch fields to dim_financial_entities,
-- and a dim_banks reference table (seeded with major US banks) for the Bank Name
-- dropdown on /finance/business-accounts/bank-accounts.
--
-- Idempotent: column adds are guarded via information_schema; dim_banks uses
-- CREATE TABLE IF NOT EXISTS + UNIQUE(name) + INSERT IGNORE.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/bank_account_branch_fields_and_banks.sql
-- =============================================================================
USE db_abos_v0.1;

-- ── 1. New columns on dim_financial_entities ────────────────────────────────
SET @db := DATABASE();

-- business_id: the JPA entity maps a Business relation and list/create queries filter
-- on it; some older dev databases predate this column. Guarded add so prod (which
-- already has it) is unaffected.
SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN business_id INT NULL AFTER tenant_id',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='business_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_representative VARCHAR(255) NULL AFTER bank_email',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_representative');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_branch VARCHAR(255) NULL AFTER bank_representative',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_branch');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_branch_number VARCHAR(100) NULL AFTER bank_branch',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_branch_number');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_branch_address VARCHAR(500) NULL AFTER bank_branch_number',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_branch_address');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 1b. Align entity_type enum with the Java enum names ─────────────────────
-- The JPA enum is stored as its constant name ('Bank','CreditCard'), but some dev
-- DBs define the column as enum('Bank','Credit Card') with a space, which truncates
-- on CreditCard inserts. Expand → migrate legacy value → collapse to canonical.
--
-- entity_type carries no index, so this UPDATE is rejected by MySQL Workbench/
-- phpMyAdmin's client-side "safe update mode" (Error 1175) even though it has a
-- WHERE clause — that mode specifically requires a KEY column in the WHERE.
-- Toggle it off for just this one statement rather than relying on every client's
-- local preference being configured a particular way.
SET SQL_SAFE_UPDATES = 0;
ALTER TABLE dim_financial_entities
  MODIFY COLUMN entity_type ENUM('Bank','Credit Card','CreditCard') NOT NULL;
UPDATE dim_financial_entities SET entity_type='CreditCard' WHERE entity_type='Credit Card';
SET SQL_SAFE_UPDATES = 1;
ALTER TABLE dim_financial_entities
  MODIFY COLUMN entity_type ENUM('Bank','CreditCard') NOT NULL;

-- ── 2. Reference table for the Bank Name dropdown ───────────────────────────
CREATE TABLE IF NOT EXISTS dim_banks (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    country     VARCHAR(10)  NOT NULL DEFAULT 'US',
    is_deleted  TINYINT(1)   NOT NULL DEFAULT 0,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dim_banks_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. Seed major US banks (curated ~100; INSERT IGNORE = safe to re-run) ────
INSERT IGNORE INTO dim_banks (name) VALUES
('JPMorgan Chase Bank'),('Bank of America'),('Wells Fargo Bank'),('Citibank'),
('U.S. Bank'),('PNC Bank'),('Truist Bank'),('Goldman Sachs Bank USA'),
('TD Bank'),('Capital One'),('The Bank of New York Mellon'),('State Street Bank and Trust'),
('Citizens Bank'),('Fifth Third Bank'),('Morgan Stanley Bank'),('HSBC Bank USA'),
('Ally Bank'),('KeyBank'),('Regions Bank'),('M&T Bank'),
('Huntington National Bank'),('American Express National Bank'),('Discover Bank'),('BMO Bank'),
('First Republic Bank'),('Silicon Valley Bank'),('Charles Schwab Bank'),('Comerica Bank'),
('Zions Bank'),('First Citizens Bank'),('Synchrony Bank'),('Santander Bank'),
('Flagstar Bank'),('Western Alliance Bank'),('Valley National Bank'),('Webster Bank'),
('East West Bank'),('First Horizon Bank'),('Frost Bank'),('Bank of Oklahoma'),
('Pinnacle Bank'),('Wintrust Bank'),('Associated Bank'),('Old National Bank'),
('UMB Bank'),('Bank OZK'),('Prosperity Bank'),('Cadence Bank'),
('South State Bank'),('Fulton Bank'),('Simmons Bank'),('Umpqua Bank'),
('Texas Capital Bank'),('Glacier Bank'),('Centennial Bank'),('Commerce Bank'),
('First National Bank of Omaha'),('Arvest Bank'),('WesBanco'),('Renasant Bank'),
('Ameris Bank'),('Bank of Hawaii'),('First Hawaiian Bank'),('Rockland Trust'),
('Banner Bank'),('Cathay Bank'),('Bank of Hope'),('Pacific Western Bank'),
('Trustmark National Bank'),('Independent Bank'),('Atlantic Union Bank'),('TowneBank'),
('Sandy Spring Bank'),('Eastern Bank'),('Berkshire Bank'),('NBT Bank'),
('Provident Bank'),('Dime Community Bank'),('ConnectOne Bank'),('First Interstate Bank'),
('Hancock Whitney Bank'),('BankUnited'),('Axos Bank'),('Live Oak Bank'),
('EverBank'),('USAA Federal Savings Bank'),('Navy Federal Credit Union'),('PenFed Credit Union'),
('Varo Bank'),('SoFi Bank'),('Mercury'),('Brex'),
('City National Bank'),('Signature Bank'),('First National Bank'),('Bremer Bank'),
('Great Southern Bank'),('Columbia Bank'),('Customers Bank'),('Amerant Bank'),
('Beneficial State Bank'),('Nicolet National Bank');