registration.sql

-- FILE_NAME : registration.sql

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Apr 11, 2023 at 07:12 AM
-- Server version: 10.4.21-MariaDB
-- PHP Version: 7.4.29

START TRANSACTION;

-- --------------------------------------------------------

--
-- Table structure for table `registration`
--

CREATE TABLE `registration` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `first_name` varchar(50) NOT NULL COMMENT "Person Name",
    `middle_name` varchar(50) NOT NULL COMMENT "Father/Husband Name",
    `last_name` varchar(50) NOT NULL COMMENT "Surname",
    `last_name_id` int(11) NOT NULL DEFAULT "0" COMMENT "Surname ID",
    `father_id` int(11) NOT NULL DEFAULT "0" COMMENT "Father ID",
    `mother_id` int(11) NOT NULL DEFAULT "0" COMMENT "Mother ID",
    `family_id` int(11) NULL DEFAULT NULL COMMENT "Families Table ID",
    `maiden_family_id` int(11) NULL DEFAULT NULL COMMENT "Families Table ID",
    `is_registered` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'if person registered form filled then 1 otherwise 0',
    `gender` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0 - not set\r\n1 - male\r\n2 - female\r\n3 - other',
    `marital_status` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0 - not set\r\1 - single\r\n2 - married\r\n3 - divorced\r\n4 - widowed',
    `email` varchar(255) NULL UNIQUE COMMENT "Email Address",
    `email_verified` TINYINT(1) DEFAULT "0" NOT NULL COMMENT "0 - not verified\r\n1 - verified",
    `email_verified_at` DATETIME DEFAULT NULL COMMENT "Email Address Verified DateTime",
    `primary_mobile` varchar(10) UNIQUE DEFAULT NULL COMMENT "Primary Mobile Number",
    `primary_mobile_verified_at` DATETIME DEFAULT NULL COMMENT "Primary Mobile Verified DateTime",
    `secondary_mobile`varchar(10) DEFAULT NULL COMMENT "Secondary Mobile Number",
    `avatar` varchar(50) DEFAULT NULL,
    `aadhaar_hash` char(64) DEFAULT NULL,
    `aadhaar_enc` varbinary(256) DEFAULT NULL,
    `aadhaar_verified` tinyint(1) NOT NULL DEFAULT 0,
    `aadhaar_verified_at` datetime DEFAULT NULL,
    `aadhaar_verified_by` int(11) NOT NULL DEFAULT 0 COMMENT 'Person ID',
    `birth_date` date DEFAULT NULL,
    `death_date` date DEFAULT NULL,
    `is_death` TINYINT(1) NOT NULL DEFAULT "0",
    `blood_group_id` TINYINT(1) NULL DEFAULT NULL COMMENT "Blood Group Type ID",
    `education_id` int(11) NULL DEFAULT NULL COMMENT "Education ID",
    `course_id` INT(11) NULL DEFAULT NULL COMMENT 'Course ID',
    `occupation_type_id` int(11) NULL DEFAULT NULL COMMENT "Occupation Type ID",
    `occupation_id` int(11) NULL DEFAULT NULL COMMENT "Occupation ID",
    `state_id` int(11) NULL DEFAULT NULL COMMENT "State ID",
    `dist_id` int(11) NULL DEFAULT NULL COMMENT "District ID",
    `sub_dist_id` int(11) NULL DEFAULT NULL COMMENT "Sub District ID",
    `city_id` int(11) NULL DEFAULT NULL COMMENT "City/Village ID",
    `pin_code` varchar(6) NULL DEFAULT NULL COMMENT "City/Village Pin Code",
    `status` TINYINT(1) DEFAULT "0" NOT NULL COMMENT '0 - inactive - User registered but not activated\r\n1 - active - User is active and can use the system\r\n2 - blocked - User is manually blocked by admin\r\n3 - suspended - Temporarily disabled, maybe for violations\r\n4 - pending - Waiting for admin approval or email verification\r\n5 - deleted - Soft-deleted or marked for removal\r\n6 - banned - Permanently banned due to serious violation',
    `status_at` DATETIME DEFAULT NULL COMMENT "Status Update DateTime",
    `verified` TINYINT(1) DEFAULT "0" NOT NULL COMMENT "0 - not verified\r\n1 - verified",
    `verified_at` DATETIME DEFAULT NULL COMMENT "Verified DateTime",
    `verified_by` int(11) NOT NULL DEFAULT "0" COMMENT "Verified Person ID",
    `created_by` int(11) NOT NULL DEFAULT "0" COMMENT "Create Person ID",
    `updated_by` int(11) NOT NULL DEFAULT "0" COMMENT "Update Person ID",
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,

    FOREIGN KEY (`family_id`) REFERENCES `families` (`id`),
    FOREIGN KEY (`maiden_family_id`) REFERENCES `families` (`id`),
    FOREIGN KEY (`blood_group_id`) REFERENCES `blood_group` (`id`),
    FOREIGN KEY (`education_id`) REFERENCES `education` (`id`),
    FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`),
    FOREIGN KEY (`occupation_type_id`) REFERENCES `occupation_types` (`id`),
    FOREIGN KEY (`occupation_id`) REFERENCES `occupation` (`id`),
    FOREIGN KEY (`state_id`) REFERENCES `state` (`id`),
    FOREIGN KEY (`dist_id`) REFERENCES `dist` (`id`),
    FOREIGN KEY (`sub_dist_id`) REFERENCES `sub_dist` (`id`),
    FOREIGN KEY (`city_id`) REFERENCES `village` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Indexes

ALTER TABLE `registration` ADD INDEX (`family_id`);
ALTER TABLE `registration` ADD INDEX (`maiden_family_id`);
ALTER TABLE `registration` ADD INDEX (`father_id`);
ALTER TABLE `registration` ADD INDEX (`mother_id`);
ALTER TABLE `registration` ADD INDEX (`last_name`);
ALTER TABLE `registration` ADD INDEX (`status`);
ALTER TABLE `registration` ADD INDEX (`verified`);

--
-- Table structure for table `languages`
--

CREATE TABLE `languages` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `title` varchar(30) NOT NULL,
    `language` varchar(10) NOT NULL,
    `code` varchar(2) NOT NULL COMMENT 'ISO 639-1 codes',
    `status` TINYINT(1) DEFAULT 0 NOT NULL,
    `is_default` tinyint(1) NOT NULL DEFAULT '0',
    `created_by` int(11) NOT NULL DEFAULT 0,
    `updated_by` int(11) NOT NULL DEFAULT 0,
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `languages`
--

INSERT INTO `languages` (`id`, `title`, `language`, `code`, `status`, `is_default`, `created_by`, `updated_by`, `created_at`, `updated_at`) VALUES
(1, 'English','english', 'en', 1, 0, 0, 0, NULL, NULL),
(2, 'हिंदी','hindi', 'hi', 1, 0, 0, 0, NULL, NULL),
(3, 'ગુજરાતી','gujarati', 'gu', 1, 0, 0, 0, NULL, NULL);

--
-- Table structure for table `registration_translation`
--

CREATE TABLE `registration_translation` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `registration_id` int(11) NOT NULL,
    `language_code_id` int(11) NOT NULL,
    `first_name` varchar(100) NOT NULL,
    `middle_name` varchar(100) NOT NULL,
    `last_name` varchar(100) NOT NULL,
    `last_name_id` int(11) NOT NULL DEFAULT NULL COMMENT "Surname ID",
    `created_by` INT DEFAULT '0' NOT NULL COMMENT "Create Person ID",
    `updated_by` INT DEFAULT '0' NOT NULL COMMENT "Update Person ID",
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,

    UNIQUE KEY `registration_language_unique` (`registration_id`, `language_code_id`),
    CONSTRAINT `fk_registration_translation_registration` FOREIGN KEY (`registration_id`) REFERENCES `registration` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code_id`) REFERENCES `languages` (`id`),
    FOREIGN KEY (`last_name_id`) REFERENCES `surname` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `family_relationships`
