--
-- Table structure for table `app_address`
--

DROP TABLE IF EXISTS `app_address`;
CREATE TABLE `app_address` (
  `Address_Id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Address_Type` varchar(50) NOT NULL,
  `Number_And_Street` varchar(250) DEFAULT NULL,
  `Barangay` varchar(100) DEFAULT NULL,
  `City` varchar(100) DEFAULT NULL,
  `State` varchar(100) DEFAULT NULL,
  `Region` varchar(100) DEFAULT NULL,
  `Country` varchar(100) DEFAULT NULL,
  `Country_Code` varchar(10) DEFAULT NULL,
  `Zip_Code` varchar(10) DEFAULT NULL,
  `LandMark` varchar(100) DEFAULT NULL,
  `Comments` varchar(100) DEFAULT NULL,
  `Date_Since_Residing` varchar(20) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Is_Preferred_Address` tinyint DEFAULT NULL,
  `Is_High_Risk_Area` tinyint DEFAULT NULL,
  `Is_Active` tinyint DEFAULT '1',
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_Rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Address_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_asset_details`
--

DROP TABLE IF EXISTS `app_asset_details`;
CREATE TABLE `app_asset_details` (
  `Asset_Id` bigint(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Asset_Number` varchar(30) DEFAULT NULL,
  `Asset_Description` varchar(100) DEFAULT NULL,
  `Product_Id` bigint DEFAULT NULL,
  `Status` varchar(50) DEFAULT NULL,
  `Quantity` int DEFAULT NULL,
  `Price` float DEFAULT NULL,
  `Purchase_Date` datetime DEFAULT NULL,
  `Valid_Upto` datetime DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Asset_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_consent_details`
--

DROP TABLE IF EXISTS `app_consent_details`;
CREATE TABLE `app_consent_details` (
  `Consent_Details_Id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Consent_Type` varchar(50) DEFAULT NULL,
  `Consent_Capture_Source` varchar(50) DEFAULT NULL,
  `Consent_Channel` varchar(50) DEFAULT NULL,
  `Status` tinyint DEFAULT NULL,
  `Comments` varchar(100) DEFAULT NULL,
  `Consent_Details` json DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Consent_Details_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_contact`
--

DROP TABLE IF EXISTS `app_contact`;
CREATE TABLE `app_contact` (
  `Contact_Id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Contact_Type` varchar(50) DEFAULT NULL,
  `Contact_Value` varchar(150) DEFAULT NULL,
  `Contact_Period` varchar(20) DEFAULT NULL,
  `Is_Preferred` tinyint DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Is_Active` tinyint DEFAULT '1',
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Contact_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_device_details`
--

DROP TABLE IF EXISTS `app_device_details`;
CREATE TABLE `app_device_details` (
  `Device_id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(255) NOT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Device_IP` varchar(15) DEFAULT NULL,
  `Latitude` decimal(10,8) DEFAULT NULL,
  `Longitude` decimal(11,8) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_document_details`
--

DROP TABLE IF EXISTS `app_document_details`;
CREATE TABLE `app_document_details` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(255) NOT NULL,
  `doctype` varchar(255) DEFAULT NULL,
  `doc_url` varchar(255) DEFAULT NULL,
  `Lead_Id` varchar(255) NOT NULL,
  `partner_score` varchar(255) DEFAULT NULL,
  `partner_screening_ekyc` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_kyc_details`
--

	DROP TABLE IF EXISTS `app_kyc_details`;
	CREATE TABLE `app_kyc_details` (
	  `KYC_Details_Id` int NOT NULL AUTO_INCREMENT,
	  `Application_Id` varchar(36) NOT NULL,
	  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
	  `Document_Id` varchar(40) DEFAULT NULL,
	  `Document_Type` varchar(40) DEFAULT NULL,
	  `Issuing_Authority` varchar(100) DEFAULT NULL,
	  `Valid_Until` varchar(20) DEFAULT NULL,
	  `National_Id` varchar(75) DEFAULT NULL,
	  `Zoloz_Details` json DEFAULT NULL,
	  `Source` varchar(50) DEFAULT NULL,
	  `Channel` varchar(100) DEFAULT NULL,
	  `Is_Active` tinyint DEFAULT '1',
	  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	  `Created_By` varchar(50) NOT NULL,
	  `Is_Latest_rec` varchar(1) DEFAULT 'N',
	  PRIMARY KEY (`KYC_Details_Id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_lead`
--

DROP TABLE IF EXISTS `app_lead`;
CREATE TABLE `app_lead` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `CW_Transaction_Id` varchar(255) DEFAULT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `First_Name` varchar(50) NOT NULL,
  `Last_Name` varchar(50) DEFAULT NULL,
  `Middle_Name` varchar(50) DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `Gender` varchar(20) DEFAULT NULL,
  `Salutation` varchar(20) DEFAULT NULL,
  `Suffix` varchar(32) DEFAULT NULL,
  `Civil_Status` varchar(20) DEFAULT NULL,
  `Place_Of_Birth` varchar(75) DEFAULT NULL,
  `Nationality` varchar(75) DEFAULT NULL,
  `No_Of_Dependents` varchar(32) DEFAULT NULL,
  `Arn_No` varchar(32) DEFAULT NULL,
  `Idm_Arn_No` varchar(45) DEFAULT NULL,
  `Party_Status` varchar(75) DEFAULT NULL,
  `Is_DOSRI` tinyint DEFAULT NULL,
  `Is_RPT` tinyint DEFAULT NULL,
  `Is_FATCA` tinyint DEFAULT NULL,
  `Party_Type` varchar(30) DEFAULT NULL,
  `Preferred_Address_Id` varchar(50) DEFAULT NULL,
  `fatca_W9_IdType` varchar(100) DEFAULT NULL,
  `fatca_W9_IdNumber` varchar(100) DEFAULT NULL,
  `App_Product_Type` varchar(50) DEFAULT NULL,
  `App_Party_Type` varchar(50) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(50) DEFAULT NULL,
  `Channel_Type` varchar(50) DEFAULT NULL,
  `Product_Type` varchar(50) DEFAULT NULL,
  `sssGsis` varchar(75) DEFAULT NULL,
  `tinId` varchar(75) DEFAULT NULL,
  `ReferralCode` varchar(50) DEFAULT NULL,
  `PromotionalCode` varchar(50) DEFAULT NULL,
  `application_stage` varchar(75) DEFAULT NULL,
  `application_status` varchar(75) DEFAULT NULL,
  `application_status_code` varchar(10) DEFAULT NULL,
  `External_Application_Number` varchar(75) DEFAULT NULL,
  `External_Application_Name` varchar(75) DEFAULT NULL,
  `Product_applied` varchar(75) DEFAULT NULL,
  `agentName` varchar(255) DEFAULT NULL,
  `agentCode` varchar(255) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `my_Job` varchar(255) DEFAULT NULL,
  `my_Job_Code` varchar(255) DEFAULT NULL,
  `Is_Record_Edited` tinyint DEFAULT NULL,
  `Is_Active` tinyint DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Updated_Date` datetime DEFAULT NULL,
  `Updated_By` varchar(50) DEFAULT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_lead_audit_details`
--

DROP TABLE IF EXISTS `app_lead_audit_details`;
CREATE TABLE `app_lead_audit_details` (
  `Id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Change_log` json NOT NULL COMMENT 'Store change informations:first_name old_value:XYZ New Value:ABCD ,Table_name:Party',
  `Actions` varchar(15) NOT NULL COMMENT 'Customer_Audit should hold the changes happened to the customer. We should update old audit history with new audit of the Case to Case_Audit.(WHO,WHAT and When) ',
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Audit Table ';

--
-- Table structure for table `app_lead_status`
--

DROP TABLE IF EXISTS `app_lead_status`;
CREATE TABLE `app_lead_status` (
  `Status_Id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `Lead_Id` bigint(10) unsigned zerofill NOT NULL,
  `Status_Type` varchar(100) NOT NULL,
  `Status_Flag` tinyint DEFAULT '0',
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Status_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_occupation`
--

DROP TABLE IF EXISTS `app_occupation`;
CREATE TABLE `app_occupation` (
  `Occupation_Id` bigint NOT NULL AUTO_INCREMENT,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Application_Id` varchar(36) NOT NULL,
  `Organization` varchar(100) DEFAULT NULL,
  `Industry_Type` varchar(75) DEFAULT NULL,
  `Industry_Type_Code` varchar(30) DEFAULT NULL,
  `Occupation_Type` varchar(75) DEFAULT NULL,
  `Occupation_Type_Code` varchar(30) DEFAULT NULL,
  `Occupation_Status` varchar(75) DEFAULT NULL,
  `Occupation_Status_Code` varchar(30) DEFAULT NULL,
  `Nature_Of_Occupation` varchar(100) DEFAULT NULL,
  `Nature_Of_Occupation_Code` varchar(32) DEFAULT NULL,
  `Nature_Of_Work` varchar(100) DEFAULT NULL,
  `Nature_Of_Work_Code` varchar(32) DEFAULT NULL,
  `Date_Of_Commencement` varchar(30) DEFAULT NULL,
  `Monthly_Income` varchar(20) DEFAULT NULL,
  `Annual_Income` varchar(20) DEFAULT NULL,
  `Fund_Source_Name` varchar(75) DEFAULT NULL,
  `Fund_Source_Code` varchar(75) DEFAULT NULL,
  `Source_Of_Wealth` varchar(100) DEFAULT NULL,
  `Proof_Of_SOW` varchar(100) DEFAULT NULL,
  `Party_Linked_Companies` varchar(200) DEFAULT NULL,
  `Party_Banks` varchar(200) DEFAULT NULL,
  `Proof_Of_Address` varchar(100) DEFAULT NULL,
  `Proof_Of_Income` varchar(50) DEFAULT NULL,
  `Proof_Of_Billing` varchar(100) DEFAULT NULL,
  `Salary_Period` varchar(50) DEFAULT NULL,
  `Salary_Dates` varchar(50) DEFAULT NULL,
  `Profession` varchar(50) DEFAULT NULL,
  `Company_Category_Segment` varchar(50) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Is_Active` tinyint DEFAULT '1',
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Occupation_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_referral_promotion`
--

DROP TABLE IF EXISTS `app_referral_promotion`;
CREATE TABLE `app_referral_promotion` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Code` varchar(50) DEFAULT NULL,
  `Code_Type` varchar(50) DEFAULT NULL,
  `Source_Type` varchar(200) DEFAULT NULL,
  `Source_Code` varchar(50) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `app_relationship`
--

DROP TABLE IF EXISTS `app_relationship`;
CREATE TABLE `app_relationship` (
  `Relationship_Id` int NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) NOT NULL,
  `Lead_Id` bigint(16) unsigned zerofill NOT NULL,
  `Relationship_Type` varchar(30) DEFAULT NULL,
  `Name` varchar(300) DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `Nationality` varchar(100) DEFAULT NULL,
  `Address` json DEFAULT NULL,
  `Share_Percentage` bigint DEFAULT NULL,
  `Is_Nominee` varchar(1) DEFAULT NULL,
  `Nominee_Index` varchar(1) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Relationship_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `asset_details`
--

DROP TABLE IF EXISTS `asset_details`;
CREATE TABLE `asset_details` (
  `Asset_Id` bigint NOT NULL AUTO_INCREMENT,
  `Application_Id` varchar(36) DEFAULT NULL,
  `Asset_Description` varchar(255) DEFAULT NULL,
  `Asset_Number` varchar(255) DEFAULT NULL,
  `Price` float DEFAULT NULL,
  `Product_Id` bigint DEFAULT NULL,
  `Purchase_Date` datetime(6) DEFAULT NULL,
  `Quantity` int DEFAULT NULL,
  `Status` varchar(255) DEFAULT NULL,
  `Valid_Upto` datetime(6) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `Channel` varchar(100) DEFAULT NULL,
  `Created_Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Created_By` varchar(50) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT 'N',
  PRIMARY KEY (`Asset_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `partner_payload`
--

DROP TABLE IF EXISTS `partner_payload`;
CREATE TABLE `partner_payload` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Transaction_Id` varchar(100) NOT NULL,
  `Partner_Name` varchar(100) NOT NULL,
  `Payload` json DEFAULT NULL,
  `Idempotency_Key` varchar(255) DEFAULT NULL,
  `Status` varchar(255) DEFAULT NULL,
  `Created_Date` datetime DEFAULT NULL,
  `Created_By` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`,`Transaction_Id`,`Partner_Name`),
  UNIQUE KEY `UniqueTransactionIdAndPartnerName` (`Transaction_Id`,`Partner_Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `partner_rule`
--

DROP TABLE IF EXISTS `partner_rule`;
CREATE TABLE `partner_rule` (
  `id` int NOT NULL AUTO_INCREMENT,
  `partner_name` varchar(50) NOT NULL,
  `partner_code` varchar(50) DEFAULT NULL,
  `nature_of_partnership` varchar(50) DEFAULT NULL,
  `process_name` varchar(255) DEFAULT NULL,
  `process_url` varchar(255) DEFAULT NULL,
  `product` varchar(255) DEFAULT NULL,
  `mandatory_fields` json DEFAULT NULL,
  `is_kyc_required` bit(1) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `created_by` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `product_details`
--

DROP TABLE IF EXISTS `product_details`;
CREATE TABLE `product_details` (
  `Product_Id` bigint NOT NULL AUTO_INCREMENT,
  `Product_Code` varchar(30) NOT NULL,
  `Product_Name` varchar(50) NOT NULL,
  `Product_Description` varchar(200) NOT NULL,
  `Product_Start_Date` datetime NOT NULL,
  `Product_End_Date` datetime NOT NULL,
  `Created_Date` datetime NOT NULL,
  `Created_By` varchar(45) NOT NULL,
  `Is_Latest_rec` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`Product_Id`),
  UNIQUE KEY `Product_Code_UNIQUE` (`Product_Code`),
  UNIQUE KEY `Product_Name_UNIQUE` (`Product_Name`),
  UNIQUE KEY `Product_Description_UNIQUE` (`Product_Description`),
  UNIQUE KEY `Product_Id_UNIQUE` (`Product_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;