-- Make sure to use utf8mb4 for proper Bengali character support
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- Table for Divisions
CREATE TABLE IF NOT EXISTS `ldtax_divisions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `division_id` INT NOT NULL UNIQUE COMMENT 'Source ID from ldtax website',
  `name_bn` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_en` VARCHAR(255) NULL, -- Assuming English name might be added later
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for Districts
CREATE TABLE IF NOT EXISTS `ldtax_districts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `district_id` INT NOT NULL UNIQUE COMMENT 'Source ID from ldtax website',
  `division_id` INT NOT NULL COMMENT 'FK referencing ldtax_divisions.division_id',
  `district_bbs_code` VARCHAR(20) NULL,
  `name_bn` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_en` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_district_division_id` (`division_id`)
  -- Optional: Add Foreign Key Constraint
  -- CONSTRAINT `fk_district_division` FOREIGN KEY (`division_id`) REFERENCES `ldtax_divisions` (`division_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for Upazilas (or Thanas)
CREATE TABLE IF NOT EXISTS `ldtax_upazilas` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `upazila_id` INT NOT NULL UNIQUE COMMENT 'Source ID from ldtax website',
  `district_id` INT NOT NULL COMMENT 'FK referencing ldtax_districts.district_id',
  `name_bn` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Mapped from name_bd in JSON',
  `name_en` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_upazila_district_id` (`district_id`)
  -- Optional: Add Foreign Key Constraint
  -- CONSTRAINT `fk_upazila_district` FOREIGN KEY (`district_id`) REFERENCES `ldtax_districts` (`district_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for Offices (Union/Municipal Land Offices etc.)
CREATE TABLE IF NOT EXISTS `ldtax_offices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `office_id` INT NOT NULL UNIQUE COMMENT 'Source ID from ldtax website',
  `upazila_id` INT NULL COMMENT 'FK referencing ldtax_upazilas.upazila_id',
  `district_id` INT NULL COMMENT 'FK referencing ldtax_districts.district_id',
  `division_id` INT NULL COMMENT 'FK referencing ldtax_divisions.division_id',
  `parent_office_id` INT NULL COMMENT 'Source parent ID',
  `title_bn` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `title_en` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `short_name_bn` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `short_name_en` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `code` VARCHAR(100) NULL UNIQUE,
  `website_url` VARCHAR(255) NULL,
  `base_office_id` INT NULL,
  `layer_id` INT NULL,
  `org_ibas_code` VARCHAR(50) NULL,
  `uuid` VARCHAR(40) NULL UNIQUE,
  `status` TINYINT DEFAULT 1,
  `office_type_name_bn` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `office_type_name_en` VARCHAR(100) NULL,
  `office_layer_name_bn` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `office_layer_name_en` VARCHAR(100) NULL,
  `office_data_json` JSON NULL COMMENT 'Store the raw JSON for fields not explicitly mapped',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_office_upazila_id` (`upazila_id`),
  INDEX `idx_office_district_id` (`district_id`),
  INDEX `idx_office_division_id` (`division_id`),
  INDEX `idx_office_parent_office_id` (`parent_office_id`),
  INDEX `idx_office_code` (`code`),
  INDEX `idx_office_uuid` (`uuid`)
   -- Optional: Add Foreign Key Constraints (use appropriate ON DELETE/UPDATE actions)
  -- CONSTRAINT `fk_office_upazila` FOREIGN KEY (`upazila_id`) REFERENCES `ldtax_upazilas` (`upazila_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  -- CONSTRAINT `fk_office_district` FOREIGN KEY (`district_id`) REFERENCES `ldtax_districts` (`district_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  -- CONSTRAINT `fk_office_division` FOREIGN KEY (`division_id`) REFERENCES `ldtax_divisions` (`division_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for Moujas
CREATE TABLE IF NOT EXISTS `ldtax_moujas` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `mouja_id` INT NOT NULL UNIQUE COMMENT 'Source ID from ldtax website',
  `office_id` INT NOT NULL COMMENT 'FK referencing ldtax_offices.office_id',
  `name_bn` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Mapped from name_bd in JSON',
  `jl_no` VARCHAR(50) NULL,
  `dglr_code` VARCHAR(50) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_mouja_office_id` (`office_id`)
  -- Optional: Add Foreign Key Constraint
  -- CONSTRAINT `fk_mouja_office` FOREIGN KEY (`office_id`) REFERENCES `ldtax_offices` (`office_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
