CREATE TABLE `otps` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `medium` varchar(255),
  `medium_code` varchar(255),
  `valid_till` timestamp,
  `is_verified` char(1) DEFAULT "N",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `api_token` text,
  `remember_token` text,
  `mobile` varchar(32) UNIQUE NOT NULL,
  `country_code` int(3) DEFAULT "91",
  `first_name` varchar(255),
  `middle_name` varchar(255),
  `last_name` varchar(255),
  `alias` varchar(255),
  `co_name` int,
  `co_relation` ENUM ('FATHER', 'MOTHER', 'GUARDIAN'),
  `dob` date NOT NULL,
  `gender` char NOT NULL,
  `address` int,
  `email` varchar(32),
  `phone` varchar(32),
  `user_web_profile_id` int,
  `data_source_id` int UNIQUE NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employers` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employer_custom_id` varchar(255),
  `source_type` ENUM ('B2C', 'B2B'),
  `user_id` int DEFAULT null,
  `email` varchar(255) UNIQUE NOT NULL,
  `mobile` varchar(24),
  `phone` varchar(24),
  `country_code` int(3) DEFAULT "91",
  `b2b_company_name` varchar(255),
  `b2b_brand_name` varchar(255),
  `b2b_gst_no` varchar(255),
  `b2b_pan_no` varchar(255),
  `b2b_website` varchar(255),
  `employer_type` ENUM ('SCHOOL', 'SME', 'FMCG', 'RETAIL', 'GOVERNMENT'),
  `billing_plan_id` int,
  `is_compliant` char(1) DEFAULT "N",
  `status` ENUM ('A', 'I') DEFAULT "I",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employees` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employee_custom_id` varchar(16),
  `employee_type_id` int,
  `employee_code` varchar(16),
  `user_id` int,
  `reports_to` int,
  `email` varchar(255) UNIQUE NOT NULL,
  `doj` date,
  `employee_address_id` int,
  `status` ENUM ('A', 'I') DEFAULT "I",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `visitors` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employer_id` int NOT NULL,
  `first_name` varchar(255),
  `middle_name` varchar(255),
  `last_name` varchar(255),
  `mobile` varchar(32) UNIQUE NOT NULL,
  `country_code` int(3) DEFAULT "91",
  `frequency` ENUM ('ONE_TIME', 'DAILY_MF', 'DAILY_MS'),
  `lang` int,
  `time_zone` varchar(24) DEFAULT "Asia/Kolkata",
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employee_types` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `type` varchar(255),
  `description` varchar(255),
  `source` ENUM ('B2C', 'B2B', 'BOT'),
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `jarvis_users` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `user_id` int,
  `email` varchar(64),
  `password` varchar(128),
  `is_super_admin` char(1) DEFAULT "N",
  `source` ENUM ('B2C', 'B2B', 'BOT'),
  `status` ENUM ('A', 'I') DEFAULT "I",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `b2b_users` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `user_id` int,
  `email` varchar(64),
  `password` varchar(128),
  `status` ENUM ('A', 'I') DEFAULT "I",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_addresses` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employee_id` int,
  `employer_id` int,
  `type` ENUM ('PERMANENT', 'CURRENT', 'OLD'),
  `street_addr1` varchar(255),
  `street_addr2` varchar(255),
  `village` varchar(255),
  `post_office` varchar(255),
  `police_station` varchar(255),
  `district` varchar(255),
  `near_by` varchar(255),
  `city` varchar(255),
  `state` varchar(255),
  `pincode` varchar(255),
  `country` varchar(255),
  `stayed_from` date,
  `stayed_to` date,
  `stayed_from_text` varchar(64),
  `stayed_to_text` varchar(64),
  `is_verified` char(1) DEFAULT "N",
  `verified_by` int,
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_prefs` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employer_id` int DEFAULT null,
  `employee_id` int DEFAULT null,
  `lang` int,
  `locale` varchar(50) DEFAULT "en_GB",
  `date_pattern` varchar(12) DEFAULT "YYYY-MM-DD",
  `time_format` varchar(12) DEFAULT "HH:MM:SS",
  `time_zone` varchar(24) DEFAULT "Asia/Kolkata",
  `notify_by_sms` char(1) DEFAULT "Y",
  `notify_by_email` char(1) DEFAULT "Y",
  `notify_by_wa` char(1) DEFAULT "Y",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_pics` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `user_id` int,
  `photo_url` varchar(255),
  `uploaded_by` int,
  `is_verified` char(1) DEFAULT "N",
  `verified_by` int,
  `verification_date` datetime,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_docs` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `user_id` int,
  `doc_type_id` int,
  `doc_number` varchar(255),
  `doc_url` varchar(255) NOT NULL,
  `cosmos_id` varchar(255) UNIQUE NOT NULL,
  `uploaded_by` int,
  `is_verified` char(1) DEFAULT "N",
  `verified_by` int,
  `verification_date` datetime,
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_web_profiles` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `fb_id` varchar(64),
  `fb_connection_id` varchar(255),
  `li_id` varchar(64),
  `li_connection_id` varchar(255),
  `twtr_id` varchar(64),
  `twtr_connection_id` varchar(255),
  `other_ids` varchar(255),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_nofitications` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `nf_medium` ENUM ('B2C_APP', 'B2B_APP', 'SMS', 'WA'),
  `nf_to` int NOT NULL,
  `nf_message` varchar(255),
  `nf_action_url` varchar(255),
  `nf_is_sent` char(1) DEFAULT "N",
  `nf_sent_on` datetime,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employee_employment_history` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employee_id` int NOT NULL,
  `employed_by` int NOT NULL,
  `salary` bigint,
  `employment_start` date NOT NULL,
  `employment_stop` date,
  `is_verified` char(1) DEFAULT "N",
  `verified_by` int,
  `verification_date` datetime,
  `is_approved` char(1) DEFAULT "N",
  `approved_on` timestamp,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employer_employee_network` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employer_id` int,
  `its_employee` int,
  `status` ENUM ('A', 'I'),
  `is_approved` char(1) DEFAULT "N",
  `approved_on` timestamp,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employee_reviews` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `rating` int,
  `review` varchar(255),
  `status` ENUM ('A', 'I'),
  `reviewed_by` int NOT NULL,
  `verified_by` int NOT NULL,
  `verification_date` datetime,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `er_ee_requests` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employer_id` int NOT NULL,
  `employee_id` int NOT NULL,
  `request_dir` ENUM ('ER_TO_EE', 'EE_TO_ER'),
  `request_type` ENUM ('DOCS', 'ECLAIM', 'PROFILE'),
  `doc_type_id` int,
  `doc_id` int,
  `request_last_sent` datetime,
  `request_done` char(1) DEFAULT "N",
  `request_status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employee_lookup_histories` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `employee_id` int NOT NULL,
  `employee_type_id` int NOT NULL,
  `doc_type_id` int,
  `latitude` varchar(255),
  `longitude` varchar(255),
  `ip_address` varchar(255),
  `browser_info` varchar(255),
  `lookup_by` int NOT NULL,
  `status` ENUM ('A', 'I'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `verification_types` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` ENUM ('NID_AADHAAR', 'NID_PAN', 'NID_DL', 'BGV', 'CRC', 'AV_PHYSICAL', 'AV_POSTAL', 'AV_DIGITAL', 'PV_WRITTEN', 'PV_VERBAL') UNIQUE NOT NULL,
  `internal_name` varchar(255),
  `icon_url` varchar(255),
  `description` varchar(255),
  `source` ENUM ('B2C', 'B2B', 'BOT'),
  `status` ENUM ('A', 'I'),
  `tat` int(10),
  `task_deps` varchar(255),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `orders` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `order_display_ids` varchar(255),
  `order_dispaly_desc` varchar(255),
  `status` ENUM ('CREATED', 'IN_PROGRESS', 'WAITING_MORE_DATA', 'ERROR', 'CANCELLED', 'COMPLETE'),
  `priority` ENUM ('NORMAL', 'MEDIUM', 'HIGH') DEFAULT "NORMAL",
  `tat` date,
  `employer_id` int NOT NULL,
  `employee_id` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_history` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `action` ENUM ('CREATED', 'UPDATED'),
  `order_id` int NOT NULL,
  `order_status` ENUM ('CREATED', 'IN_PROGRESS', 'WAITING_MORE_DATA', 'ERROR', 'CANCELLED', 'COMPLETE'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `task_types` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `task_type` ENUM ('AADHAAR_VERIFICATION', 'PAN_VERIFICATION', 'WEB_CHECK', 'CRC', 'AV_PHYSICAL', 'AV_POSTAL', 'AV_DIGITAL', 'PV_WRITTEN', 'PV_VERBAL') UNIQUE NOT NULL,
  `task_desc` varchar(255),
  `tat` int,
  `document_deps` varchar(255),
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `case_types` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `case_type` varchar(128),
  `category` ENUM ('CIVIL', 'CRIMINAL'),
  `description` varchar(255),
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `section_acts` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255),
  `category` ENUM ('CIVIL', 'CRIMINAL'),
  `description` varchar(255),
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_tasks` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `task_display_id` varchar(255),
  `order_id` int NOT NULL,
  `task_type` int NOT NULL,
  `employer_id` int NOT NULL,
  `employee_id` int NOT NULL,
  `priority` ENUM ('NORMAL', 'MEDIUM', 'HIGH') DEFAULT "NORMAL",
  `tat` date,
  `status` ENUM ('CREATED', 'IN_PROGRESS', 'WAITING_MORE_DATA', 'SENT_TO_TP', 'ERROR', 'CANCELLED', 'COMPLETE'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `task_history` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `action` ENUM ('CREATED', 'UPDATED'),
  `task_id` int NOT NULL,
  `task_status` ENUM ('CREATED', 'IN_PROGRESS', 'WAITING_MORE_DATA', 'SENT_TO_TP', 'ERROR', 'CANCELLED', 'COMPLETE'),
  `assigned_to` int NOT NULL,
  `action_by` int NOT NULL,
  `candidate_data` varchar(255),
  `antecedants_data` varchar(255),
  `report_url` varchar(255),
  `severity` ENUM ('GREEN', 'YELLOW', 'RED', 'INCONCLUSIVE'),
  `severity_conclusion` varchar(255),
  `verification_date` datetime,
  `verified_by` int,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `severity_messages` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `task_severity_message` varchar(255),
  `status` ENUM ('A', 'I'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `task_type_severities` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `task_type_id` int,
  `task_severity_id` int,
  `task_severity` ENUM ('GREEN', 'YELLOW', 'RED', 'INCONCLUSIVE'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `task_history_docs` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `task_history_id` int NOT NULL,
  `document_url` varchar(255),
  `document_name` varchar(255),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `document_types` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255),
  `source` ENUM ('B2C', 'B2B', 'BOT'),
  `status` ENUM ('A', 'I'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `languages` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `data_sources` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `source_name` varchar(255) NOT NULL,
  `campaign_name` varchar(255),
  `ip_address` varchar(255),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `api_active_sessions` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `api_version` varchar(255),
  `device_id` varchar(255),
  `mac_address` varchar(255),
  `ip_address` varchar(255) NOT NULL,
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `api_keys` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `api_key` varchar(255) UNIQUE NOT NULL,
  `api_system_name` varchar(255),
  `created_by` int NOT NULL,
  `verified_by` int NOT NULL,
  `status` ENUM ('A', 'I') DEFAULT "I",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `urls` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `url_code` varchar(24),
  `full_url` varchar(255),
  `hits` int,
  `valid_till` timestamp,
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `promocodes` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `reward` double(10,2),
  `hits` int,
  `quantity` int(11),
  `data` text,
  `valid_till` timestamp,
  `for_user` int DEFAULT null,
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `billable_tasks_addons` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `billiable_verification_type_id` int,
  `title` varchar(128),
  `amount` double,
  `status` ENUM ('A', 'I'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `billable_verification_tasks` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `verification_type_id` int,
  `amount` double,
  `tax` int,
  `source` ENUM ('B2C', 'B2B', 'BOT'),
  `status` ENUM ('A', 'I'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `billing_plan_tasks` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `billing_plan_id` int,
  `billable_tasks` int,
  `amount` double,
  `status` ENUM ('A', 'I'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `billing_plans` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `name` varchar(32) NOT NULL,
  `description` varchar(255),
  `status` ENUM ('A', 'I') DEFAULT "A",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `billing_invoice` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `invoice_number` varchar(32),
  `employer_id` int,
  `invoice_date` timestamp,
  `status` ENUM ('DRAFT', 'OPEN', 'PAID'),
  `place_of_supply` char(2),
  `gst_number` varchar(16),
  `payment_terms` int,
  `payment_terms_label` varchar(12),
  `due_date` timestamp,
  `currency_code` varchar(3),
  `exchange_rate` double,
  `sub_total` double,
  `is_inclusive_tax` char(1),
  `cgst` double,
  `sgst` double,
  `igst` double,
  `total` double,
  `payment_method` ENUM ('CASH', 'CC', 'NETBANKING', 'UPI', 'CHEQUE'),
  `payment_ref` varchar(64),
  `notes` varchar(255),
  `payment_status` ENUM ('PENDING', 'SUCCESS') DEFAULT "DRAFT",
  `reminders_sent` int,
  `attachment_url` varchar(128),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `billing_invoice_line_item` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `billing_line_item_text` varchar(128),
  `billing_line_item_amount` double,
  `billing_line_item_cgst` double,
  `billing_line_item_sgst` double,
  `billing_line_item_igst` double,
  `billing_line_item_quantity` int,
  `billing_line_item_sub_total` double,
  `order_task_id` int,
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `surveys` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `survey_type` ENUM ('HEALTH_CHECK', 'EMPLOYEE_FEEDBACK', 'EMPLOYER_FEEDBACK'),
  `employee_id` int NOT NULL,
  `employer_id` int NOT NULL,
  `visitor_id` int NOT NULL,
  `survey_start` timestamp,
  `survey_end` timestamp,
  `survey_status` ENUM ('DRAFT', 'IN_PROGRESS', 'COMPLETE'),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `survey_questions` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `text` varchar(255),
  `possible_answers` varchar(255),
  `status` ENUM ('A', 'I') DEFAULT "I",
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `survey_answers` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `survey_id` int NOT NULL,
  `question_id` int NOT NULL,
  `question_answer` varchar(64),
  `created_at` timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` timestamp DEFAULT null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `acts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `act_name` varchar(255) NOT NULL,
  `act_no` int NOT NULL,
  `act_description` text NOT NULL,
  `status` enum('A','I') NOT NULL DEFAULT 'A',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sections` (
  `id` int NOT NULL AUTO_INCREMENT,
  `act_id` int NOT NULL,
  `section_details` text NOT NULL,
  `status` enum('A','I') DEFAULT 'A',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=694 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `users` ADD FOREIGN KEY (`co_name`) REFERENCES `users` (`id`);

ALTER TABLE `sections` ADD FOREIGN KEY (`act_id`) REFERENCES `acts` (`id`);

ALTER TABLE `users` ADD FOREIGN KEY (`address`) REFERENCES `user_addresses` (`id`);

ALTER TABLE `users` ADD FOREIGN KEY (`user_web_profile_id`) REFERENCES `user_web_profiles` (`id`);

ALTER TABLE `users` ADD FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`);

ALTER TABLE `employers` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `employers` ADD FOREIGN KEY (`billing_plan_id`) REFERENCES `billing_plans` (`id`);

ALTER TABLE `employees` ADD FOREIGN KEY (`employee_type_id`) REFERENCES `employee_types` (`id`);

ALTER TABLE `employees` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `employees` ADD FOREIGN KEY (`reports_to`) REFERENCES `users` (`id`);

ALTER TABLE `employees` ADD FOREIGN KEY (`employee_address_id`) REFERENCES `user_addresses` (`id`);

ALTER TABLE `visitors` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `visitors` ADD FOREIGN KEY (`lang`) REFERENCES `languages` (`id`);

ALTER TABLE `jarvis_users` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `b2b_users` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `user_addresses` ADD FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

ALTER TABLE `user_addresses` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `user_addresses` ADD FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

ALTER TABLE `user_prefs` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `user_prefs` ADD FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

ALTER TABLE `user_prefs` ADD FOREIGN KEY (`lang`) REFERENCES `languages` (`id`);

ALTER TABLE `user_pics` ADD FOREIGN KEY (`user_id`) REFERENCES `employees` (`id`);

ALTER TABLE `user_pics` ADD FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`);

ALTER TABLE `user_pics` ADD FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

ALTER TABLE `user_docs` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `user_docs` ADD FOREIGN KEY (`doc_type_id`) REFERENCES `document_types` (`id`);

ALTER TABLE `user_docs` ADD FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`);

ALTER TABLE `user_docs` ADD FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

ALTER TABLE `user_nofitications` ADD FOREIGN KEY (`nf_to`) REFERENCES `users` (`id`);

ALTER TABLE `employee_employment_history` ADD FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

ALTER TABLE `employee_employment_history` ADD FOREIGN KEY (`employed_by`) REFERENCES `employers` (`id`);

ALTER TABLE `employee_employment_history` ADD FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

ALTER TABLE `employer_employee_network` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `employer_employee_network` ADD FOREIGN KEY (`its_employee`) REFERENCES `employees` (`id`);

ALTER TABLE `employee_reviews` ADD FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`);

