CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_voter_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `full_name` text NOT NULL,
  `guardian_name` text,
  `gender` varchar(20) NOT NULL,
  `age` int DEFAULT NULL,
  `date_of_birth` text,
  `indian_voter_id` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `sec_id` varchar(50) DEFAULT NULL,
  `occupation` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `profession` text,
  `profession_region` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `profession_location` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `profession_at` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `profession_remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `mobile_number` text,
  `whatsapp_number` text,
  `alt_number` text,
  `education_level` text,
  `education_domain` text,
  `education_stream` text,
  `religion` text,
  `caste` text,
  `reservation_category` varchar(100) DEFAULT NULL,
  `political_front` text,
  `political_party` text,
  `achievements` text,
  `state` text,
  `parliament_constituency` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `assembly_constituency` text,
  `localbody_type` text,
  `localbody_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `localbody_name` text,
  `ward_number` int DEFAULT NULL,
  `ward_name` text,
  `booth_number` text,
  `booth_name` text,
  `house_owner` tinyint DEFAULT NULL,
  `house_id` int DEFAULT NULL,
  `diabetic_patient` tinyint DEFAULT NULL,
  `diabetic_remark` text,
  `bp_patient` tinyint DEFAULT NULL,
  `bp_patient_remark` text,
  `bed_lying_patient` tinyint DEFAULT NULL,
  `bed_lying_patient_remark` text,
  `palliative_care_required` tinyint DEFAULT NULL,
  `palliative_care_remark` text,
  `pwd_status` tinyint NOT NULL,
  `kidney_patient` tinyint DEFAULT NULL,
  `kidney_patient_dialysis` text,
  `cancer_patient` tinyint DEFAULT NULL,
  `radiation_needed` text,
  `any_other_health_issues` tinyint DEFAULT NULL,
  `any_other_health_issue_remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `regional_issues` tinyint DEFAULT NULL,
  `voter_status` tinyint DEFAULT NULL,
  `voterlisted_localbody` tinyint DEFAULT NULL,
  `voterlisted_assembly` tinyint DEFAULT NULL,
  `voterlisted_parliament` tinyint DEFAULT NULL,
  `part_of_special_group` tinyint NOT NULL DEFAULT '0',
  `part_of_ente_nadu` tinyint NOT NULL DEFAULT '0',
  `collected_by` text,
  `verified_by` text,
  `modified_by` text,
  `modified_at` text,
  `modified_ip` text,
  `created_by` text,
  `created_at` text,
  `created_ip` text,
  `verified_ip` text,
  `verified_at` text,
  `surveyed_year` text,
  `dead` tinyint DEFAULT '0',
  `married` tinyint DEFAULT NULL,
  `married_to` text,
  `photo_url` text,
  `blood_group` enum('A+','A-','B+','B-','O+','O-','AB+','AB-') DEFAULT NULL,
  `email_id` text,
  `marital_status` enum('Single','Married','Divorced','Widowed') DEFAULT NULL,
  `alcohol_consumption` enum('Not Available','Never','Occasionally','Regularly','Heavy','Prefer not to say') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `driving_licence` tinyint DEFAULT NULL,
  `health_insurance` tinyint DEFAULT NULL,
  `localbody_booth` text,
  `assembly_booth` text,
  `parliament_booth` text,
  `localbody_booth_serial_number` int DEFAULT NULL,
  `assembly_booth_serial_number` int DEFAULT NULL,
  `parliament_booth_serial_number` int DEFAULT NULL,
  `new_voter` tinyint DEFAULT NULL,
  `voter_data_correction_required` tinyint DEFAULT NULL,
  `need_to_add_as_a_new_voter` tinyint DEFAULT NULL,
  `awards` tinyint DEFAULT NULL,
  `heart_diseases` tinyint DEFAULT NULL,
  `heart_diseases_remark` text,
  `liver_diseases` tinyint DEFAULT NULL,
  `liver_diseases_remark` text,
  `Thyroid_issues` tinyint DEFAULT NULL,
  `Thyroid_issues_type` text,
  `asthma_or_respiratory_issue` tinyint DEFAULT NULL,
  `asthma_or_respiratory_issue_remark` text,
  `health_details_remark_individual` text,
  `influencer` tinyint DEFAULT NULL,
  `influencer_id` int DEFAULT NULL,
  `postal_vote` tinyint DEFAULT NULL,
  `soft_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_on` datetime DEFAULT NULL,
  `deleted_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sec_id` (`sec_id`),
  KEY `house_id` (`house_id`),
  KEY `fk_voter_influencer` (`influencer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23183 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `{{TABLE_PREFIX}}_voter_house`
  ADD CONSTRAINT `{{TABLE_PREFIX}}_voter_house_ibfk_1` FOREIGN KEY (`house_owner_id`) REFERENCES `{{TABLE_PREFIX}}_voter_data` (`id`);

