--se pierden campos exclusivos esto es cuando cargo para dar de alta

--dar de baja lo quita de usr_business
--cuando doy de alta y esta flagueado a cero no lo da de alta (campo up en usr_business)
/*
DELETE FROM usr_suppliers WHERE "id" ='139';

INSERT INTO usr_business 
    (code,firstname,lastname,emails,phones,addresses,customer)VALUES
    ('minda','Julia','Grande',
        '[{"id":"252","person_id":0,"type":1,"content":"minda@gmail.com","sort":1}]',
        '[{"id":"250","person_id":0,"type":2,"content":"7054 7029","sort":1}]',
        '[{"id":"253","person_id":0,"type":3,"content":"caton nuevo eden","sort":1}]',true) RETURNING id AS rv

INSERT INTO usr_business 
(code,firstname,lastname,no_resolucion,serie_documento,emails,phones,addresses,customer)
VALUES('2','Francisco','Cruz',
'[{"id":"0","person_id":"83","type":4,"content":"res1","sort":1}]',
'[{"id":"0","person_id":"83","type":5,"content":"ser 1","sort":1}]',
'[]','[]','[]',true) 

UPDATE usr_business SET code='minda',firstname='Julia',lastname='Grande',dui=null,nit=null,county=null,state=null,country=null,register_no='00000-0',no_resolucion='[{"id":"0","person_id":"83","type":4,"content":"res1","sort":1}]',serie_documento='[{"id":"0","person_id":"83","type":5,"content":"ser 1","sort":1}]',emails='[{"id":"252","person_id":"139","type":1,"content":"minda@gmail.com","sort":1}]',phones='[{"id":"250","person_id":"139","type":2,"content":"7054 7029","sort":1}]',addresses='[{"id":"253","person_id":"139","type":3,"content":"caton nuevo eden","sort":1}]' WHERE id = '139';
UPDATE usr_business SET 
	code='freund',firstname='Ferreterías Freund S.A. de C.V.',lastname=null,
	dui='DUIDEFREUND',nit=null,
	county=null,state=null,country=null,register_no='0000-000',
	no_resolucion='[]',
	serie_documento='[{"id":"0","person_id":"83","type":5,"content":"serIE # 1","sort":1}]',
	emails='[]',phones='[]',addresses='[{"id":"0","person_id":"83","type":3,"content":"eN VARIAS PARTES","sort":1}]',
    SUPPLIER=TRUE
WHERE id = '83';

*/
CREATE OR REPLACE FUNCTION public.insert_update_delete_usr_business()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
            iId integer=0;
            iCnt integer=0;
            iCnt2 integer=0;
            iCnt3 integer=0;
            bUp boolean=true;
        BEGIN
            IF TG_OP = 'INSERT' THEN
                SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code;
                IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra
                    IF NEW.id IS NULL THEN
                        iId=nextval('usr_persons_id_seq'::regclass);
                    ELSE
                        iId=NEW.id;
                    END IF;
                    INSERT INTO usr_persons (id, code, firstname, lastname,dui,nit)
                        SELECT iId, NEW.code, NEW.firstname, NEW.lastname,NEW.dui,NEW.nit;
                    INSERT INTO usr_x_business (id,type,register_no,supplier,customer)
                        SELECT iId, NEW.type, NEW.register_no, NEW.supplier, NEW.customer;   
                ELSE --si ya está en la tabla maestra
                    SELECT id INTO iId FROM usr_persons WHERE code=NEW.code;
                    UPDATE usr_persons SET id=iId, code=NEW.code, firstname=NEW.firstname, lastname=NEW.lastname, dui=NEW.dui, nit=NEW.nit  WHERE id=iId;
                    SELECT count(id) INTO iCnt2 FROM usr_x_business WHERE id=iId;
                    IF NOT iCnt2::BOOLEAN THEN --si no está en la tabla x             
                        INSERT INTO usr_x_business (id,type,register_no,supplier,customer)
                            SELECT iId, NEW.type, NEW.register_no, NEW.supplier, NEW.customer; 
                    ELSE
                        UPDATE usr_x_business SET type=NEW.type,register_no=NEW.register_no,up=1 WHERE id=iId;
                        IF  NOT NEW.supplier IS NULL AND NEW.supplier::BOOL THEN
                            UPDATE usr_x_business SET supplier=TRUE WHERE id=iId;
                        END IF;
                        IF  NOT NEW.customer IS NULL AND NEW.customer::BOOL THEN
                            UPDATE usr_x_business SET customer=TRUE WHERE id=iId;
                        END IF;
                    END IF;                    
                END IF;
                DELETE FROM  public.usr_x_data WHERE person_id = iId AND type IN (1,2,3);--email,phone,address's
                IF NEW.supplier::BOOL THEN
                    DELETE FROM  public.usr_x_data WHERE person_id = iId AND type IN (4,5);--no_resolucion's,serie_documento's
                END IF;
                IF NEW.emails IS NOT NULL THEN      
                    PERFORM update_usr_data_json(NEW.emails,1,iId);    
                END IF;
                IF NEW.phones IS NOT NULL THEN          
                    PERFORM update_usr_data_json(NEW.phones,2,iId);      
                END IF;
                IF NEW.addresses IS NOT NULL THEN          
                    PERFORM update_usr_data_json(NEW.addresses,3,iId);   
                END IF;
                IF NEW.no_resolucion IS NOT NULL THEN                      
                    PERFORM update_usr_data_json(NEW.no_resolucion,4,iId);   
                END IF;
                IF NEW.serie_documento IS NOT NULL THEN 
                    PERFORM update_usr_data_json(NEW.serie_documento,5,iId);   
                END IF;
            ELSIF TG_OP = 'UPDATE' THEN
                SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code AND id<>OLD.id;
                IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra ese code porque no es repetible
                    UPDATE usr_persons SET id=NEW.id, code=NEW.code, firstname=NEW.firstname, lastname=NEW.lastname, dui=NEW.dui, nit=NEW.nit  WHERE id=OLD.id;
                    UPDATE usr_x_business SET type=NEW.type, register_no=NEW.register_no,supplier=NEW.supplier,customer=NEW.customer  WHERE id=OLD.id;
                    IF NEW.id IS NOT NULL THEN
                        iId=NEW.id;
                    ELSE
                        IF OLD.id IS NOT NULL THEN
                            iId=OLD.id;
                        ELSE
                            SELECT id INTO iId FROM usr_persons WHERE code=NEW.id;
                        END IF;                     
                    END IF;
                    DELETE FROM  public.usr_x_data WHERE person_id = iId AND type IN (1,2,3);--email,phone,address's
                    IF NEW.emails IS NOT NULL THEN      
                        PERFORM update_usr_data_json(NEW.emails,1,iId);    
                    END IF;
                    IF NEW.phones IS NOT NULL THEN          
                        PERFORM update_usr_data_json(NEW.phones,2,iId);      
                    END IF;
                    IF NEW.addresses IS NOT NULL THEN          
                        PERFORM update_usr_data_json(NEW.addresses,3,iId);   
                    END IF;
                    IF NEW.no_resolucion IS NOT NULL THEN          
                        PERFORM update_usr_data_json(NEW.no_resolucion,4,iId);   
                    END IF;
                    IF NEW.serie_documento IS NOT NULL THEN          
                        PERFORM update_usr_data_json(NEW.serie_documento,5,iId);   
                    END IF;
                END IF;
            ELSIF TG_OP = 'DELETE' THEN
                UPDATE usr_x_business SET up = 0
                    WHERE OLD.id = usr_x_business.id;
                RETURN NULL;
            END IF;
            RETURN NEW;
        END;
