
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET row_security = off;

SET default_tablespace = '';

SET search_path TO $schema, public, pg_catalog;


CREATE DOMAIN $schema.d_a_code_insee AS character varying
	CONSTRAINT code_insee_check CHECK (((VALUE)::text ~ ('^\d{5}$'::character varying)::text));




COMMENT ON DOMAIN $schema.d_a_code_insee IS 'Code INSEE sur 5 chiffres';



CREATE DOMAIN $schema.d_a_code_postal AS character varying
	CONSTRAINT code_postal_check CHECK (((VALUE)::text ~ ('^\d{5}$'::character varying)::text));




COMMENT ON DOMAIN $schema.d_a_code_postal IS 'Code postal sur 5 chiffres';



CREATE DOMAIN $schema.d_a_email AS character varying;




COMMENT ON DOMAIN $schema.d_a_email IS 'Adresse email';



CREATE DOMAIN $schema.d_a_siren AS text
	CONSTRAINT siren_check CHECK ((VALUE ~ '^[0-9]{3}[- .]?[0-9]{3}[- .]?[0-9]{3}$'::text));




COMMENT ON DOMAIN $schema.d_a_siren IS 'Numero SIREN sur 9 chiffres, contigus ou groupes (xxx xxx xxx) séparés par des espaces, points ou tirets';



CREATE DOMAIN $schema.d_a_siret AS text
	CONSTRAINT siret_check CHECK ((VALUE ~ '^[0-9]{3}[- .]?[0-9]{3}[- .]?[0-9]{3}[- .]?[0-9]{5}$'::text));




COMMENT ON DOMAIN $schema.d_a_siret IS 'Numero SIRET sur 14 chiffres, contigus ou groupes (xxx xxx xxx xxxxx) séparés par des espaces, points ou tirets';



CREATE DOMAIN $schema.d_a_telephone AS character varying
	CONSTRAINT d_a_telephone_check CHECK (((VALUE)::text ~ '^((00[- .]?[0-9]{2}|\+[0-9]{2})?[- .]?[0-9]{1}[- .]?[0-9]{2}[- .]?[0-9]{2}[- .]?[0-9]{2}[- .]?[0-9]{2})|([0-9]{2}[- .]?[0-9]{2}[- .]?[0-9]{2}[- .]?[0-9]{2}[- .]?[0-9]{2})$'::text));




COMMENT ON DOMAIN $schema.d_a_telephone IS 'Numero de téléphone sur 10,ou 11 chiffres, contigus ou séparés par des espaces, points ou tirets';


CREATE TABLE $schema.repertoires (
    id serial PRIMARY KEY,
    nom text NOT NULL,
    chemin text NOT NULL,
    base boolean DEFAULT false NOT NULL,
    utile boolean DEFAULT true NOT NULL,
    ordre smallint
);


CREATE TABLE $schema.cerfa_14435_defauts (
    cle character varying(32) NOT NULL,
    valeur text,
    description text
);
COMMENT ON TABLE $schema.cerfa_14435_defauts IS 'Paramétrage des valeurs par défaut pour remplir le formulaire de réponse CERFA';


CREATE TABLE $schema.dec_contacts (
    id serial PRIMARY KEY,
    nom text,
    tel $schema.d_a_telephone,
    mob $schema.d_a_telephone,
    fax $schema.d_a_telephone,
    email $schema.d_a_email,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone
);
COMMENT ON TABLE $schema.dec_contacts IS 'Table des contacts de déclarants DTDICT';

COMMENT ON COLUMN $schema.dec_contacts.nom IS 'Nom du contact déclarant';
COMMENT ON COLUMN $schema.dec_contacts.tel IS 'Téléphone du contact déclarant';
COMMENT ON COLUMN $schema.dec_contacts.fax IS 'Fax du contact déclarant';
COMMENT ON COLUMN $schema.dec_contacts.email IS 'Email contact déclarantant';
COMMENT ON COLUMN $schema.dec_contacts.created_at IS 'Horodatage création';
COMMENT ON COLUMN $schema.dec_contacts.updated_at IS 'Horodatage modification';

CREATE TABLE $schema.declarants (
    id serial PRIMARY KEY,
    denomination text,
    adresse2 text,
    no_voie text,
    lieudit_bp text,
    code_postal $schema.d_a_code_postal,
    commune text,
    pays text,
    type_entite text,
    siret character varying(32),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone
);
COMMENT ON TABLE $schema.declarants IS 'Table des déclarants DTDICT';

