-- Adds organisation membership/application form fields to existing customers table.
-- Run this if customer_module.sql was imported before these fields were added.

DROP PROCEDURE IF EXISTS add_customer_column;

DELIMITER //
CREATE PROCEDURE add_customer_column(IN column_name VARCHAR(64), IN column_definition TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'customers'
      AND COLUMN_NAME = column_name
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `customers` ADD COLUMN ', column_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;

CALL add_customer_column('cust_application_date', '`cust_application_date` date DEFAULT NULL AFTER `cust_branch_id`');
CALL add_customer_column('cust_dob', '`cust_dob` date DEFAULT NULL AFTER `cust_mother_name`');
CALL add_customer_column('cust_gender', "`cust_gender` enum('Male','Female','Other') DEFAULT NULL AFTER `cust_dob`");
CALL add_customer_column('cust_address_district', '`cust_address_district` varchar(80) DEFAULT NULL AFTER `cust_address`');
CALL add_customer_column('cust_address_state', '`cust_address_state` varchar(80) DEFAULT NULL AFTER `cust_address_district`');
CALL add_customer_column('cust_address_pincode', '`cust_address_pincode` varchar(10) DEFAULT NULL AFTER `cust_address_state`');
CALL add_customer_column('cust_permanent_district', '`cust_permanent_district` varchar(80) DEFAULT NULL AFTER `cust_permanent_address`');
CALL add_customer_column('cust_permanent_state', '`cust_permanent_state` varchar(80) DEFAULT NULL AFTER `cust_permanent_district`');
CALL add_customer_column('cust_permanent_pincode', '`cust_permanent_pincode` varchar(10) DEFAULT NULL AFTER `cust_permanent_state`');
CALL add_customer_column('cust_nationality', '`cust_nationality` varchar(60) DEFAULT NULL AFTER `cust_email`');
CALL add_customer_column('cust_occupation', '`cust_occupation` varchar(60) DEFAULT NULL AFTER `cust_nationality`');
CALL add_customer_column('cust_occupation_other', '`cust_occupation_other` varchar(120) DEFAULT NULL AFTER `cust_occupation`');
CALL add_customer_column('cust_employer_sector', '`cust_employer_sector` varchar(80) DEFAULT NULL AFTER `cust_occupation_other`');
CALL add_customer_column('cust_annual_income', '`cust_annual_income` varchar(40) DEFAULT NULL AFTER `cust_employer_sector`');
CALL add_customer_column('cust_residence_type', '`cust_residence_type` varchar(60) DEFAULT NULL AFTER `cust_annual_income`');
CALL add_customer_column('cust_identity_proofs', '`cust_identity_proofs` varchar(255) DEFAULT NULL AFTER `cust_residence_type`');
CALL add_customer_column('cust_address_proofs', '`cust_address_proofs` varchar(255) DEFAULT NULL AFTER `cust_identity_proofs`');
CALL add_customer_column('cust_application_place', '`cust_application_place` varchar(100) DEFAULT NULL AFTER `cust_address_proofs`');
CALL add_customer_column('cust_declaration_date', '`cust_declaration_date` date DEFAULT NULL AFTER `cust_application_place`');

DROP PROCEDURE IF EXISTS add_customer_column;