--

CREATE TABLE relation_types (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `reverse_relation` VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert common relation types
INSERT INTO relation_types (id, name, reverse_relation) VALUES
(1, 'father', 'child'),
(2, 'mother', 'child'),
(3, 'child', 'parent'),
(4, 'wife', 'husband'),
(5, 'husband', 'wife'),
(6, 'brother', 'sibling'),
(7, 'sister', 'sibling');

-- 3. Create family_relationships
CREATE TABLE family_relationships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id INT NOT NULL,
    related_person_id INT NOT NULL,
    relation_type_id INT NOT NULL,
    created_at DATETIME DEFAULT NULL,
    FOREIGN KEY (person_id) REFERENCES registration(id),
    FOREIGN KEY (related_person_id) REFERENCES registration(id),
    FOREIGN KEY (relation_type_id) REFERENCES relation_types(id),
    UNIQUE (person_id, related_person_id, relation_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `maiden_name`
--

CREATE TABLE `maiden_name` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `registration_id` INT NOT NULL,
    `first_name` VARCHAR(100),
    `middle_name` VARCHAR(100),
    `last_name` VARCHAR(100),
    `last_name_id` int(11) NOT NULL COMMENT "Surname ID",
    `created_by` INT DEFAULT '0' NOT NULL COMMENT "Create Person ID",
    `updated_by` INT DEFAULT '0' NOT NULL COMMENT "Update Person ID",
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,
    FOREIGN KEY (`registration_id`) REFERENCES `registration` (`id`) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `maiden_name_translation`
--

CREATE TABLE `maiden_name_translation` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `registration_id` INT NOT NULL,
    `language_code_id` INT NOT NULL,
    `first_name` VARCHAR(100),
    `middle_name` VARCHAR(100),
    `last_name` VARCHAR(100),
    `last_name_id` int(11) NOT NULL COMMENT "Surname ID",
    `created_by` INT DEFAULT '0' NOT NULL COMMENT "Create Person ID",
    `updated_by` INT DEFAULT '0' NOT NULL COMMENT "Update Person ID",
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,
    UNIQUE KEY `registration_language_unique` (`registration_id`, `language_code_id`),
    FOREIGN KEY (`registration_id`) REFERENCES `registration` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code_id`) REFERENCES `languages` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `blood_group`
--

CREATE TABLE `blood_group` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `blood_group` varchar(12) NOT NULL,
    `status` TINYINT(1) DEFAULT 0 NOT NULL,
    `created_by` INT DEFAULT '0' NOT NULL COMMENT "Create Person ID",
    `updated_by` INT DEFAULT '0' NOT NULL COMMENT "Update Person ID",
    `created_at` DATETIME NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `blood_group`
--

INSERT INTO `blood_group` (`blood_group`, `status`, `created_by`, `updated_by`, `created_at`, `updated_at`) VALUES
('A Positive', 1, 0, 0, NOW(), null),
('A Negative', 1, 0, 0, NOW(), null),
('B Positive', 1, 0, 0, NOW(), null),
('B Negative', 1, 0, 0, NOW(), null),
('AB Positive', 1, 0, 0, NOW(), null),
('AB Negative', 1, 0, 0, NOW(), null),
('O Positive', 1, 0, 0, NOW(), null),
('O Negative', 1, 0, 0, NOW(), null);

--
-- Table structure for table `education`
--

CREATE TABLE `education` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `education_type` varchar(100) NOT NULL,
    `status` TINYINT(1) DEFAULT 0 NOT NULL,
    `created_by` INT DEFAULT '0' NOT NULL COMMENT "Create Person ID",
    `updated_by` INT DEFAULT '0' NOT NULL COMMENT "Update Person ID",
    `created_at` DATETIME NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `education`
--

INSERT INTO `education` (`education_type`, `status`) VALUES
('Below 7th Standard', 1),
('7th Standard', 1),
('8th Standard', 1),
('9th Standard', 1),
('10th Standard (SSC)', 1),
('11th Standard', 1),
('12th Standard (HSC)', 1),
('Bachelor\'s Degree - Graduate', 1),
('Master\'s Degree - Post Graduate', 1),
('PhD or Equivalent - Doctorate', 1);

CREATE TABLE `surname` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `surname` VARCHAR(100) NOT NULL UNIQUE,
    `status` TINYINT(1) DEFAULT 0 NOT NULL,
    `created_by` INT NOT NULL DEFAULT 0 COMMENT 'Create Person ID',
    `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'Update Person ID',
    `created_at` DATETIME NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `surname` (`surname`, `status`) VALUES ('Nakum', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Rathod', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Parmar', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Herma', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Makvana', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Dodiya', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Sakariya', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Vadher', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Sindhav', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Chauhan', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Solanki', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Zala', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Padhariya', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Padhiyar', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Barad', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Vala', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Kher', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Asvar', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Zankat', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Mori', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Jadav', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Dabhi', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Vanar', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Gohil', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Tank', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Bhadaliya', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Yadav', 1);
INSERT INTO `surname` (`surname`, `status`) VALUES ('Chavda', 1);

CREATE TABLE `surname_translation` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `translation` VARCHAR(100) NOT NULL,
    `surname_id` BIGINT UNSIGNED NOT NULL,
    `language_code_id` INT NOT NULL,
    `created_by` INT NOT NULL DEFAULT 0 COMMENT 'Create Person ID',
    `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'Update Person ID',
    `created_at` DATETIME NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL,
    FOREIGN KEY (`language_code_id`) REFERENCES `languages`(`id`),
    FOREIGN KEY (`surname_id`) REFERENCES `surname`(`id`),
    UNIQUE KEY `unique_surname_language` (`surname_id`, `language_code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('नकुम', 1, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('નકુમ', 1, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('राठोड', 2, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('રાઠોડ', 2, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('परमार', 3, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('પરમાર', 3, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('हेरमा', 4, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('હેરમા', 4, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('मकवाणा', 5, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('મકવાણા', 5, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('डोडिया', 6, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ડોડિયા', 6, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('साकरिया', 7, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('સાકરિયા', 7, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('वाढेर', 8, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('વાઢેર', 8, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('सिंधव', 9, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('સિંધવ', 9, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('चौहाण', 10, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ચૌહાણ', 10, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('सोलंकी', 11, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('સોલંકી', 11, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('झाला', 12, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ઝાલા', 12, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('पढारिया', 13, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('પઢારિયા', 13, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('पढियार', 14, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('પઢિયાર', 14, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('बारड', 15, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('બારડ', 15, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('वाळा', 16, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('વાળા', 16, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('खेर', 17, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ખેર', 17, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('असवार', 18, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('અસવાર', 18, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('जणकाट', 19, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('જણકાટ', 19, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('मोरी', 20, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('મોરી', 20, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('जादव', 21, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('જાદવ', 21, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('डाभी', 22, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ડાભી', 22, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('वनार', 23, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('વનાર', 23, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('गोहिल', 24, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ગોહિલ', 24, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('टांक', 25, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ટાંક', 25, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('भाडलिया', 26, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ભાડલિયા', 26, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('यादव', 27, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('યાદવ', 27, 3);

INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('चावडा', 28, 2);
INSERT INTO `surname_translation` (`translation`,`surname_id`,`language_code_id`) VALUES ('ચાવડા', 28, 3);

-- Create occupation_types table
CREATE TABLE occupation_types (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `status` TINYINT(1) DEFAULT 0 NOT NULL,
    `created_by` INT NOT NULL DEFAULT 0 COMMENT 'Create Person ID',
    `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'Update Person ID',
    `created_at` DATETIME NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert data into occupation_types
INSERT INTO occupation_types (`name`, `status`) VALUES
('Healthcare & Medical', 1),
('Education & Training', 1),
('Information Technology (IT)', 1),
('Engineering & Architecture', 1),
('Finance & Accounting', 1),
('Legal & Law Enforcement', 1),
('Business & Management', 1),
('Arts, Design & Media', 1),
('Skilled Trades & Technical', 1),
('Sales & Customer Service', 1),
('Hospitality & Tourism', 1),
('Transportation & Logistics', 1),
('Agriculture, Food & Natural Resources', 1),
('Science & Research', 1),
('Public Administration & Government', 1),
('Other', 1);

INSERT INTO occupation_types (`name`, `status`) VALUES
('Sports & Recreation', 1),
('Social & Community Services', 1),
('Beauty & Wellness', 1),
('Real Estate & Property', 1),
('Religion & Spirituality', 1),
('Entertainment & Performing Arts', 1),
('Military & Defense', 1),
('Childcare & Early Education', 1),
('Environment & Sustainability', 1),
('Construction & Manual Labor', 1);

-- Create occupations table
CREATE TABLE occupation (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `occupation_type_id` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `status` TINYINT(1) DEFAULT 0 NOT NULL,
    `created_by` INT NOT NULL DEFAULT 0 COMMENT 'Create Person ID',
    `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'Update Person ID',
    `created_at` DATETIME NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL,
    FOREIGN KEY (occupation_type_id) REFERENCES occupation_types(id) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert initial data into occupations
INSERT INTO occupation (`occupation_type_id`, `name`, `status`) VALUES
-- 1. Healthcare & Medical
(1, 'Doctor', 1),
(1, 'Nurse', 1),
(1, 'Surgeon', 1),
(1, 'Pharmacist', 1),
(1, 'Dentist', 1),
(1, 'Medical Laboratory Technician', 1),
(1, 'Physical Therapist', 1),

-- 2. Education & Training
(2, 'Teacher', 1),
(2, 'University Professor', 1),
(2, 'School Counselor', 1),
(2, 'Special Education Teacher', 1),
(2, 'Corporate Trainer', 1),
(2, 'Instructional Designer', 1),
(2, 'Librarian', 1),

-- 3. Information Technology (IT)
(3, 'Software Developer', 1),
(3, 'Systems Analyst', 1),
(3, 'Network Administrator', 1),
(3, 'Data Scientist', 1),
(3, 'Cybersecurity Specialist', 1),
(3, 'IT Support Technician', 1),

-- 4. Engineering & Architecture
(4, 'Civil Engineer', 1),
(4, 'Mechanical Engineer', 1),
(4, 'Electrical Engineer', 1),
(4, 'Architect', 1),
(4, 'Structural Engineer', 1),
(4, 'CAD Technician', 1),

-- 5. Finance & Accounting
(5, 'Accountant', 1),
(5, 'Financial Analyst', 1),
(5, 'Auditor', 1),
(5, 'Tax Consultant', 1),
(5, 'Investment Banker', 1),
(5, 'Bookkeeper', 1),

-- 6. Legal & Law Enforcement
(6, 'Lawyer', 1),
(6, 'Judge', 1),
(6, 'Police Officer', 1),
(6, 'Paralegal', 1),
(6, 'Correctional Officer', 1),
(6, 'Legal Assistant', 1),

-- 7. Business & Management
(7, 'Business Analyst', 1),
(7, 'Marketing Manager', 1),
(7, 'Project Manager', 1),
(7, 'Human Resources Specialist', 1),
(7, 'Chief Executive Officer - CEO', 1),
(7, 'Operations Manager', 1),
(7, 'Entrepreneur - Business Owner', 1),
(7, 'Consultant', 1),

-- 8. Arts, Design & Media
(8, 'Graphic Designer', 1),
(8, 'Photographer', 1),
(8, 'Animator', 1),
(8, 'Art Director', 1),
(8, 'Journalist', 1),
(8, 'Copywriter', 1),
(8, 'Musician', 1),
(8, 'Writer', 1),

-- 9. Skilled Trades & Technical
(9, 'Electrician', 1),
(9, 'Plumber', 1),
(9, 'Carpenter', 1),
(9, 'Welder', 1),
(9, 'Auto Mechanic', 1),
(9, 'HVAC Technician', 1),

-- 10. Sales & Customer Service
(10, 'Sales Representative', 1),
(10, 'Retail Associate', 1),
(10, 'Call Center Agent', 1),
(10, 'Customer Success Manager', 1),
(10, 'Account Executive', 1),

-- 11. Hospitality & Tourism
(11, 'Hotel Manager', 1),
(11, 'Chef', 1),
(11, 'Travel Agent', 1),
(11, 'Tour Guide', 1),
(11, 'Waiter/Waitress', 1),
(11, 'Event Coordinator', 1),

-- 12. Transportation & Logistics
(12, 'Truck Driver', 1),
(12, 'Delivery Person', 1),
(12, 'Warehouse Manager', 1),
(12, 'Supply Chain Analyst', 1),
(12, 'Pilot', 1),
(12, 'Train Operator', 1),

-- 13. Agriculture, Food & Natural Resources
(13, 'Farmer', 1),
(13, 'Agricultural Technician', 1),
(13, 'Food Scientist', 1),
(13, 'Horticulturist', 1),
(13, 'Fishery Worker', 1),

-- 14. Science & Research
(14, 'Biologist', 1),
(14, 'Chemist', 1),
(14, 'Environmental Scientist', 1),
(14, 'Physicist', 1),
(14, 'Research Assistant', 1),

-- 15. Public Administration & Government
(15, 'Policy Analyst', 1),
(15, 'Urban Planner', 1),
(15, 'Government Officer', 1),
(15, 'Diplomat', 1),
(15, 'Customs Officer', 1),

-- 16. Other
(16, 'Other', 1),
(16, 'Homemaker', 1),
(16, 'Student', 1),
(16, 'Unemployed', 1),
(16, 'Retired', 1),

-- 17. Sports & Recreation
(17, 'Athlete', 1),
(17, 'Coach', 1),
(17, 'Fitness Trainer', 1),
(17, 'Sports Official / Referee', 1),

-- 18. Social & Community Services
(18, 'Social Worker', 1),
(18, 'Community Outreach Coordinator', 1),
(18, 'Nonprofit Manager', 1),
(18, 'Substance Abuse Counselor', 1),

-- 19. Beauty & Wellness
(19, 'Hairdresser / Hairstylist', 1),
(19, 'Barber', 1),
(19, 'Massage Therapist', 1),
(19, 'Esthetician', 1),

-- 20. Real Estate & Property
(20, 'Real Estate Agent', 1),
(20, 'Property Manager', 1),
(20, 'Appraiser', 1),

-- 21. Religion & Spirituality
(21, 'Clergy / Pastor', 1),
(21, 'Religious Educator', 1),
(21, 'Imam', 1),
(21, 'Rabbi', 1),

-- 22. Entertainment & Performing Arts
(22, 'Actor / Actress', 1),
(22, 'Comedian', 1),
(22, 'TV Host / Presenter', 1),
(22, 'Stage Performer', 1),

-- 23. Military & Defense
(23, 'Soldier / Military Personnel', 1),
(23, 'Defense Analyst', 1),
(23, 'Intelligence Officer', 1),

-- 24. Childcare & Early Education
(24, 'Daycare Worker', 1),
(24, 'Preschool Teacher', 1),
(24, 'Nanny', 1),

-- 25. Environment & Sustainability
(25, 'Environmental Consultant', 1),
(25, 'Renewable Energy Technician', 1),
(25, 'Climate Scientist', 1),
(25, 'Recycling Coordinator', 1),

-- 26. Construction & Manual Labor
(26, 'Construction Worker', 1),
(26, 'Janitor / Cleaner', 1),
(26, 'Mover', 1);

--
-- Table structure for table `families`
--

CREATE TABLE `families` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `surname_id` INT NOT NULL,
    `family_code` VARCHAR(20) NOT NULL UNIQUE COMMENT 'Unique family code, e.g., NAK-9SFASF',
    `created_by` INT NOT NULL DEFAULT 0 COMMENT 'Create Person ID',
    `updated_by` INT NOT NULL DEFAULT 0 COMMENT 'Update Person ID',
    `created_at` DATETIME NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `deleted_at` DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------

COMMIT;