-- Branch Management Table
CREATE TABLE IF NOT EXISTS `tbl_branch` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `company_id` INT(11) NOT NULL COMMENT 'Foreign key to tbl_company',
    `branch_name` VARCHAR(200) NOT NULL,
    `branch_code` VARCHAR(50) NOT NULL UNIQUE,
    `contact_no` VARCHAR(20) NOT NULL,
    `address` TEXT NOT NULL,
    `state` VARCHAR(100) NOT NULL,
    `email` VARCHAR(150) DEFAULT NULL,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `remarks` TEXT DEFAULT NULL,
    `created_by` INT(11) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_by` INT(11) DEFAULT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company_id` (`company_id`),
    KEY `idx_status` (`status`),
    KEY `idx_branch_code` (`branch_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data
INSERT INTO `tbl_branch` (`company_id`, `branch_name`, `branch_code`, `contact_no`, `address`, `state`, `email`, `status`, `remarks`) VALUES
(1, 'Head Office', 'HO001', '9876543210', '123 Main Street, MG Road', 'Karnataka', 'headoffice@example.com', 'active', 'Main branch'),
(1, 'Branch Office - Mumbai', 'BO002', '9876543211', '456 Andheri West', 'Maharashtra', 'mumbai@example.com', 'active', 'Mumbai regional office');
