-- --------------------------------------------------------
-- Location Management Tables
-- Country -> Division -> District -> Thana (Cascading)
-- --------------------------------------------------------

-- Dumping structure for table cms_db.countries
CREATE TABLE IF NOT EXISTS `countries` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(10) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_country_name` (`name`),
  KEY `idx_country_code` (`code`),
  KEY `idx_country_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping structure for table cms_db.divisions
CREATE TABLE IF NOT EXISTS `divisions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `country_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(10) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_division_name_country` (`country_id`, `name`),
  KEY `idx_division_country_id` (`country_id`),
  KEY `idx_division_code` (`code`),
  KEY `idx_division_is_active` (`is_active`),
  CONSTRAINT `divisions_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping structure for table cms_db.districts
CREATE TABLE IF NOT EXISTS `districts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `division_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(10) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_district_name_division` (`division_id`, `name`),
  KEY `idx_district_division_id` (`division_id`),
  KEY `idx_district_code` (`code`),
  KEY `idx_district_is_active` (`is_active`),
  CONSTRAINT `districts_ibfk_1` FOREIGN KEY (`division_id`) REFERENCES `divisions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping structure for table cms_db.thanas
CREATE TABLE IF NOT EXISTS `thanas` (
  `id` int NOT NULL AUTO_INCREMENT,
  `district_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(10) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_thana_name_district` (`district_id`, `name`),
  KEY `idx_thana_district_id` (`district_id`),
  KEY `idx_thana_code` (`code`),
  KEY `idx_thana_is_active` (`is_active`),
  CONSTRAINT `thanas_ibfk_1` FOREIGN KEY (`district_id`) REFERENCES `districts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Insert default data for Bangladesh
INSERT INTO `countries` (`id`, `name`, `code`, `is_active`) VALUES
(1, 'Bangladesh', 'BD', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- Insert default divisions for Bangladesh
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`);