COMMENT ON COLUMN $schema.declarants.denomination IS 'Dénomination déclarant';
COMMENT ON COLUMN $schema.declarants.adresse2 IS 'Complément adresse déclarant';
COMMENT ON COLUMN $schema.declarants.no_voie IS 'N° voie déclarant';
COMMENT ON COLUMN $schema.declarants.lieudit_bp IS 'Lieu-dit, boite postale déclarant';
COMMENT ON COLUMN $schema.declarants.code_postal IS 'Code postal déclarant';
COMMENT ON COLUMN $schema.declarants.commune IS 'Commune déclarant';
COMMENT ON COLUMN $schema.declarants.pays IS 'Pays déclarant';
COMMENT ON COLUMN $schema.declarants.type_entite IS 'Type entité déclarant (PERSONNE MORALE, ...)';
COMMENT ON COLUMN $schema.declarants.siret IS 'N° SIRET déclarant';
COMMENT ON COLUMN $schema.declarants.created_at IS 'Horodatage création';
COMMENT ON COLUMN $schema.declarants.updated_at IS 'Horodatage modification';

CREATE TABLE $schema.en_cours (
    id serial PRIMARY KEY,
    etat character(2),
    type_demande character varying(8),
    no_teleservice character varying(32),
    tvx_commune text,
    tvx_code_insee $schema.d_a_code_insee,
    tvx_adresse text,
    tvx_description text,
    dec_denomination text,
    dec_type_entite text,
    dec_siret character varying(32),
    dec_adresse2 text,
    dec_no_voie text,
    dec_lieudit_bp text,
    dec_code_postal $schema.d_a_code_postal,
    dec_commune text,
    dec_pays text,
    dec_contact text,
    dec_email $schema.d_a_email,
    dec_tel text,
    dec_fax text,
    dec_affaire text,
    declaration_at timestamp with time zone,
    reception_at timestamp with time zone,
    transmission_at timestamp with time zone,
    retour_at timestamp with time zone,
    reponse_at timestamp with time zone,
    exp_raison_sociale text,
    exp_contact text,
    exp_signataire text,
    t_r_actor_id integer,
    filename text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone,
    repertoire_id integer,
    login_name text,
    CONSTRAINT no_teleservice_not_null CHECK ((no_teleservice IS NOT NULL)),
    CONSTRAINT type_demande_not_null CHECK ((type_demande IS NOT NULL))
);
SELECT AddGeometryColumn('$schema'::varchar, 'en_cours'::varchar, 'geom'::varchar, $srid, 'MultiPolygon'::varchar, 2);

COMMENT ON TABLE $schema.en_cours IS 'Table des DT/DICT/DC/ATU';

