-- Migration: Add location columns to users/contacts table
-- This adds Country, Division, District, and Thana IDs for Present, Office, and Permanent addresses

-- Add Present Address location columns
ALTER TABLE `users` 
ADD COLUMN `present_country_id` INT NULL AFTER `present_address`,
ADD COLUMN `present_division_id` INT NULL AFTER `present_country_id`,
ADD COLUMN `present_district_id` INT NULL AFTER `present_division_id`,
ADD COLUMN `present_thana_id` INT NULL AFTER `present_district_id`;

-- Add Office Address location columns
ALTER TABLE `users` 
ADD COLUMN `office_country_id` INT NULL AFTER `office_address`,
ADD COLUMN `office_division_id` INT NULL AFTER `office_country_id`,
ADD COLUMN `office_district_id` INT NULL AFTER `office_division_id`,
ADD COLUMN `office_thana_id` INT NULL AFTER `office_district_id`;

-- Add Permanent Address location columns
ALTER TABLE `users` 
ADD COLUMN `permanent_country_id` INT NULL AFTER `permanent_address`,
ADD COLUMN `permanent_division_id` INT NULL AFTER `permanent_country_id`,
ADD COLUMN `permanent_district_id` INT NULL AFTER `permanent_division_id`,
ADD COLUMN `permanent_thana_id` INT NULL AFTER `permanent_district_id`;

-- Add indexes for better query performance
CREATE INDEX `idx_present_country` ON `users` (`present_country_id`);
CREATE INDEX `idx_present_division` ON `users` (`present_division_id`);
CREATE INDEX `idx_present_district` ON `users` (`present_district_id`);
CREATE INDEX `idx_present_thana` ON `users` (`present_thana_id`);

CREATE INDEX `idx_office_country` ON `users` (`office_country_id`);
CREATE INDEX `idx_office_division` ON `users` (`office_division_id`);
CREATE INDEX `idx_office_district` ON `users` (`office_district_id`);
CREATE INDEX `idx_office_thana` ON `users` (`office_thana_id`);

CREATE INDEX `idx_permanent_country` ON `users` (`permanent_country_id`);
CREATE INDEX `idx_permanent_division` ON `users` (`permanent_division_id`);
CREATE INDEX `idx_permanent_district` ON `users` (`permanent_district_id`);
CREATE INDEX `idx_permanent_thana` ON `users` (`permanent_thana_id`);