CREATE DATABASE IF NOT EXISTS `ifsc_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `ifsc_db`;

CREATE TABLE IF NOT EXISTS `banks` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bank_id` VARCHAR(100) NOT NULL,
  `bank_name` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL,
  `logo_url` VARCHAR(255) DEFAULT NULL,
  `is_popular` TINYINT(1) DEFAULT 0,
  `branch_count` INT(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bank_id` (`bank_id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `branches` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bank_id` VARCHAR(100) NOT NULL,
  `ifsc` VARCHAR(20) NOT NULL,
  `bank` VARCHAR(200) DEFAULT NULL,
  `branch` VARCHAR(200) DEFAULT NULL,
  `centre` VARCHAR(200) DEFAULT NULL,
  `district` VARCHAR(200) DEFAULT NULL,
  `state` VARCHAR(200) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `contact` VARCHAR(100) DEFAULT NULL,
  `imps` TINYINT(1) DEFAULT 0,
  `rtgs` TINYINT(1) DEFAULT 0,
  `neft` TINYINT(1) DEFAULT 0,
  `upi` TINYINT(1) DEFAULT 0,
  `micr` VARCHAR(50) DEFAULT NULL,
  `swift` VARCHAR(50) DEFAULT NULL,
  `city` VARCHAR(200) DEFAULT NULL,
  `iso3166` VARCHAR(50) DEFAULT NULL,
  `bank_code` VARCHAR(50) DEFAULT NULL,
  `slug` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ifsc` (`ifsc`),
  KEY `bank_id` (`bank_id`),
  KEY `bank` (`bank`),
  KEY `city` (`city`),
  KEY `state` (`state`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;