COMMENT ON COLUMN $schema.en_cours.etat IS 'Etat de la réponse (NULL: à traiter,''ca'': transmise au chargé d''affaire,''an'': analysée par le C.A,''re'': réponse envoyée';
COMMENT ON COLUMN $schema.en_cours.type_demande IS 'Type de la demande (DT,DICT,DC)';
COMMENT ON COLUMN $schema.en_cours.no_teleservice IS 'Numéro consultation du téléservice';
COMMENT ON COLUMN $schema.en_cours.tvx_commune IS 'Commune travaux';
COMMENT ON COLUMN $schema.en_cours.tvx_code_insee IS 'Code Insee commune travaux';
COMMENT ON COLUMN $schema.en_cours.tvx_adresse IS 'Adresse travaux';
COMMENT ON COLUMN $schema.en_cours.reception_at IS 'Date de réception chez l''exploitant';
COMMENT ON COLUMN $schema.en_cours.transmission_at IS 'Date de transmission au technicien';
COMMENT ON COLUMN $schema.en_cours.retour_at IS 'Date de retour du technicien';
COMMENT ON COLUMN $schema.en_cours.reponse_at IS 'Date de réponse au déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_denomination IS 'Raison sociale déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_type_entite IS 'Type entité déclarant (PERSONNE MORALE, ...)';
COMMENT ON COLUMN $schema.en_cours.dec_siret IS 'N° SIRET déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_adresse2 IS 'Complément adresse déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_no_voie IS 'N° voie déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_lieudit_bp IS 'Lieu-dit, boite postale déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_code_postal IS 'Code postal déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_commune IS 'Commune déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_pays IS 'Pays déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_contact IS 'Contact déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_email IS 'Email contact déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_tel IS 'N° téléphone contact déclarant';
COMMENT ON COLUMN $schema.en_cours.dec_affaire IS 'N° affaire déclarant';
COMMENT ON COLUMN $schema.en_cours.exp_raison_sociale IS 'Raison sociale exploitant';
COMMENT ON COLUMN $schema.en_cours.exp_contact IS 'Contact exploitant';
COMMENT ON COLUMN $schema.en_cours.exp_signataire IS 'Nom du signataire exploitant';
COMMENT ON COLUMN $schema.en_cours.t_r_actor_id IS 'Référence à l''agent SMDEV auteur de la réponse';
COMMENT ON COLUMN $schema.en_cours.geom IS 'Géométrie de l''emprise';
COMMENT ON COLUMN $schema.en_cours.filename IS 'Nom du fichier xml';
COMMENT ON COLUMN $schema.en_cours.created_at IS 'Horodatage création réponse';
COMMENT ON COLUMN $schema.en_cours.updated_at IS 'Horodatage modification réponse';
COMMENT ON COLUMN $schema.en_cours.repertoire_id IS 'Id du repertoire dans la table repertoires';
COMMENT ON COLUMN $schema.en_cours.declaration_at IS 'Date de création de la déclaration (dans le xml)';
COMMENT ON COLUMN $schema.en_cours.dec_fax IS 'N° fax contact déclarant';
COMMENT ON COLUMN $schema.en_cours.tvx_description IS 'Description des travaux';
COMMENT ON COLUMN $schema.en_cours.login_name IS 'Login de la personne ayant traité la DT/DCI/DC/ATU';

COMMENT ON CONSTRAINT no_teleservice_not_null ON $schema.en_cours IS 'N° de téléservice not null';
COMMENT ON CONSTRAINT type_demande_not_null ON $schema.en_cours IS 'Type demande (F=DT,DICT,ATU) not null';

CREATE TABLE $schema.exp_contacts (
    id serial PRIMARY KEY,
    exploitant_id integer,
    t_r_actor_id integer,
    service text,
    tel $schema.d_a_telephone,
    mob $schema.d_a_telephone,
    fax $schema.d_a_telephone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone
);


COMMENT ON TABLE $schema.exp_contacts IS 'Contacts des exploitants';

COMMENT ON COLUMN $schema.exp_contacts.exploitant_id IS 'Référence à l''exploitant';
COMMENT ON COLUMN $schema.exp_contacts.t_r_actor_id IS 'référence à l''agent dans t_r_actors';
COMMENT ON COLUMN $schema.exp_contacts.service IS 'Nom du service exploitant';
COMMENT ON COLUMN $schema.exp_contacts.tel IS 'Téléphone du contact exploitant';
COMMENT ON COLUMN $schema.exp_contacts.mob IS 'Mobile du contact exploitant';
COMMENT ON COLUMN $schema.exp_contacts.fax IS 'Fax du contact exploitant';
COMMENT ON COLUMN $schema.exp_contacts.created_at IS 'Horodatage création';
COMMENT ON COLUMN $schema.exp_contacts.updated_at IS 'Horodatage modification';

CREATE TABLE $schema.exploitants (
    id serial PRIMARY KEY,
    raison_sociale text,
    contact text,
    no_voie text,
    adresse2 text,
    lieudit_bp text,
    code_postal $schema.d_a_code_postal,
    commune text,
    telephone $schema.d_a_telephone,
    fax $schema.d_a_telephone,
    representant text,
    signataire text,
    tel_modif $schema.d_a_telephone,
    tel_dommage $schema.d_a_telephone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone
);

COMMENT ON TABLE $schema.exploitants IS 'Table des exploitants';