$BODY$;

CREATE OR REPLACE FUNCTION public.series_documento(
	employee integer,
	sort_ integer)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE
    ret_val text='';
BEGIN
	SELECT public.get_usr_data(employee,5,sort_) INTO ret_val;
RETURN ret_val;
END;
$BODY$;

CREATE OR REPLACE FUNCTION public.series_documento(
	employee integer)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE
    ret_val text='';
BEGIN
	SELECT public.get_usr_data(employee,5,1) INTO ret_val;
RETURN ret_val;
END;
$BODY$;


CREATE OR REPLACE FUNCTION public.resoluciones(
	employee integer,
	sort_ integer)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE
    ret_val text='';
BEGIN
	SELECT public.get_usr_data(employee,4,sort_) INTO ret_val;
RETURN ret_val;
END;
$BODY$;

CREATE OR REPLACE FUNCTION public.resoluciones(
	employee integer)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE
    ret_val text='';
BEGIN
	SELECT public.get_usr_data(employee,4,1) INTO ret_val;
RETURN ret_val;
END;
$BODY$;

DROP VIEW public.usr_labs;

DROP VIEW public.usr_suppliers;

DROP VIEW public.usr_customers;

DROP VIEW public.usr_business_plus;

DROP VIEW public.usr_business;


CREATE OR REPLACE VIEW public.usr_business
 AS
 SELECT u.id,
    u.code,
    u.firstname,
    u.lastname,
    u.short_name,
    u.dui,
    u.nit,
    u.county,
    u.state,
    u.country,
    x.type,
    x.register_no,
    x.supplier,
    x.customer,
    data_(u.id, 1) AS emails,
    data_(u.id, 2) AS phones,
    data_(u.id, 3) AS addresses,
    data_(u.id, 4) AS no_resolucion,
    data_(u.id, 5) AS serie_documento
   FROM usr_persons u
     LEFT JOIN usr_x_business x USING (id)
  WHERE x.up::boolean;


