-- Create user_branches table
CREATE TABLE IF NOT EXISTS user_branches (
    user_id INT NOT NULL,
    branch_id INT NOT NULL,
    PRIMARY KEY (user_id, branch_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migrate existing data from users table
INSERT IGNORE INTO user_branches (user_id, branch_id)
SELECT id, branch_id FROM users WHERE branch_id IS NOT NULL;

-- Keep branch_id in users for now as fallback or default, but we should eventually rely on user_branches