COMMENT ON COLUMN $schema.exploitants.raison_sociale IS 'Raison sociale exploitant';
COMMENT ON COLUMN $schema.exploitants.no_voie IS 'N° voie exploitant';
COMMENT ON COLUMN $schema.exploitants.adresse2 IS 'Complément d''adresse exploitant';
COMMENT ON COLUMN $schema.exploitants.lieudit_bp IS 'Lieu-dit, boite postale exploitant';
COMMENT ON COLUMN $schema.exploitants.code_postal IS 'Code postal exploitant';
COMMENT ON COLUMN $schema.exploitants.commune IS 'Commune exploitant';
COMMENT ON COLUMN $schema.exploitants.telephone IS 'Téléphone exploitant';
COMMENT ON COLUMN $schema.exploitants.fax IS 'Fax exploitant';
COMMENT ON COLUMN $schema.exploitants.representant IS 'Représentant exploitant';
COMMENT ON COLUMN $schema.exploitants.signataire IS 'Signataire exploitant';
COMMENT ON COLUMN $schema.exploitants.tel_modif IS 'Téléphone si modification';
COMMENT ON COLUMN $schema.exploitants.tel_dommage IS 'Téléphone si dommage';
COMMENT ON COLUMN $schema.exploitants.created_at IS 'Horodatage création';
COMMENT ON COLUMN $schema.exploitants.updated_at IS 'Horodatage modification';

CREATE TABLE $schema.join_declarants_contacts (
    declarant_id integer NOT NULL,
    dec_contact_id integer NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone
);

COMMENT ON TABLE $schema.join_declarants_contacts IS 'Association des contacts et déclarants';

COMMENT ON COLUMN $schema.join_declarants_contacts.declarant_id IS 'Référence au déclarant';
COMMENT ON COLUMN $schema.join_declarants_contacts.dec_contact_id IS 'Référence au contact';
COMMENT ON COLUMN $schema.join_declarants_contacts.created_at IS 'Horodatage création';
COMMENT ON COLUMN $schema.join_declarants_contacts.updated_at IS 'Horodatage modification';

CREATE TABLE $schema.reponses (
    en_cour_id integer PRIMARY KEY
);
SELECT AddGeometryColumn('$schema'::varchar, 'reponses'::varchar, 'geom'::varchar, $srid, 'MultiPolygon'::varchar, 2);


COMMENT ON TABLE $schema.reponses IS 'Emprises des tableaux d''assemblage des réponses aux DT/DICT/DC/ATU';
COMMENT ON COLUMN $schema.reponses.en_cour_id IS 'Id de l''enregistrement DT/DICT dans la table en_cours';
COMMENT ON COLUMN $schema.reponses.geom IS 'Contour du tableau d''assemblage des plans de la réponse';


CREATE TABLE $schema.t_r_actors (
    id serial PRIMARY KEY,
    code character(16) NOT NULL,
    libelle character varying(64) NOT NULL,
    base boolean DEFAULT false NOT NULL,
    utile boolean DEFAULT true NOT NULL,
    ordre smallint
);


ALTER TABLE ONLY $schema.en_cours
    ADD CONSTRAINT no_teleservice_unq UNIQUE (no_teleservice);

COMMENT ON CONSTRAINT no_teleservice_unq ON $schema.en_cours IS 'Unicité du numéro de téléservice';


CREATE INDEX dec_contacts_email_idx ON $schema.dec_contacts USING btree (email) WITH (fillfactor='70');


CREATE INDEX dec_contacts_nom_idx ON $schema.dec_contacts USING btree (nom) WITH (fillfactor='70');


CREATE INDEX declarants_adresse2_idx ON $schema.declarants USING btree (adresse2) WITH (fillfactor='70');


CREATE INDEX declarants_code_postal_idx ON $schema.declarants USING btree (code_postal) WITH (fillfactor='70');


CREATE INDEX declarants_commune_idx ON $schema.declarants USING btree (commune) WITH (fillfactor='70');


CREATE INDEX declarants_denomination_idx ON $schema.declarants USING btree (denomination) WITH (fillfactor='70');


CREATE INDEX declarants_lieudit_bp_idx ON $schema.declarants USING btree (lieudit_bp) WITH (fillfactor='70');


CREATE INDEX declarants_no_voie_idx ON $schema.declarants USING btree (no_voie) WITH (fillfactor='70');


CREATE INDEX en_cours_geom_idx ON $schema.en_cours USING gist (geom);


CREATE INDEX join_declarants_contacts_cont_idx ON $schema.join_declarants_contacts USING btree (dec_contact_id) WITH (fillfactor='70');


CREATE INDEX join_declarants_contacts_decl_idx ON $schema.join_declarants_contacts USING btree (declarant_id) WITH (fillfactor='70');


