-- --------------------------------------------------------
-- Complete Bangladesh Location Data
-- Country -> Division -> District -> Thana
-- --------------------------------------------------------

-- Ensure Bangladesh country exists
INSERT INTO `countries` (`id`, `name`, `code`, `is_active`) VALUES
(1, 'Bangladesh', 'BD', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all 8 Divisions
INSERT INTO `divisions` (`id`, `country_id`, `name`, `code`, `is_active`) VALUES
(1, 1, 'Dhaka', 'DHK', 1),
(2, 1, 'Chittagong', 'CTG', 1),
(3, 1, 'Rajshahi', 'RAJ', 1),
(4, 1, 'Khulna', 'KHL', 1),
(5, 1, 'Barisal', 'BAR', 1),
(6, 1, 'Sylhet', 'SYL', 1),
(7, 1, 'Rangpur', 'RAN', 1),
(8, 1, 'Mymensingh', 'MYM', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Dhaka Division (13 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(1, 'Dhaka', 'DHK', 1),
(1, 'Gazipur', 'GZP', 1),
(1, 'Kishoreganj', 'KSG', 1),
(1, 'Manikganj', 'MKG', 1),
(1, 'Munshiganj', 'MSG', 1),
(1, 'Narayanganj', 'NRG', 1),
(1, 'Narsingdi', 'NSD', 1),
(1, 'Tangail', 'TGL', 1),
(1, 'Faridpur', 'FRP', 1),
(1, 'Gopalganj', 'GPG', 1),
(1, 'Madaripur', 'MDP', 1),
(1, 'Rajbari', 'RJB', 1),
(1, 'Shariatpur', 'SRP', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Chittagong Division (11 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(2, 'Chittagong', 'CTG', 1),
(2, 'Cox\'s Bazar', 'CXB', 1),
(2, 'Bandarban', 'BDB', 1),
(2, 'Rangamati', 'RGM', 1),
(2, 'Khagrachhari', 'KGC', 1),
(2, 'Feni', 'FNI', 1),
(2, 'Lakshmipur', 'LKP', 1),
(2, 'Noakhali', 'NKL', 1),
(2, 'Chandpur', 'CDP', 1),
(2, 'Comilla', 'CML', 1),
(2, 'Brahmanbaria', 'BRB', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Rajshahi Division (8 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(3, 'Rajshahi', 'RAJ', 1),
(3, 'Bogra', 'BGR', 1),
(3, 'Joypurhat', 'JPH', 1),
(3, 'Naogaon', 'NGN', 1),
(3, 'Natore', 'NTR', 1),
(3, 'Chapai Nawabganj', 'CNG', 1),
(3, 'Pabna', 'PBN', 1),
(3, 'Sirajganj', 'SRG', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Khulna Division (10 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(4, 'Khulna', 'KHL', 1),
(4, 'Bagerhat', 'BGH', 1),
(4, 'Chuadanga', 'CHD', 1),
(4, 'Jashore', 'JSR', 1),
(4, 'Jhenaidah', 'JND', 1),
(4, 'Kushtia', 'KST', 1),
(4, 'Magura', 'MGR', 1),
(4, 'Meherpur', 'MHP', 1),
(4, 'Narail', 'NRL', 1),
(4, 'Satkhira', 'SKR', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Barisal Division (6 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(5, 'Barisal', 'BAR', 1),
(5, 'Barguna', 'BGN', 1),
(5, 'Bhola', 'BHL', 1),
(5, 'Jhalokati', 'JHK', 1),
(5, 'Patuakhali', 'PTK', 1),
(5, 'Pirojpur', 'PRP', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Sylhet Division (4 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(6, 'Sylhet', 'SYL', 1),
(6, 'Habiganj', 'HBG', 1),
(6, 'Moulvibazar', 'MLV', 1),
(6, 'Sunamganj', 'SNG', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Rangpur Division (8 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(7, 'Rangpur', 'RAN', 1),
(7, 'Dinajpur', 'DNP', 1),
(7, 'Gaibandha', 'GBD', 1),
(7, 'Kurigram', 'KRM', 1),
(7, 'Lalmonirhat', 'LMH', 1),
(7, 'Nilphamari', 'NPM', 1),
(7, 'Panchagarh', 'PCG', 1),
(7, 'Thakurgaon', 'TKG', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- Insert all Districts for Mymensingh Division (4 districts)
INSERT INTO `districts` (`division_id`, `name`, `code`, `is_active`) VALUES
(8, 'Mymensingh', 'MYM', 1),
(8, 'Jamalpur', 'JMP', 1),
(8, 'Netrokona', 'NTK', 1),
(8, 'Sherpur', 'SRP', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `code` = VALUES(`code`);

-- ============================================================
-- THANAS (Upazilas) - Major Thanas for each District
-- ============================================================

-- Dhaka District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 1 as div_id, 'Dhaka' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Dhanmondi' as name, 'DHN' as code UNION ALL
    SELECT 'Gulshan', 'GLS' UNION ALL
    SELECT 'Banani', 'BNN' UNION ALL
    SELECT 'Uttara', 'UTR' UNION ALL
    SELECT 'Motijheel', 'MTJ' UNION ALL
    SELECT 'Ramna', 'RMN' UNION ALL
    SELECT 'Tejgaon', 'TJG' UNION ALL
    SELECT 'Wari', 'WRI' UNION ALL
    SELECT 'Lalbagh', 'LBG' UNION ALL
    SELECT 'Kotwali', 'KTW' UNION ALL
    SELECT 'Sutrapur', 'STR' UNION ALL
    SELECT 'Sabujbagh', 'SBJ' UNION ALL
    SELECT 'Cantonment', 'CNT' UNION ALL
    SELECT 'Demra', 'DMR' UNION ALL
    SELECT 'Dohar', 'DHR' UNION ALL
    SELECT 'Dhamrai', 'DMR' UNION ALL
    SELECT 'Keraniganj', 'KRG' UNION ALL
    SELECT 'Nawabganj', 'NWB' UNION ALL
    SELECT 'Savar', 'SVR'
) as t;

-- Gazipur District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 1 as div_id, 'Gazipur' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Gazipur Sadar' as name, 'GZS' as code UNION ALL
    SELECT 'Kaliakair', 'KLK' UNION ALL
    SELECT 'Kapasia', 'KPS' UNION ALL
    SELECT 'Sreepur', 'SRP' UNION ALL
    SELECT 'Kaliganj', 'KLG'
) as t;

-- Narayanganj District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 1 as div_id, 'Narayanganj' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Narayanganj Sadar' as name, 'NRS' as code UNION ALL
    SELECT 'Bandar', 'BDR' UNION ALL
    SELECT 'Rupganj', 'RPG' UNION ALL
    SELECT 'Sonargaon', 'SNG' UNION ALL
    SELECT 'Araihazar', 'ARH'
) as t;

-- Chittagong District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 2 as div_id, 'Chittagong' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Chittagong Sadar' as name, 'CTS' as code UNION ALL
    SELECT 'Hathazari', 'HTZ' UNION ALL
    SELECT 'Raujan', 'RJU' UNION ALL
    SELECT 'Fatikchhari', 'FTC' UNION ALL
    SELECT 'Sandwip', 'SDW' UNION ALL
    SELECT 'Sitakunda', 'STK' UNION ALL
    SELECT 'Mirsharai', 'MRS' UNION ALL
    SELECT 'Patiya', 'PTY' UNION ALL
    SELECT 'Banshkhali', 'BSK' UNION ALL
    SELECT 'Boalkhali', 'BKL' UNION ALL
    SELECT 'Anwara', 'ANW' UNION ALL
    SELECT 'Chandanaish', 'CDN' UNION ALL
    SELECT 'Satkania', 'STK' UNION ALL
    SELECT 'Lohagara', 'LHG' UNION ALL
    SELECT 'Patiya', 'PTY'
) as t;

-- Comilla District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 2 as div_id, 'Comilla' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Comilla Sadar' as name, 'CMS' as code UNION ALL
    SELECT 'Barura', 'BRR' UNION ALL
    SELECT 'Brahmanpara', 'BRM' UNION ALL
    SELECT 'Burichang', 'BRC' UNION ALL
    SELECT 'Chandina', 'CDN' UNION ALL
    SELECT 'Chauddagram', 'CDG' UNION ALL
    SELECT 'Daudkandi', 'DDK' UNION ALL
    SELECT 'Debidwar', 'DBD' UNION ALL
    SELECT 'Homna', 'HMN' UNION ALL
    SELECT 'Laksam', 'LKM' UNION ALL
    SELECT 'Monohargonj', 'MNG' UNION ALL
    SELECT 'Meghna', 'MGH' UNION ALL
    SELECT 'Muradnagar', 'MRD' UNION ALL
    SELECT 'Nangalkot', 'NGK' UNION ALL
    SELECT 'Titas', 'TTS'
) as t;

-- Rajshahi District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 3 as div_id, 'Rajshahi' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Rajshahi Sadar' as name, 'RJS' as code UNION ALL
    SELECT 'Bagha', 'BGH' UNION ALL
    SELECT 'Bagmara', 'BGM' UNION ALL
    SELECT 'Charghat', 'CGT' UNION ALL
    SELECT 'Durgapur', 'DRG' UNION ALL
    SELECT 'Godagari', 'GDG' UNION ALL
    SELECT 'Mohanpur', 'MHP' UNION ALL
    SELECT 'Paba', 'PBA' UNION ALL
    SELECT 'Puthia', 'PTH' UNION ALL
    SELECT 'Tanore', 'TNR'
) as t;

-- Khulna District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 4 as div_id, 'Khulna' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Khulna Sadar' as name, 'KLS' as code UNION ALL
    SELECT 'Batiaghata', 'BTG' UNION ALL
    SELECT 'Dacope', 'DCP' UNION ALL
    SELECT 'Dumuria', 'DMR' UNION ALL
    SELECT 'Dighalia', 'DGL' UNION ALL
    SELECT 'Dumuria', 'DMR' UNION ALL
    SELECT 'Dacope', 'DCP' UNION ALL
    SELECT 'Koyra', 'KYR' UNION ALL
    SELECT 'Paikgachha', 'PKG' UNION ALL
    SELECT 'Phultala', 'PHT' UNION ALL
    SELECT 'Rupsa', 'RPS' UNION ALL
    SELECT 'Terokhada', 'TRK'
) as t;

-- Barisal District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 5 as div_id, 'Barisal' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Barisal Sadar' as name, 'BRS' as code UNION ALL
    SELECT 'Agailjhara', 'AGJ' UNION ALL
    SELECT 'Babuganj', 'BBG' UNION ALL
    SELECT 'Bakerganj', 'BKG' UNION ALL
    SELECT 'Banaripara', 'BNP' UNION ALL
    SELECT 'Gaurnadi', 'GRD' UNION ALL
    SELECT 'Hizla', 'HZL' UNION ALL
    SELECT 'Mehendiganj', 'MHD' UNION ALL
    SELECT 'Muladi', 'MLD' UNION ALL
    SELECT 'Wazirpur', 'WZP'
) as t;

-- Sylhet District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 6 as div_id, 'Sylhet' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Sylhet Sadar' as name, 'SLS' as code UNION ALL
    SELECT 'Balaganj', 'BLG' UNION ALL
    SELECT 'Beanibazar', 'BNB' UNION ALL
    SELECT 'Bishwanath', 'BSW' UNION ALL
    SELECT 'Balaganj', 'BLG' UNION ALL
    SELECT 'Fenchuganj', 'FNG' UNION ALL
    SELECT 'Golapganj', 'GLP' UNION ALL
    SELECT 'Gowainghat', 'GWG' UNION ALL
    SELECT 'Jaintiapur', 'JNP' UNION ALL
    SELECT 'Kanaighat', 'KNG' UNION ALL
    SELECT 'Osmani Nagar', 'OSN' UNION ALL
    SELECT 'Zakiganj', 'ZKG'
) as t;

-- Rangpur District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 7 as div_id, 'Rangpur' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Rangpur Sadar' as name, 'RPS' as code UNION ALL
    SELECT 'Badarganj', 'BDG' UNION ALL
    SELECT 'Gangachara', 'GGC' UNION ALL
    SELECT 'Kaunia', 'KNA' UNION ALL
    SELECT 'Mithapukur', 'MTP' UNION ALL
    SELECT 'Pirgacha', 'PRG' UNION ALL
    SELECT 'Pirganj', 'PRJ' UNION ALL
    SELECT 'Taraganj', 'TRG'
) as t;

-- Mymensingh District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 8 as div_id, 'Mymensingh' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Mymensingh Sadar' as name, 'MYS' as code UNION ALL
    SELECT 'Bhaluka', 'BLK' UNION ALL
    SELECT 'Dhobaura', 'DHB' UNION ALL
    SELECT 'Fulbaria', 'FLB' UNION ALL
    SELECT 'Gaffargaon', 'GFG' UNION ALL
    SELECT 'Gauripur', 'GRP' UNION ALL
    SELECT 'Haluaghat', 'HLG' UNION ALL
    SELECT 'Ishwarganj', 'ISG' UNION ALL
    SELECT 'Muktagachha', 'MKG' UNION ALL
    SELECT 'Nandail', 'NDL' UNION ALL
    SELECT 'Phulpur', 'PHP' UNION ALL
    SELECT 'Tarakanda', 'TRK'
) as t;

-- Add more major Thanas for other important districts
-- Bogra District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 3 as div_id, 'Bogra' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Bogra Sadar' as name, 'BGS' as code UNION ALL
    SELECT 'Adamdighi', 'ADG' UNION ALL
    SELECT 'Dhunat', 'DHT' UNION ALL
    SELECT 'Dhupchanchia', 'DPC' UNION ALL
    SELECT 'Gabtali', 'GBT' UNION ALL
    SELECT 'Kahaloo', 'KHL' UNION ALL
    SELECT 'Nandigram', 'NDG' UNION ALL
    SELECT 'Sariakandi', 'SRK' UNION ALL
    SELECT 'Shajahanpur', 'SJP' UNION ALL
    SELECT 'Sherpur', 'SHP' UNION ALL
    SELECT 'Shibganj', 'SBG' UNION ALL
    SELECT 'Sonatala', 'SNT'
) as t;

-- Jashore District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 4 as div_id, 'Jashore' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Jashore Sadar' as name, 'JSS' as code UNION ALL
    SELECT 'Abhaynagar', 'ABN' UNION ALL
    SELECT 'Bagherpara', 'BGP' UNION ALL
    SELECT 'Chaugachha', 'CGC' UNION ALL
    SELECT 'Jhikargacha', 'JKG' UNION ALL
    SELECT 'Keshabpur', 'KSB' UNION ALL
    SELECT 'Manirampur', 'MRP' UNION ALL
    SELECT 'Sharsha', 'SRS'
) as t;

-- Cox's Bazar District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 2 as div_id, 'Cox\'s Bazar' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Cox\'s Bazar Sadar' as name, 'CXS' as code UNION ALL
    SELECT 'Chakaria', 'CKR' UNION ALL
    SELECT 'Kutubdia', 'KTB' UNION ALL
    SELECT 'Maheshkhali', 'MHK' UNION ALL
    SELECT 'Pekua', 'PKU' UNION ALL
    SELECT 'Ramu', 'RMU' UNION ALL
    SELECT 'Teknaf', 'TKF' UNION ALL
    SELECT 'Ukhia', 'UKH'
) as t;

-- Tangail District Thanas
INSERT IGNORE INTO `thanas` (`district_id`, `name`, `code`, `is_active`) 
SELECT d.id, t.name, t.code, 1
FROM (SELECT 1 as div_id, 'Tangail' as dist_name) as dist_info
JOIN districts d ON d.division_id = dist_info.div_id AND d.name = dist_info.dist_name
CROSS JOIN (
    SELECT 'Tangail Sadar' as name, 'TGS' as code UNION ALL
    SELECT 'Basail', 'BSL' UNION ALL
    SELECT 'Bhuapur', 'BHP' UNION ALL
    SELECT 'Delduar', 'DLD' UNION ALL
    SELECT 'Dhanbari', 'DHB' UNION ALL
    SELECT 'Ghatail', 'GTL' UNION ALL
    SELECT 'Gopalpur', 'GPP' UNION ALL
    SELECT 'Kalihati', 'KLH' UNION ALL
    SELECT 'Madhupur', 'MDP' UNION ALL
    SELECT 'Mirzapur', 'MRZ' UNION ALL
    SELECT 'Nagarpur', 'NGP' UNION ALL
    SELECT 'Sakhipur', 'SKP'
) as t;

-- Note: This script includes major Thanas for key districts
-- For complete data, you may need to add more Thanas for remaining districts
-- Total: 1 Country, 8 Divisions, 64 Districts, and hundreds of Thanas