DELETE FROM public.tb_colonne WHERE nom LIKE 'num_tablette' AND entite LIKE 'ardoc.tb_contenant'__@@__
INSERT INTO public.tb_import (id, table_name, libelle) VALUES (12, 'starged.tb_archive', 'Archives numériques')__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('Code', 'code', 'starged.tb_archive', 1, 'Code de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Code', nom = 'code', entite='starged.tb_archive', import_only=1, description='Code de l''archive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('Titre', 'titre', 'starged.tb_archive', 1, 'Titre de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Titre', nom = 'titre', entite='starged.tb_archive', import_only=1, description='Titre de l''archive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, table_referenced, column_referenced, description)
VALUES ('Classement', 'idclassement', 'starged.tb_archive', 1, 'tb_classement', 'code', 'Code de la série du Plan de classement de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Classement', nom = 'idclassement', entite='starged.tb_archive', import_only=1, table_referenced='tb_classement',  column_referenced='code', description='Code de la série du Plan de classement de l''archive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('DUA', 'dua', 'starged.tb_archive', 1, 'Format YYYY-MM-DD')
ON CONFLICT (id) DO UPDATE SET libelle = 'DUA', nom = 'dua', entite='starged.tb_archive', import_only=1, description='Format YYYY-MM-DD'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('DUC', 'duc', 'starged.tb_archive', 1, 'Format YYYY-MM-DD')
ON CONFLICT (id) DO UPDATE SET libelle = 'DUC', nom = 'duc', entite='starged.tb_archive', import_only=1, description='Format YYYY-MM-DD'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('SOF', 'sof', 'starged.tb_archive', 1, 'Destruction, A définir, Définitive')
ON CONFLICT (id) DO UPDATE SET libelle = 'SOF', nom = 'sof', entite='starged.tb_archive', import_only=1, description='Destruction, A définir, Définitive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('Date archive', 'datearchive', 'starged.tb_archive', 1, 'Format YYYY-MM-DD')
ON CONFLICT (id) DO UPDATE SET libelle = 'Date archive', nom = 'datearchive', entite='starged.tb_archive', import_only=1, description='Format YYYY-MM-DD'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, table_referenced, column_referenced, import_only, description)
VALUES ('Lien du document', 'iddocument', 'starged.tb_archive', 'tb_document', 'id', 1,  'Chemin complet du fichier')
ON CONFLICT (id) DO UPDATE SET libelle = 'Lien du document', nom = 'iddocument', entite='starged.tb_archive', import_only=1, table_referenced='tb_document',  column_referenced='id', description='Chemin complet du fichier'__@@__	

