-- Customer module migration for existing Gold Loan databases.
-- Run this once after the branch module migration.

CREATE TABLE IF NOT EXISTS `customer_document_types` (
  `dt_id` int NOT NULL AUTO_INCREMENT,
  `dt_name` varchar(80) NOT NULL,
  `dt_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `dt_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dt_id`),
  UNIQUE KEY `dt_name` (`dt_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT IGNORE INTO `customer_document_types` (`dt_name`, `dt_status`) VALUES
('Aadhaar', 'active'),
('PAN', 'active'),
('Voter ID', 'active'),
('Driving License', 'active');

CREATE TABLE IF NOT EXISTS `customers` (
  `cust_id` int NOT NULL AUTO_INCREMENT,
  `cust_branch_id` int DEFAULT NULL,
  `cust_application_date` date DEFAULT NULL,
  `cust_name` varchar(120) NOT NULL,
  `cust_father_name` varchar(120) NOT NULL,
  `cust_mother_name` varchar(120) NOT NULL,
  `cust_dob` date DEFAULT NULL,
  `cust_gender` enum('Male','Female','Other') DEFAULT NULL,
  `cust_address` text NOT NULL,
  `cust_address_district` varchar(80) DEFAULT NULL,
  `cust_address_state` varchar(80) DEFAULT NULL,
  `cust_address_pincode` varchar(10) DEFAULT NULL,
  `cust_permanent_address` text NOT NULL,
  `cust_permanent_district` varchar(80) DEFAULT NULL,
  `cust_permanent_state` varchar(80) DEFAULT NULL,
  `cust_permanent_pincode` varchar(10) DEFAULT NULL,
  `cust_aadhaar` varchar(20) NOT NULL,
  `cust_pan` varchar(20) NOT NULL,
  `cust_phone` varchar(20) NOT NULL,
  `cust_email` varchar(120) NOT NULL,
  `cust_nationality` varchar(60) DEFAULT NULL,
  `cust_occupation` varchar(60) DEFAULT NULL,
  `cust_occupation_other` varchar(120) DEFAULT NULL,
  `cust_employer_sector` varchar(80) DEFAULT NULL,
  `cust_annual_income` varchar(40) DEFAULT NULL,
  `cust_residence_type` varchar(60) DEFAULT NULL,
  `cust_identity_proofs` varchar(255) DEFAULT NULL,
  `cust_address_proofs` varchar(255) DEFAULT NULL,
  `cust_application_place` varchar(100) DEFAULT NULL,
  `cust_declaration_date` date DEFAULT NULL,
  `cust_photo` varchar(255) DEFAULT NULL,
  `cust_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `cust_created_by` int DEFAULT NULL,
  `cust_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cust_id`),
  KEY `cust_branch_id` (`cust_branch_id`),
  KEY `cust_created_by` (`cust_created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `customer_documents` (
  `doc_id` int NOT NULL AUTO_INCREMENT,
  `doc_customer_id` int NOT NULL,
  `doc_type_id` int NOT NULL,
  `doc_file` varchar(255) NOT NULL,
  `doc_original_name` varchar(255) NOT NULL,
  `doc_uploaded_by` int DEFAULT NULL,
  `doc_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`doc_id`),
  KEY `doc_customer_id` (`doc_customer_id`),
  KEY `doc_type_id` (`doc_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;