begin;

update menu_ref set me_menu='Journal' where me_code='CFGLED';

CREATE OR REPLACE FUNCTION comptaproc.four_upper_letter()
    RETURNS trigger
AS $function$
begin
    new.dc_code=transform_to_code(new.dc_code);
    new.dc_code:=substr(new.dc_code,1,4);
    return new;
END;
$function$
LANGUAGE plpgsql;

COMMENT ON FUNCTION comptaproc.four_upper_letter() IS 'Cut to the 4 first letter in uppercase';


DROP TABLE if exists document_component ;

CREATE TABLE document_component (
                                    dc_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, -- PK
                                    dc_code text NOT NULL, -- Code used in document_modele
                                    dc_comment text not null , -- description
                                    CONSTRAINT document_component_pk PRIMARY KEY (dc_id),
                                    CONSTRAINT document_component_un UNIQUE (dc_code)
);
COMMENT ON TABLE public.document_component IS 'Give the component of NOALYSS that is using is';

-- Column comments

COMMENT ON COLUMN public.document_component.dc_id IS 'PK';
COMMENT ON COLUMN public.document_component.dc_code IS 'Code used in document_modele';
COMMENT ON COLUMN public.document_component.dc_comment IS 'Code used in document_modele';

-- Table Triggers

create trigger t_code before insert
    or update on
    public.document_component for each row execute function comptaproc.four_upper_letter();


INSERT INTO document_component (dc_comment, dc_code) VALUES('Journaux achat', 'ACH');
INSERT INTO document_component (dc_comment, dc_code) VALUES('Journaux vente', 'VEN');
INSERT INTO document_component (dc_comment, dc_code) VALUES('Gestion', 'GES');

ALTER TABLE public.document_modele ADD CONSTRAINT document_modele_fk FOREIGN KEY (md_affect) REFERENCES public.document_component(dc_code) ON UPDATE CASCADE;

insert into version (val,v_description) values (185,'Document Component : Type of Document Model in a table');
commit;