CREATE TRIGGER insert_update_delete_usr_business_trg
    INSTEAD OF INSERT OR DELETE OR UPDATE 
    ON public.usr_business
    FOR EACH ROW
    EXECUTE FUNCTION public.insert_update_delete_usr_business();


CREATE OR REPLACE VIEW public.usr_business_plus
 AS
 SELECT u.id,
    u.code,
    u.firstname,
    u.lastname,
    u.short_name,
    full_name(u.firstname, u.lastname, u.short_name) AS full_name,
    u.dui,
    u.nit,
    u.county,
    u.state,
    u.country,
    u.type,
    u.register_no,
    u.supplier,
    u.customer,
    phone(u.id) AS phone,
    email(u.id) AS email,
    address(u.id) AS address,
    resoluciones(u.id) AS no_resolucion,
    series_documento(u.id) AS serie_documento,
    ( SELECT balance(1, u.id::numeric) AS balance) AS balance
   FROM usr_business u;
CREATE OR REPLACE VIEW public.usr_customers
 AS
 SELECT u.id,
    u.code,
    u.firstname,
    u.lastname,
    u.short_name,
    u.full_name,
    u.dui,
    u.nit,
    u.county,
    u.state,
    u.country,
    u.type,
    u.register_no,
    u.email,
    u.phone,
    u.address,
    u.balance AS balance_
   FROM usr_business_plus u
  WHERE u.customer;


CREATE OR REPLACE VIEW public.usr_suppliers
 AS
 SELECT u.id,
    u.code,
    u.firstname,
    u.lastname,
    u.short_name,
    u.full_name,
    u.dui,
    u.nit,
    u.county,
    u.state,
    u.country,
    u.type,
    u.register_no,
    u.email,
    u.phone,
    u.address,
    u.no_resolucion,
    u.serie_documento,
    u.balance
   FROM usr_business_plus u
  WHERE u.supplier;

CREATE OR REPLACE VIEW public.usr_labs
 AS
 SELECT u.id,
    u.code,
    u.firstname,
    u.lastname,
    u.short_name,
    u.dui,
    u.nit,
    u.county,
    u.state,
    u.country,
    u.register_no,
    u.type
   FROM usr_business u
     LEFT JOIN usr_x_labs x USING (id)
  WHERE x.up::boolean;

ALTER TABLE public.usr_labs
    OWNER TO postgres;


CREATE TRIGGER insert_update_delete_usr_labs_trg
    INSTEAD OF INSERT OR DELETE OR UPDATE 
    ON public.usr_labs
    FOR EACH ROW
    EXECUTE FUNCTION public.insert_update_delete_usr_labs();
-------------------------------------

ALTER FUNCTION public.insert_delete_usr_customers()
    RENAME TO delete_usr_customers;

ALTER FUNCTION public.insert_delete_usr_suppliers()
    RENAME TO delete_usr_suppliers;

CREATE TRIGGER delete_usr_customers_trg
    INSTEAD OF DELETE 
    ON public.usr_customers
    FOR EACH ROW
    EXECUTE FUNCTION public.delete_usr_customers();

CREATE TRIGGER delete_usr_suppliers_trg
    INSTEAD OF DELETE 
    ON public.usr_suppliers
    FOR EACH ROW
    EXECUTE FUNCTION public.delete_usr_suppliers();