ALTER TABLE `employee_reviews` ADD FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

ALTER TABLE `er_ee_requests` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `er_ee_requests` ADD FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

ALTER TABLE `er_ee_requests` ADD FOREIGN KEY (`doc_type_id`) REFERENCES `document_types` (`id`);

ALTER TABLE `er_ee_requests` ADD FOREIGN KEY (`doc_id`) REFERENCES `user_docs` (`id`);

ALTER TABLE `employee_lookup_histories` ADD FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

ALTER TABLE `employee_lookup_histories` ADD FOREIGN KEY (`employee_type_id`) REFERENCES `employee_types` (`id`);

ALTER TABLE `employee_lookup_histories` ADD FOREIGN KEY (`doc_type_id`) REFERENCES `document_types` (`id`);

ALTER TABLE `employee_lookup_histories` ADD FOREIGN KEY (`lookup_by`) REFERENCES `users` (`id`);

ALTER TABLE `orders` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `orders` ADD FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

ALTER TABLE `order_history` ADD FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);

ALTER TABLE `order_tasks` ADD FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);

ALTER TABLE `order_tasks` ADD FOREIGN KEY (`task_type`) REFERENCES `task_types` (`id`);

ALTER TABLE `order_tasks` ADD FOREIGN KEY (`employer_id`) REFERENCES `orders` (`employer_id`);

