-- Adminer 4.7.1 MySQL dump 
SET NAMES utf8; 
SET time_zone = '+00:00'; 
SET foreign_key_checks = 0; 
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; 
DROP TABLE IF EXISTS `Applicants`; 
CREATE TABLE `Applicants` ( 
 `ApplicantId` int(11) NOT NULL AUTO_INCREMENT, 
 `FirstName` varchar(255) NOT NULL, 
 `MiddleName` varchar(255) NOT NULL, 
 `LastName` varchar(255) NOT NULL, 
 `DOB` date NOT NULL, 
 `MobileNo` int(11) NOT NULL, 
 `EmailId` varchar(255) NOT NULL, 
 `WhatsappNo` varchar(255) NOT NULL, 
 `OtherContactNumber` varchar(255) NOT NULL, 
 `AadharNo` int(11) NOT NULL, 
 `PANNo` varchar(10) NOT NULL, 
 `FatherName` varchar(255) NOT NULL, 
 `Occupation` varchar(255) NOT NULL, 
 `Age` tinyint(3) NOT NULL, 
 `Gender` tinyint(1) NOT NULL COMMENT '1-male, 2-female, 3- other',  `MarritalStatus` tinyint(1) NOT NULL COMMENT '0- Unmarried, 1- Married, 2- Divorced, 3 - Widow', 
 `EmploymentStatus` tinyint(4) NOT NULL COMMENT '01, Salaried – 02, Regular Wage – 03, Labour – 04, Other – 05', 
 `AvgMonthlyIncome` varchar(255) NOT NULL, 
 `IncomeGroup` tinyint(4) NOT NULL COMMENT '1- EWS, 2- LIG, 3-MIG I 4-MIG II', 
 `Religion` varchar(255) NOT NULL, 
 `CastCategoryId` int(11) NOT NULL, 
 `IsDisabled` tinyint(4) NOT NULL COMMENT '1- Disabled, 0 - Not Disable', 
 `IsExServiceman` tinyint(4) NOT NULL COMMENT '1- Exserviceman, 0-not ex serviceman', 
 `ExServiceType` varchar(255) NOT NULL, 
 `Address1` varchar(255) NOT NULL, 
 `Address2` varchar(255) NOT NULL, 
 `Locality` varchar(255) NOT NULL, 
 `City` varchar(255) NOT NULL, 
 `State` varchar(255) NOT NULL, 
 `Pincode` varchar(255) NOT NULL, 
 `HomeType` tinyint(4) NOT NULL COMMENT '1- Owner,2- Rented,3-Other',  `TypeofHouse` tinyint(4) NOT NULL COMMENT '1- Pucca (CC & Stone Slab), 2- Semi-Pucca (Asbestos/ Steel Sheet, Tiled), 3- Katcha (Grass/thatched, Tarpaulin, Wooden)', 
 `NoOfRoom` tinyint(1) NOT NULL, 
 `ExistingHouseType` tinyint(1) NOT NULL COMMENT '1- Home,2- Flat, 3- Bungalow', 
 `ExistingHouseNumber` smallint(6) NOT NULL, 
 `ExistingHouseLocality` varchar(255) NOT NULL, 
 `ExistingHouseCity` varchar(255) NOT NULL, 
 `ExistingHousePincode` smallint(6) NOT NULL, 
 `ExistingHouseCarpetArea` varchar(255) NOT NULL,
 `YearsofStay` tinyint(3) NOT NULL, 
 `CarpetSize` varchar(255) NOT NULL, 
 `OwnOtherRoom` tinyint(1) NOT NULL COMMENT '0- No, 1- Yes',  `BPLCardNo` varchar(255) NOT NULL, 
 `CreatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, 
 `UpdatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
 PRIMARY KEY (`ApplicantId`)
--  KEY `CastCategoryId` (`CastCategoryId`), 
--  CONSTRAINT `Applicants_ibfk_2` FOREIGN KEY (`CastCategoryId`) REFERENCES `CastCategories` (`CastCatId`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
DROP TABLE IF EXISTS `Applications`; 
CREATE TABLE `Applications` ( 
 `ApplicationId` int(11) NOT NULL AUTO_INCREMENT, 
 `ApplicantId` int(11) NOT NULL, 
 `SchemeId` int(11) NOT NULL, 
 `ReservationId` tinyint(4) NOT NULL, 
 `AccountNo` int(11) NOT NULL, 
 `IFSCCode` varchar(20) NOT NULL, 
 `BankName` varchar(255) NOT NULL, 
 `BranchName` varchar(255) NOT NULL, 
 `ApplicationStatus` tinyint(4) NOT NULL COMMENT '1- completed, 2- verified, 3- Rejected, 4- winner,5 - waitlist', 
 `CreatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, 
 `UpdatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
 PRIMARY KEY (`ApplicationId`)
--  KEY `ApplicantId` (`ApplicantId`), 
--  KEY `SchemeId` (`SchemeId`), 
--  KEY `ReservationId` (`ReservationId`), 
--  CONSTRAINT `Applications_ibfk_1` FOREIGN KEY (`ApplicantId`) REFERENCES `Applicants` (`ApplicantId`), 
--  CONSTRAINT `Applications_ibfk_6` FOREIGN KEY (`SchemeId`) REFERENCES `Schemes` (`SchemeId`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
DROP TABLE IF EXISTS `CastCategories`; 
CREATE TABLE `CastCategories` ( 
 `CastCatId` int(11) NOT NULL AUTO_INCREMENT, 
 `CastCategoryName` varchar(255) NOT NULL, 
 `Status` int(11) NOT NULL, 
 `CreatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
 PRIMARY KEY (`CastCatId`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
DROP TABLE IF EXISTS `Documents`; 
CREATE TABLE `Documents` ( 
 `DocumentId` int(11) NOT NULL AUTO_INCREMENT, 
 `ApplicantId` int(11) NOT NULL, 
 `DocumentName` varchar(255) NOT NULL, 
 `DocumentPath` varchar(255) NOT NULL,
 `DocumentType` varchar(255) NOT NULL, 
 `isValid` tinyint(1) NOT NULL COMMENT '0-No,1-Yes',  `CreatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, 
 `UpdatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
 PRIMARY KEY (`DocumentId`)
--  KEY `ApplicantId` (`ApplicantId`), 
--  CONSTRAINT `Documents_ibfk_1` FOREIGN KEY (`ApplicantId`) REFERENCES `Applicants` (`ApplicantId`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
DROP TABLE IF EXISTS `FamilyDetails`; 
CREATE TABLE `FamilyDetails` ( 
 `FamilyId` int(11) NOT NULL AUTO_INCREMENT, 
 `ApplicantId` int(11) NOT NULL, 
 `Relationship` varchar(255) NOT NULL, 
 `RelativeFullName` varchar(255) NOT NULL, 
 `Gender` tinyint(4) NOT NULL COMMENT '1-male, 2-female, 3- other',  `DOB` date NOT NULL, 
 `Age` tinyint(4) NOT NULL, 
 `Occupation` varchar(255) NOT NULL, 
 `AadharNo` int(11) NOT NULL, 
 `PANNo` varchar(10) NOT NULL, 
 `CreatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, 
 `UpdatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
 PRIMARY KEY (`FamilyId`)
--  KEY `ApplicantId` (`ApplicantId`), 
--  CONSTRAINT `FamilyDetails_ibfk_1` FOREIGN KEY (`ApplicantId`) REFERENCES `Applicants` (`ApplicantId`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
DROP TABLE IF EXISTS `Schemes`; 
CREATE TABLE `Schemes` ( 
 `SchemeId` int(11) NOT NULL AUTO_INCREMENT, 
 `Name` varchar(255) NOT NULL, 
 `Summary` tinytext NOT NULL, 
 `Description` text NOT NULL, 
 `Location` varchar(255) NOT NULL, 
 `Provider` varchar(255) NOT NULL, 
 `Images` json NOT NULL, 
 `StartDate` datetime NOT NULL, 
 `EndDate` datetime NOT NULL, 
 `Status` tinyint(4) NOT NULL COMMENT '0-Inactive, 1- Active',  `CreatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, 
 `UpdatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
 PRIMARY KEY (`SchemeId`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
DROP TABLE IF EXISTS `Transactions`; 
CREATE TABLE `Transactions` ( 
 `TransactionId` int(11) NOT NULL AUTO_INCREMENT, 
 `InvoiceNo` int(11) NOT NULL,
 `ApplicationId` int(11) NOT NULL, 
 `TransactionReferenceNo` varchar(255) NOT NULL,  `AmountPaid` float NOT NULL, 
 `PaymentStatus` tinyint(4) NOT NULL COMMENT '1- Success, 2- Failure', 
 `PaymentMode` varchar(255) NOT NULL, 
 `PaymentGateway` varchar(255) NOT NULL, 
 `CreatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, 
 `UpdatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
 PRIMARY KEY (`TransactionId`)
--  KEY `ApplicationId` (`ApplicationId`), 
--  CONSTRAINT `Transactions_ibfk_1` FOREIGN KEY (`ApplicationId`) REFERENCES `Applications` (`ApplicationId`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
-- 2021-04-18 07:14:14
