-- Moves branch details into the admin table.
-- Customers.cust_branch_id and loans.loan_branch_id should store admin.ad_id.

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

DROP PROCEDURE IF EXISTS add_admin_branch_column;

DELIMITER //
CREATE PROCEDURE add_admin_branch_column(IN column_name VARCHAR(64), IN column_definition TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'admin'
      AND COLUMN_NAME = column_name
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `admin` ADD COLUMN ', column_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;

CALL add_admin_branch_column('br_code', '`br_code` varchar(20) DEFAULT NULL AFTER `ad_mobile`');
CALL add_admin_branch_column('br_name', '`br_name` varchar(100) DEFAULT NULL AFTER `br_code`');
CALL add_admin_branch_column('br_manager', '`br_manager` varchar(100) DEFAULT NULL AFTER `br_name`');
CALL add_admin_branch_column('br_address', '`br_address` varchar(255) DEFAULT NULL AFTER `br_manager`');
CALL add_admin_branch_column('br_city', '`br_city` varchar(50) DEFAULT NULL AFTER `br_address`');
CALL add_admin_branch_column('br_state_id', '`br_state_id` int DEFAULT NULL AFTER `br_city`');
CALL add_admin_branch_column('ad_status', "`ad_status` enum('active','inactive') NOT NULL DEFAULT 'active' AFTER `br_state_id`");

DROP PROCEDURE IF EXISTS add_admin_branch_column;

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` = '');