DROP TABLE IF EXISTS `{{TABLE_PREFIX}}_voter_awards`;
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_voter_awards` (
  `id` int NOT NULL AUTO_INCREMENT,
  `voter_id` int NOT NULL,
  `Awards_name` text NOT NULL,
  `Awards_level` text NOT NULL,
  `Awards_sector` text NOT NULL,
  `Awards_year` text NOT NULL,
  `Awards_detail` text,
  PRIMARY KEY (`id`),
  KEY `voter_id` (`voter_id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `{{TABLE_PREFIX}}_voter_awards`
  ADD CONSTRAINT `{{TABLE_PREFIX}}_voter_awards_ibfk_1` FOREIGN KEY (`voter_id`) REFERENCES `{{TABLE_PREFIX}}_voter_data` (`id`) ON DELETE CASCADE;




DROP TABLE IF EXISTS `{{TABLE_PREFIX}}_voter_regional_issues`;
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_voter_regional_issues` (
  `id` int NOT NULL AUTO_INCREMENT,
  `voter_id` int NOT NULL,
  `regional_issue_category` text NOT NULL,
  `issue_details` text,
  PRIMARY KEY (`id`),
  KEY `voter_id` (`voter_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `{{TABLE_PREFIX}}_voter_regional_issues`
  ADD CONSTRAINT `{{TABLE_PREFIX}}_voter_regional_issues_ibfk_1` FOREIGN KEY (`voter_id`) REFERENCES `{{TABLE_PREFIX}}_voter_data` (`id`) ON DELETE CASCADE;


CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_voter_special_group` (
  `id` int NOT NULL AUTO_INCREMENT,
  `voter_id` int NOT NULL,
  `special_group_name` text NOT NULL,
  `remark` text,
  PRIMARY KEY (`id`),
  KEY `voter_id` (`voter_id`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `{{TABLE_PREFIX}}_voter_special_group`
  ADD CONSTRAINT `{{TABLE_PREFIX}}_voter_special_group_ibfk_1` FOREIGN KEY (`voter_id`) REFERENCES `{{TABLE_PREFIX}}_voter_data` (`id`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_voter_pwd` (
  `id` INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `voter_id` INT NOT NULL,
  `disease` TEXT NOT NULL,
  `remark` TEXT NOT NULL,
  FOREIGN KEY (`voter_id`) REFERENCES `{{TABLE_PREFIX}}_voter_data`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;





CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_tenant_user_regions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `region_type` enum('state','district','parliament','assembly','localbody','panchayat','ward','booth') COLLATE utf8mb4_general_ci NOT NULL,
  `region_table` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `region_column` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `region_id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `region_label` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_by_user_type` enum('super_admin','tenant_admin','tenant_user') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by_id` int DEFAULT NULL,
  `modified_by_user_type` enum('super_admin','tenant_admin','tenant_user') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `modified_by_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `region_type` (`region_type`),
  KEY `region_table` (`region_table`),
  KEY `region_id` (`region_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_voter_data_drafts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `draft_key` varchar(255) NOT NULL,
  `user_id` int NOT NULL,
  `individual_or_family` enum('individual','family') NOT NULL DEFAULT 'individual',
  `current_step` int NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `completed` tinyint(1) NOT NULL DEFAULT '0',
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `{{TABLE_PREFIX}}_uniq_draft_key` (`draft_key`),
  KEY `fk_{{TABLE_PREFIX}}_voter_data_drafts_user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=696 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



ALTER TABLE `{{TABLE_PREFIX}}_voter_data_drafts`
  ADD CONSTRAINT `fk_{{TABLE_PREFIX}}_voter_data_drafts_user` FOREIGN KEY (`user_id`) REFERENCES `{{TABLE_PREFIX}}_tenant_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;