ALTER TABLE `order_tasks` ADD FOREIGN KEY (`employee_id`) REFERENCES `orders` (`employee_id`);

ALTER TABLE `task_history` ADD FOREIGN KEY (`task_id`) REFERENCES `order_tasks` (`id`);

ALTER TABLE `task_history` ADD FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`);

ALTER TABLE `task_history` ADD FOREIGN KEY (`action_by`) REFERENCES `users` (`id`);

ALTER TABLE `task_history` ADD FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

ALTER TABLE `task_type_severities` ADD FOREIGN KEY (`task_type_id`) REFERENCES `task_types` (`id`);

ALTER TABLE `task_type_severities` ADD FOREIGN KEY (`task_severity_id`) REFERENCES `severity_messages` (`id`);

ALTER TABLE `task_history_docs` ADD FOREIGN KEY (`task_history_id`) REFERENCES `task_history` (`id`);

ALTER TABLE `api_keys` ADD FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

ALTER TABLE `api_keys` ADD FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

ALTER TABLE `promocodes` ADD FOREIGN KEY (`for_user`) REFERENCES `users` (`id`);

ALTER TABLE `billable_tasks_addons` ADD FOREIGN KEY (`billiable_verification_type_id`) REFERENCES `billable_verification_tasks` (`id`);

ALTER TABLE `billable_verification_tasks` ADD FOREIGN KEY (`verification_type_id`) REFERENCES `verification_types` (`id`);

ALTER TABLE `billing_plan_tasks` ADD FOREIGN KEY (`billing_plan_id`) REFERENCES `billing_plans` (`id`);

ALTER TABLE `billing_plan_tasks` ADD FOREIGN KEY (`billable_tasks`) REFERENCES `billable_verification_tasks` (`id`);

ALTER TABLE `billing_invoice` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `billing_invoice_line_item` ADD FOREIGN KEY (`order_task_id`) REFERENCES `order_tasks` (`id`);

ALTER TABLE `surveys` ADD FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`);

ALTER TABLE `surveys` ADD FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`);

ALTER TABLE `surveys` ADD FOREIGN KEY (`visitor_id`) REFERENCES `visitors` (`id`);

ALTER TABLE `survey_answers` ADD FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`id`);

ALTER TABLE `survey_answers` ADD FOREIGN KEY (`question_id`) REFERENCES `survey_questions` (`id`);
