-- Migration: Update Banks Structure
-- Description: Create master_banks table and update bs_banks to link to master_banks and accounts
-- Create master_banks table
CREATE TABLE IF NOT EXISTS master_banks (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Insert some default banks
INSERT IGNORE INTO master_banks (name) VALUES
('HDFC Bank'),
('ICICI Bank'),
('State Bank of India'),
('Axis Bank'),
('Kotak Mahindra Bank'),
('Punjab National Bank'),
('Bank of Baroda'),
('Canara Bank'),
('Union Bank of India'),
('IndusInd Bank'),
('IDFC FIRST Bank'),
('Yes Bank');
-- Update bs_banks table
ALTER TABLE bs_banks
ADD COLUMN IF NOT EXISTS master_bank_id INT NULL,
ADD COLUMN IF NOT EXISTS account_id INT NULL,
ADD CONSTRAINT fk_bs_banks_master_bank FOREIGN KEY (master_bank_id) REFERENCES master_banks(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_bs_banks_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL;
-- Index for better performance
CREATE INDEX idx_bs_banks_master_bank_id ON bs_banks(master_bank_id);
CREATE INDEX idx_bs_banks_account_id ON bs_banks(account_id);
