
DROP PROCEDURE IF EXISTS spCreateMissingBusinnessMoc2;
DELIMITER //
CREATE PROCEDURE spCreateMissingBusinnessMoc2(IN first_name varchar(100),IN second_name varchar(100),IN third_name varchar(100),
IN fourth_name varchar(100),IN gender varchar(100),IN occupation varchar(100),IN place_of_birth varchar(100),
IN address_line_1 varchar(100),IN district_id int,IN company_name varchar(100),IN company_type_id int, IN license_type_id int,
IN business_class_id int,IN industry_id int,IN agency_license_required varchar(100),IN tin varchar(100),IN expiry_date date, IN issued_date date,
IN registration_date date,IN status varchar(100),IN phone_number_1 varchar(100),IN partyable_type varchar(100),IN provisional char)

BEGIN
DECLARE contact_party_id int;
DECLARE address_id int;
DECLARE company_id int;
DECLARE party_id int;
DECLARE person_id int;
DECLARE person varchar(100) default 'Person';
DECLARE created_by int default 1;
DECLARE updated_at int default issued_date;
DECLARE created_at int default issued_date;
DECLARE track_no int default 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND, SQLWARNING
    BEGIN
		ROLLBACK;
        GET DIAGNOSTICS CONDITION 1 @`errno` = MYSQL_ERRNO, @`sqlstate` = RETURNED_SQLSTATE, @`text` = MESSAGE_TEXT;
        SET @full_error = CONCAT('ERROR ', @`errno`, ' (', @`sqlstate`, '): ', @`text`);
        SELECT track_no, @full_error;
    
    END;
    
	START TRANSACTION;
		SET FOREIGN_KEY_CHECKS = 0;
        SET track_no = 1;
        
        #CREATE CONTACT PERSON DETAILS
		INSERT INTO person(first_name,second_name,third_name,fourth_name,gender,occupation,place_of_birth,created_by,created_at,updated_at) 
		VALUES(upper(first_name),upper(second_name),upper(third_name),upper(fourth_name),gender,occupation,place_of_birth,created_by,created_at,updated_at);
		SET contact_party_id = LAST_INSERT_ID();
        
		SET track_no = 2;
		#CREATE THE COMPANY ADRESS DETAILS
		INSERT INTO address(address_line_1,district_id,created_by,created_at,updated_at) 
					VALUES (address_line_1,district_id,created_by,created_at,updated_at);
		SET address_id = LAST_INSERT_ID();
        
        SET track_no = 3;

		#CREATE THE PARTY PERSON
		INSERT INTO party(phone_number_1,address_id,partyable_id,partyable_type,provisional,created_by,created_at,updated_at) 
		VALUES(phone_number_1,address_id,contact_party_id,person,upper(provisional),created_by,created_at,updated_at);
		SET person_id = LAST_INSERT_ID();
        
        SET track_no = 4;

        #CREATE THE COMPANY DETAILS
		INSERT INTO company(name,contact_party_id,company_type_id,license_type_id,business_class_id,industry_id,
		agency_license_required,tin,expiry_date,issued_date,created_at,status,created_by,updated_at)
		VALUES (upper(company_name),person_id,company_type_id,license_type_id,business_class_id,industry_id,
		upper(agency_license_required),upper(tin),expiry_date,issued_date,registration_date,upper(status),created_by,updated_at);
		SET company_id = LAST_INSERT_ID();
        SET track_no = 5;
        
        #CREATE THE PARTY COMPANY
		INSERT INTO party(phone_number_1,address_id,partyable_id,partyable_type,provisional,created_by,created_at,updated_at) 
		VALUES(phone_number_1,address_id,company_id,partyable_type,upper(provisional),created_by,created_at,updated_at);
		SET party_id = LAST_INSERT_ID();
        SET track_no = 6;
        
		#CREATE THE COMPANY OWNERSHIP
		INSERT INTO company_ownership (party_id,company_id,percentage,created_by,created_at,updated_at)
		VALUES (person_id,party_id,100,created_by,created_at,updated_at);
        
		SET track_no = 7;
        
		#CREATE THE COMPANY REGISTRATION HISTORY
		INSERT INTO company_registration_history (company_id,registration_date,issued_date,expiry_date,created_by,created_at,updated_at)
		VALUES (company_id,registration_date,issued_date,expiry_date,created_by,created_at,updated_at);
        
		SET track_no = 8;
        SET FOREIGN_KEY_CHECKS = 1;
        SET track_no = 9;
        SELECT track_no,company_id,party_id, 'Nowa!!!, Execution completed Successfully.' as 'Status';
	COMMIT;
END //

DELIMITER ;