-- Branch module migration for existing Gold Loan databases.
-- Branches are now stored directly in admin where ad_type='b'.

ALTER TABLE `admin` MODIFY `ad_id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `admin` MODIFY `ad_mobile` varchar(20) NOT NULL;

SET @db_name = DATABASE();
SET @sql = (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `admin` ADD COLUMN `br_code` varchar(20) DEFAULT NULL AFTER `ad_mobile`',
    'SELECT 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'admin' AND COLUMN_NAME = 'br_code'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `admin` ADD COLUMN `br_name` varchar(100) DEFAULT NULL AFTER `br_code`',
    'SELECT 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'admin' AND COLUMN_NAME = 'br_name'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `admin` ADD COLUMN `br_manager` varchar(100) DEFAULT NULL AFTER `br_name`',
    'SELECT 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'admin' AND COLUMN_NAME = 'br_manager'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(COUNT(*) = 0, 'ALTER TABLE `admin` ADD COLUMN `br_address` varchar(255) DEFAULT NULL AFTER `br_manager`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'admin' AND COLUMN_NAME = 'br_address'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(COUNT(*) = 0, 'ALTER TABLE `admin` ADD COLUMN `br_city` varchar(50) DEFAULT NULL AFTER `br_address`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'admin' AND COLUMN_NAME = 'br_city'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(COUNT(*) = 0, 'ALTER TABLE `admin` ADD COLUMN `br_state_id` int DEFAULT NULL AFTER `br_city`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'admin' AND COLUMN_NAME = 'br_state_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(COUNT(*) = 0, "ALTER TABLE `admin` ADD COLUMN `ad_status` enum('active','inactive') NOT NULL DEFAULT 'active' AFTER `br_state_id`", 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'admin' AND COLUMN_NAME = 'ad_status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `admin` SET `ad_status` = 'active' WHERE `ad_status` IS NULL OR `ad_status` = '';
UPDATE `admin` SET `br_code`='ADMIN', `br_name`='Super Admin Branch', `br_manager`='Super Admin' WHERE `ad_id`=1 AND (`br_code` IS NULL OR `br_code`='');