CREATE TABLE IF NOT EXISTS `branches` (
    `branch_id` INT PRIMARY KEY AUTO_INCREMENT,
    `branch_name` VARCHAR(255) NOT NULL,
    `branch_code` VARCHAR(4) NOT NULL UNIQUE COMMENT 'Format: one letter followed by 3 numbers (e.g., m001)',
    `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 main branch
INSERT INTO `branches` (`branch_id`, `branch_name`, `branch_code`) VALUES 
(1, 'Main Branch', 'm001'); 