CREATE FUNCTION $schema.f_repertoire_id(character varying) RETURNS integer
    LANGUAGE sql STABLE
    AS $_$
    select id from $schema.repertoires where nom ilike $1 or chemin ilike $1$semicolon
$_$;

CREATE FUNCTION $schema.f_refidbyall(v_tname character varying, v_all character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $$

DECLARE

	v_newvalue integer :=  -1$semicolon

	v_sqls varchar$semicolon

BEGIN

	IF v_all IS NULL THEN

		RETURN NULL::integer$semicolon

	END IF$semicolon

	v_sqls := 'SELECT id FROM ' || v_tname || ' WHERE utile = ''t'' AND (lower(''' || v_all || ''') = lower(code) OR lower(''' || v_all || ''') = lower(libelle))'$semicolon

	EXECUTE v_sqls INTO v_newvalue$semicolon

	RETURN v_newvalue$semicolon

END$semicolon

$$;

CREATE FUNCTION $schema.f_trg_etat_maj() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
IF NEW.reponse_at IS NOT NULL THEN
	NEW.etat := 're'$semicolon
ELSIF NEW.retour_at IS NOT NULL THEN
	NEW.etat := 'an'$semicolon
ELSIF NEW.transmission_at IS NOT NULL THEN
	NEW.etat := 'ca'$semicolon
END IF$semicolon
RETURN NEW$semicolon

END$semicolon
$$;



CREATE FUNCTION $schema.f_trg_horodater_maj() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
                NEW.updated_at := current_timestamp$semicolon
    RETURN NEW$semicolon
END$semicolon
$$;


CREATE TRIGGER trg_horodater_maj_dec_contacts BEFORE UPDATE ON $schema.dec_contacts FOR EACH ROW WHEN ((old.* IS DISTINCT FROM new.*)) EXECUTE FUNCTION $schema.f_trg_horodater_maj();


CREATE TRIGGER trg_horodater_maj_declarants BEFORE UPDATE ON $schema.declarants FOR EACH ROW WHEN ((old.* IS DISTINCT FROM new.*)) EXECUTE FUNCTION $schema.f_trg_horodater_maj();


CREATE TRIGGER trg_horodater_maj_join_declarants_contacts BEFORE UPDATE ON $schema.join_declarants_contacts FOR EACH ROW WHEN ((old.* IS DISTINCT FROM new.*)) EXECUTE FUNCTION $schema.f_trg_horodater_maj();


CREATE TRIGGER trg_maj_etat_en_cours_i BEFORE INSERT ON $schema.en_cours FOR EACH ROW EXECUTE FUNCTION $schema.f_trg_etat_maj();


CREATE TRIGGER trg_maj_etat_en_cours_u BEFORE UPDATE ON $schema.en_cours FOR EACH ROW WHEN (((old.transmission_at IS DISTINCT FROM new.transmission_at) OR (old.retour_at IS DISTINCT FROM new.retour_at) OR (old.reponse_at IS DISTINCT FROM new.reponse_at))) EXECUTE FUNCTION $schema.f_trg_etat_maj();


ALTER TABLE ONLY $schema.exp_contacts
    ADD CONSTRAINT exp_contacts_t_r_actor_fk FOREIGN KEY (t_r_actor_id) REFERENCES $schema.t_r_actors(id) MATCH FULL;


ALTER TABLE ONLY $schema.join_declarants_contacts
    ADD CONSTRAINT join_declarants_contact_cont_fk FOREIGN KEY (dec_contact_id) REFERENCES $schema.declarants(id) MATCH FULL;


ALTER TABLE ONLY $schema.join_declarants_contacts
    ADD CONSTRAINT join_declarants_contact_decl_fk FOREIGN KEY (declarant_id) REFERENCES $schema.declarants(id) MATCH FULL;


ALTER TABLE ONLY $schema.reponses
    ADD CONSTRAINT reponses_en_cour_id_fk FOREIGN KEY (en_cour_id) REFERENCES $schema.en_cours(id);

ALTER TABLE $schema.en_cours ALTER COLUMN repertoire_id SET DEFAULT $schema.f_repertoire_id('$nom_var_dossier_demandes'::character varying);

INSERT INTO $schema.repertoires (nom, chemin) VALUES ('$nom_var_dossier_demandes', '$dossier_demandes');