INSERT INTO tb_colonne (libelle, nom, entite, table_referenced, column_referenced, import_only, description)
VALUES ('Niveau de sécurité', 'idsecurite', 'starged.tb_archive', 'tb_securite', 'code', 1, 'Code du niveau de sécurité de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Niveau de sécurité', nom = 'idsecurite', entite='starged.tb_archive', import_only=1, table_referenced='tb_securite',  column_referenced='code', description='Code du niveau de sécurité de l''archive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, table_referenced, column_referenced, import_only, description)
VALUES ('Affaire', 'idaffaire', 'starged.tb_archive', 'tb_affaire', 'code', 1, 'Code de l''affaire de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Affaire', nom = 'idaffaire', entite='starged.tb_archive', import_only=1, table_referenced='tb_affaire',  column_referenced='code', description='Code de l''affaire de l''archive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, table_referenced, column_referenced, import_only, description)
VALUES ('Dossier', 'iddossier', 'starged.tb_archive', 'tb_dossier', 'code', 1, 'Code du dossier de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Dossier', nom = 'iddossier', entite='starged.tb_archive', import_only=1, table_referenced='tb_dossier',  column_referenced='code', description='Code du dossier de l''archive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('Description', 'description', 'starged.tb_archive', 1, 'Description de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Description', nom = 'description', entite='starged.tb_archive', import_only=1, description='Description de l''archive'__@@__

INSERT INTO tb_colonne (libelle, nom, entite, import_only, description)
VALUES ('Description', 'description', 'ardoc.tb_archive', 1, 'Description de l''archive')
ON CONFLICT (id) DO UPDATE SET libelle = 'Description', nom = 'description', entite='ardoc.tb_archive', import_only=1, description='Description de l''archive'__@@__


-- FUNCTION: ardoc.set_import_archive_ts_search()

-- DROP FUNCTION IF EXISTS ardoc.set_import_archive_ts_search();

CREATE OR REPLACE FUNCTION ardoc.set_import_archive_ts_search(
	)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE 
	rec RECORD; 
	rec1 RECORD;
BEGIN
	--Search all archives
	FOR rec IN SELECT id FROM ardoc.tb_archive WHERE ts_search IS NULL
	LOOP
	    SELECT ardoc.set_ts_search(rec.id) INTO rec1;
	END LOOP;
END;
$BODY$__@@__

ALTER FUNCTION ardoc.set_import_archive_ts_search()
    OWNER TO postgres__@@__


-- FUNCTION: ardoc.set_import_contenant_ts_search()

-- DROP FUNCTION IF EXISTS ardoc.set_import_contenant_ts_search();

CREATE OR REPLACE FUNCTION ardoc.set_import_contenant_ts_search(
	)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE 
	rec RECORD; 
	rec1 RECORD;
BEGIN
	--Search all archives
	FOR rec IN SELECT id FROM ardoc.tb_contenant WHERE ts_search IS NULL
	LOOP
	    SELECT ardoc.set_ts_search_contenant(rec.id) INTO rec1;
	END LOOP;
END;
$BODY$__@@__

ALTER FUNCTION ardoc.set_import_contenant_ts_search()
    OWNER TO postgres__@@__
	
-- FUNCTION: melbox.set_import_courrier_ts_search()

-- DROP FUNCTION IF EXISTS melbox.set_import_courrier_ts_search();

CREATE OR REPLACE FUNCTION melbox.set_import_courrier_ts_search(
	)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE 
	rec RECORD; 
	rec1 RECORD;
BEGIN
	--Search all archives
	FOR rec IN SELECT id FROM melbox.tb_courrier WHERE ts_search IS NULL
	LOOP
	    SELECT melbox.set_ts_search(rec.id) INTO rec1;
	END LOOP;
END;
$BODY$__@@__

ALTER FUNCTION melbox.set_import_courrier_ts_search()
    OWNER TO postgres__@@__
	
	
-- FUNCTION: starged.set_import_archive_ts_search()

-- DROP FUNCTION IF EXISTS starged.set_import_archive_ts_search();

CREATE OR REPLACE FUNCTION starged.set_import_archive_ts_search(
	)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE 
	rec RECORD; 
	rec1 RECORD;
BEGIN
	--Search all archives
	FOR rec IN SELECT id FROM starged.tb_archive WHERE ts_search IS NULL
	LOOP
	    SELECT starged.set_ts_search(rec.id) INTO rec1;
	END LOOP;
END;
$BODY$__@@__

ALTER FUNCTION starged.set_import_archive_ts_search()
    OWNER TO postgres__@@__
	
SELECT ardoc.set_import_archive_ts_search() __@@__

SELECT ardoc.set_import_contenant_ts_search() __@@__

UPDATE tb_import set libelle = 'Archives physiques' WHERE table_name LIKE 'ardoc.tb_archive' __@@__

ALTER TABLE starged.tb_archive ADD COLUMN idardoc integer __@@__

ALTER TABLE starged.tb_archive ADD CONSTRAINT fk_tb_archive_tb_ardoc FOREIGN KEY (idardoc) REFERENCES ardoc.tb_archive (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE __@@__

ALTER TABLE starged.tb_archive ADD COLUMN estarchive integer default 0 __@@__

INSERT INTO public.tb_droit(id, libelle, description, estclassique, application)
	VALUES (80, 'Transfert archives physiques au SAE', 'Droit de transférer les archives physiques dans le système d''archivage électronique', 1, 3) __@@__
	
INSERT INTO public.tb_droit(id, libelle, description, estclassique, application)
	VALUES (81, 'Transfert courriers au SAE', 'Droit de transférer les courriers dans le système d''archivage électronique', 1, 1) __@@__