drop procedure updateintoconsumers_sp;

CREATE OR REPLACE PROCEDURE updateintoconsumers_sp
    (consumer_id_in IN NUMBER default NULL,
    consumer_email_in IN VARCHAR2 default NULL,
    source_ip_in IN VARCHAR2 default NULL,
    host_name_in IN VARCHAR2 default NULL,
    initial_offer_id_in IN NUMBER default NULL,
    consumer_id_out OUT NUMBER default NULL)
AS

bConsumerIDSet BOOLEAN := FALSE;
iConsumerCount NUMBER := 0;


BEGIN
    IF consumer_id_in IS NOT NULL THEN
        IF consumer_id_in > 0 THEN
            bConsumerIDSet := TRUE;
        ELSE
            bConsumerIDSet := FALSE;
        END IF;
    ELSE
        bConsumerIDSet := FALSE;
    END IF;
    
    /* Check if the consumer already exists and either update it or insert it (consumer_id_in IS NOT NULL) AND (consumer_id_in >= 0) */
    IF bConsumerIDSet THEN
        iConsumerCount := (SELECT COUNT(*) FROM consumers WHERE consumer_id = consumer_id_in);
        
        IF iConsumerCount > 0 THEN
            /* Update */
            UPDATE consumers SET consumer_email = consumer_email_in, source_ip = source_ip_in, host_name=host_name_in
            WHERE consumer_id = consumer_id_in;
            
            consumer_id_out := consumer_id_in;
        ELSE
            /* Insert */
            INSERT INTO consumers ( consumer_id, ip_address, host_name, initial_offer_id, create_date, consumer_email )
            VALUES ( consumers_seq.nextval, ip_address_in, host_name_in, initial_offer_id_in, SYSDATE, consumer_email_in )
            RETURNING consumer_id INTO consumer_id_out;
        END IF;
    ELSE
        /* Try again with the email address */
        iConsumerCount := (SELECT COUNT(*) FROM consumers WHERE consumer_email = consumer_email_in);
        IF iConsumerCount > 0 THEN
            /* Update */
            UPDATE consumers SET source_ip = source_ip_in, host_name=host_name_in
            WHERE consumer_email = consumer_email_in;
            consumer_id_out := (SELECT consumer_id FROM consumers WHERE consumer_email = consumer_email_in);
        ELSE
            /* Insert */
            INSERT INTO consumers ( consumer_id, ip_address, host_name, initial_offer_id, create_date, consumer_email )
            VALUES ( consumers_seq.nextval, ip_address_in, host_name_in, initial_offer_id_in, SYSDATE, consumer_email_in )
            RETURNING consumer_id INTO consumer_id_out;
        END IF;
    END IF;

EXCEPTION
  WHEN OTHERS THEN
    consumer_id_out := NULL;

END updateintoconsumers_sp;