data/pgsql/v3.2.0-dev.sql

--
-- Ajout de table: avis_consultation_tmp;
--


CREATE TABLE avis_consultation_tmp (
    avis character varying(2) NOT NULL,
    libelle character varying(30) NOT NULL,
    typeavis character(1) DEFAULT ''::bpchar NOT NULL,
    sitadel character(1) DEFAULT ''::bpchar NOT NULL,
    sitadel_motif character(1) DEFAULT ''::bpchar NOT NULL
);


--
-- Data for Name: avis_consultation_tmp;
--

INSERT INTO avis_consultation_tmp VALUES ('D', 'Defavorable', 'D', '6', ' ');
INSERT INTO avis_consultation_tmp VALUES ('F', 'Favorable', 'F', '4', ' ');
INSERT INTO avis_consultation_tmp VALUES ('F1', 'Favorable avec Reserve', 'F', '4', ' ');
INSERT INTO avis_consultation_tmp VALUES ('T', 'Tacite', 'F', '2', ' ');
INSERT INTO avis_consultation_tmp VALUES ('A', 'Autre', ' ', '7', ' ');


--
-- Ajout de la table 'service_categorie' 
--
CREATE TABLE service_categorie (
  service_categorie integer,
  libelle varchar(70)  NOT NULL default ''
);

ALTER TABLE ONLY service_categorie
    ADD CONSTRAINT service_categorie_pkey PRIMARY KEY (service_categorie);

CREATE SEQUENCE service_categorie_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--
-- Modification de la table 'service' et des clés étangères
--
ALTER TABLE consultation DROP CONSTRAINT consultation_service_fkey;
ALTER TABLE service DROP CONSTRAINT service_pkey;

ALTER TABLE consultation RENAME COLUMN service TO service_old;
ALTER TABLE service RENAME COLUMN service TO service_old;

CREATE SEQUENCE service_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE service ADD COLUMN service integer NOT NULL DEFAULT nextval('service_seq'::regclass);
ALTER TABLE consultation ADD COLUMN service integer;

UPDATE consultation SET service=(select service.service from service where service_old=service.service_old);

ALTER TABLE service ADD COLUMN consultation_papier boolean;
ALTER TABLE service ADD COLUMN notification_email boolean;
ALTER TABLE service ADD COLUMN om_validite_debut date;
ALTER TABLE service ADD COLUMN om_validite_fin date;
ALTER TABLE service ADD COLUMN type_consultation varchar(70) NOT NULL DEFAULT 'avec_avis_attendu';

ALTER TABLE service RENAME COLUMN service_old TO abrege;
ALTER TABLE consultation DROP COLUMN service_old;

ALTER TABLE ONLY service
    ADD CONSTRAINT service_pkey PRIMARY KEY (service);

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_service_fkey FOREIGN KEY (service) REFERENCES service(service);

ALTER SEQUENCE service_seq OWNED BY service.service;

ALTER TABLE service ALTER COLUMN service DROP DEFAULT;

--
-- Ajout de la table 'lien_service_service_categorie'
--

CREATE TABLE lien_service_service_categorie (
  lien_service_service_categorie integer,
  service_categorie integer,
  service integer
);

ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_pkey PRIMARY KEY (lien_service_service_categorie);
ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_service_categorie_fkey FOREIGN KEY (service_categorie) REFERENCES service_categorie(service_categorie);
ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_service_fkey FOREIGN KEY (service) REFERENCES service(service);

CREATE SEQUENCE lien_service_service_categorie_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--
-- Ajout de la table 'lien_service_utilisateur'
--

CREATE TABLE lien_service_om_utilisateur (
  lien_service_om_utilisateur integer,
  om_utilisateur bigint,
  service integer
);

ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_pkey PRIMARY KEY (lien_service_om_utilisateur);
ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_om_utilisateur_fkey FOREIGN KEY (om_utilisateur) REFERENCES om_utilisateur(om_utilisateur);
ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_service_fkey FOREIGN KEY (service) REFERENCES service(service);

CREATE SEQUENCE lien_service_om_utilisateur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--
-- Ajout des tables 'avis_consultation' et 'avis_decision'
--

CREATE TABLE avis_decision (
  avis_old character varying(2) NOT NULL,
  libelle character varying(30) NOT NULL,
  typeavis character(1) DEFAULT ''::bpchar NOT NULL,
  sitadel character(1) DEFAULT ''::bpchar NOT NULL,
  sitadel_motif character(1) DEFAULT ''::bpchar NOT NULL
);

CREATE SEQUENCE avis_decision_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE avis_consultation (
  avis_old character varying(2) NOT NULL,
  libelle character varying(30) NOT NULL,
  abrege character varying(10),
  om_validite_debut date,
  om_validite_fin date
);

CREATE SEQUENCE avis_consultation_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE avis_decision ADD COLUMN avis_decision integer NOT NULL DEFAULT nextval('avis_decision_seq'::regclass);
INSERT INTO avis_decision(avis_old, libelle, typeavis, sitadel, sitadel_motif) SELECT avis, libelle, typeavis, sitadel, sitadel_motif 
  FROM avis;

ALTER TABLE avis_consultation ADD COLUMN avis_consultation integer NOT NULL DEFAULT nextval('avis_consultation_seq'::regclass);
INSERT INTO avis_consultation(avis_old, libelle) SELECT avis, libelle 
  FROM avis_consultation_tmp;


ALTER TABLE ONLY avis_decision
    ADD CONSTRAINT avis_decision_pkey PRIMARY KEY (avis_decision);
ALTER TABLE ONLY avis_consultation
    ADD CONSTRAINT avis_consultation_pkey PRIMARY KEY (avis_consultation);
ALTER SEQUENCE avis_consultation_seq OWNED BY avis_consultation.avis_consultation;
ALTER SEQUENCE avis_decision_seq OWNED BY avis_decision.avis_decision;
ALTER TABLE avis_decision ALTER COLUMN avis_decision DROP DEFAULT;
ALTER TABLE avis_consultation ALTER COLUMN avis_consultation DROP DEFAULT;

-- Changement des clés étrangères pour 'avis'
ALTER TABLE consultation DROP CONSTRAINT consultation_avis_fkey;
ALTER TABLE evenement DROP CONSTRAINT evenement_avis_fkey;
ALTER TABLE instruction DROP CONSTRAINT instruction_avis_fkey;
ALTER TABLE dossier DROP CONSTRAINT dossier_avis_fkey;

ALTER TABLE consultation ADD COLUMN avis_consultation integer;
ALTER TABLE evenement ADD COLUMN avis_decision integer;
ALTER TABLE instruction ADD COLUMN avis_decision integer;
ALTER TABLE dossier ADD COLUMN avis_decision integer;


UPDATE consultation SET avis_consultation=(select avis_consultation.avis_consultation from avis_consultation where avis=avis_consultation.avis_old);

--
-- Modification de la table 'consultation'
--
ALTER TABLE consultation ADD COLUMN date_reception date;
ALTER TABLE consultation ADD COLUMN motivation text DEFAULT '';
ALTER TABLE consultation ADD COLUMN fichier character varying(100);
ALTER TABLE consultation ADD COLUMN lu boolean;


UPDATE evenement SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old);
UPDATE instruction SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old);
UPDATE dossier SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old);

ALTER TABLE consultation DROP COLUMN avis;
ALTER TABLE evenement DROP COLUMN avis;
ALTER TABLE instruction DROP COLUMN avis;
ALTER TABLE dossier DROP COLUMN avis;

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_avis_consultation_fkey FOREIGN KEY (avis_consultation) REFERENCES avis_consultation(avis_consultation);
ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);
ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);
ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);
ALTER TABLE avis_decision DROP COLUMN avis_old;
ALTER TABLE avis_consultation DROP COLUMN avis_old;
DROP TABLE avis;
DROP TABLE avis_consultation_tmp;

--
-- Ajout des droits sur les nouvelles tables
--
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'service_categorie', '4');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_decision', '4');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_consultation', '4');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'lien_service_service_categorie', '4');

ALTER TABLE consultation ALTER service SET NOT NULL;
ALTER TABLE dossier ADD COLUMN enjeu_erp boolean;
ALTER TABLE dossier ADD COLUMN enjeu_urba boolean;

INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_mes_retours', 4);
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_tous_retours', 4);

--
-- Modification de la structure des instructeurs
--
CREATE SEQUENCE direction_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    
CREATE TABLE direction (
  direction integer NOT NULL,
  code character varying(20) NOT NULL,
  libelle character varying(100) NOT NULL,
  description text,
  chef character varying(100) NOT NULL,
  om_validite_debut date,
  om_validite_fin date,
  PRIMARY KEY (direction)
);

INSERT INTO direction VALUES (nextval('direction_seq'::regclass),'ADS', 'Direction ADS', 'Direction des autorisations des droits du sol', 'Mme Dupont', NULL, NULL);

CREATE SEQUENCE division_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    
CREATE TABLE division (
  division integer NOT NULL,
  code character varying(20) NOT NULL,
  libelle character varying(100) NOT NULL,
  description text,
  chef character varying(100) NOT NULL,
  direction integer NOT NULL,
  om_validite_debut date,
  om_validite_fin date,
  PRIMARY KEY (division),
  FOREIGN KEY ( direction ) REFERENCES direction ( direction )
);

INSERT INTO division VALUES (nextval('division_seq'::regclass),'Defaut', 'Division par defaut', '', 'Mme Dupont',1, NULL, NULL);

CREATE SEQUENCE instructeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    
CREATE TABLE instructeur (
  instructeur integer NOT NULL,
  nom character varying(100) NOT NULL,
  telephone character varying(14),
  division integer NOT NULL,
  om_utilisateur integer,
  om_validite_debut date,
  om_validite_fin date,
  PRIMARY KEY ( instructeur ),
  FOREIGN KEY ( division ) REFERENCES division ( division ),
  FOREIGN KEY ( om_utilisateur ) REFERENCES om_utilisateur ( om_utilisateur )
);

-- Insertion des utilisateurs instructeurs dans la table instructeur
INSERT INTO instructeur (instructeur,nom,telephone,division,om_utilisateur)
(SELECT om_utilisateur,nom,telephone,1,om_utilisateur FROM om_utilisateur WHERE instructeur='Oui');
-- Mise a jour de la sequence
SELECT setval('instructeur_seq',(SELECT MAX(instructeur) FROM instructeur));

-- Creation du parametre pour afficher ou non la division dans les dossiers
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'afficher_division','false',1);

-- Ajout des droits sur les tables d'organisation
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'direction', '2');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'division', '2');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'instructeur', '2');

-- Modification de la clé étrangère  dossier -> om_utilisateur par dossier -> instructeur
ALTER TABLE dossier DROP CONSTRAINT dossier_instructeur_fkey;
ALTER TABLE dossier ADD CONSTRAINT
dossier_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur);

-- Ajout des parametres des liens dans la table om_parametre
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'lien_interne_vdm', '',1);
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'lien_externe', '',1);
ALTER TABLE om_parametre ALTER COLUMN valeur TYPE character varying(150);


INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_code_barre', '2');

--
-- Ajout des tables arrondissement, quartier et lien_localisation_nature
--
CREATE TABLE arrondissement (
        arrondissement integer NOT NULL,
        libelle character varying(3) NOT NULL
);

ALTER TABLE ONLY arrondissement
    ADD CONSTRAINT arrondissement_pkey PRIMARY KEY (arrondissement);

CREATE SEQUENCE arrondissement_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE quartier (
        quartier integer NOT NULL,
        arrondissement integer NOT NULL,
        code_impots character varying(3) NOT NULL,
        libelle character varying(40) NOT NULL
);

ALTER TABLE ONLY quartier
    ADD CONSTRAINT quartier_pkey PRIMARY KEY (quartier);
ALTER TABLE ONLY quartier
    ADD CONSTRAINT quartier_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);

CREATE SEQUENCE quartier_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE lien_localisation_nature (
        lien_localisation_nature integer NOT NULL,
        nature character varying(2),
        arrondissement integer,
        quartier integer ,
        section varchar(2),
        instructeur integer NOT NULL
);

ALTER TABLE ONLY lien_localisation_nature
    ADD CONSTRAINT lien_localisation_nature_pkey PRIMARY KEY (lien_localisation_nature);
ALTER TABLE ONLY lien_localisation_nature
    ADD CONSTRAINT lien_localisation_nature_nature_fkey FOREIGN KEY (nature) REFERENCES nature(nature);
ALTER TABLE ONLY lien_localisation_nature
    ADD CONSTRAINT lien_localisation_nature_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);
ALTER TABLE ONLY lien_localisation_nature
    ADD CONSTRAINT lien_localisation_nature_quartier_fkey FOREIGN KEY (quartier) REFERENCES quartier(quartier);
ALTER TABLE ONLY lien_localisation_nature
    ADD CONSTRAINT lien_localisation_nature_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur);

CREATE SEQUENCE lien_localisation_nature_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Ajout des droits pour le retour des services
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'demande_avis_encours', '2');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_retour_service', '2');

-- Suppression des colonnes inutiles dans la table om_utilisateur
ALTER TABLE om_utilisateur DROP instructeur;

ALTER TABLE om_utilisateur DROP telephone;

-- Ajout des droits sur lien_service_om_utilisateur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'lien_service_om_utilisateur', '2');
-- Ajout des droits pour le retour des services
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'demande_avis_passee', '2');

-- Ajout des droits sur lien_localisation_nature
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'lien_localisation_nature', '2');

-- Ajout du droit pour changer l'état (lu/non lu) d'une  consultation
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_modifier_lu', '2');

-- Changement de taille du champs parcelle de la table parcelle et terrain 
ALTER TABLE parcelle ALTER COLUMN parcelle TYPE character varying(20);
ALTER TABLE terrain ALTER COLUMN parcelle TYPE character varying(20);


--
-- Messages
--

-- create sequence for the message ID generation
CREATE SEQUENCE messages_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Create table messages
CREATE TABLE messages (
    message integer PRIMARY KEY DEFAULT nextval('messages_seq'),
    dossier character varying(12),
    type character varying(60),
    emetteur character varying(40),
    date_emission DATE NOT NULL,
    lu boolean default FALSE,
    FOREIGN KEY ( dossier ) REFERENCES dossier ( dossier ) -- add this
);
ALTER SEQUENCE messages_seq OWNED BY messages.message;

INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'menu_suivi', '2');

-- Droit de l'ajout forcé d'un instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'dossier_modifier_instructeur', '2');

-- Module 1
CREATE TABLE dossier_autorisation_type (
        dossier_autorisation_type integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        confidentiel boolean default FALSE
);

ALTER TABLE ONLY dossier_autorisation_type
    ADD CONSTRAINT dossier_autorisation_type_pkey PRIMARY KEY (dossier_autorisation_type);

CREATE SEQUENCE dossier_autorisation_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE dossier_autorisation_type_detaille (
        dossier_autorisation_type_detaille integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        dossier_autorisation_type integer
);

ALTER TABLE ONLY dossier_autorisation_type_detaille
    ADD CONSTRAINT dossier_autorisation_type_detaille_pkey PRIMARY KEY (dossier_autorisation_type_detaille);
ALTER TABLE ONLY dossier_autorisation_type_detaille
    ADD CONSTRAINT dossier_autorisation_type_detaille_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);

CREATE SEQUENCE dossier_autorisation_type_detaille_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE dossier_instruction_type (
        dossier_instruction_type integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        dossier_autorisation_type_detaille integer,
        suffixe boolean default FALSE
);

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_pkey PRIMARY KEY (dossier_instruction_type);
ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);

CREATE SEQUENCE dossier_instruction_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE demande_genre (
        demande_genre integer,
        code character varying(20),
        libelle character varying(100),
        description text
);

ALTER TABLE ONLY demande_genre
    ADD CONSTRAINT demande_genre_pkey PRIMARY KEY (demande_genre);

CREATE SEQUENCE demande_genre_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE groupe (
        groupe integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        demande_genre integer
);

ALTER TABLE ONLY groupe
    ADD CONSTRAINT groupe_pkey PRIMARY KEY (groupe);
ALTER TABLE ONLY groupe
    ADD CONSTRAINT groupe_demande_genre_fkey FOREIGN KEY (demande_genre) REFERENCES demande_genre(demande_genre);

CREATE SEQUENCE groupe_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Ajout de clé étrangère à la table dossier_autorisation_type
ALTER TABLE dossier_autorisation_type ADD COLUMN groupe integer;
ALTER TABLE ONLY dossier_autorisation_type
    ADD CONSTRAINT dossier_autorisation_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe);

--Demande nature

CREATE TABLE demande_nature (
        demande_nature integer,
        code character varying(20),
        libelle character varying(100),
        description text
);

ALTER TABLE ONLY demande_nature
    ADD CONSTRAINT demande_nature_pkey PRIMARY KEY (demande_nature);

CREATE SEQUENCE demande_nature_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--Demande type

CREATE TABLE demande_type (
        demande_type integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        demande_nature integer,
        groupe integer,
        dossier_instruction_type integer,
        dossier_autorisation_type_detaille integer,
        contraintes character varying(20),
        etats_dossier_autorisation_autorises character varying(100),
        qualification boolean,
        evenement integer
);

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_pkey PRIMARY KEY (demande_type);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_demande_nature_fkey FOREIGN KEY (demande_nature) REFERENCES demande_nature(demande_nature);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_dossier_instruction_type_fkey FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);

CREATE SEQUENCE demande_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE lien_evenement_dossier_autorisation_type (
        lien_evenement_dossier_autorisation_type integer,
        evenement integer,
        dossier_autorisation_type integer
);

ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_pkey PRIMARY KEY (lien_evenement_dossier_autorisation_type);
ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);
ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);

CREATE SEQUENCE lien_evenement_dossier_autorisation_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE autorite_competente (
        autorite_competente integer,
        code character varying(20),
        libelle character varying(100),
        description text
);

ALTER TABLE ONLY autorite_competente
    ADD CONSTRAINT autorite_competente_pkey PRIMARY KEY (autorite_competente);

CREATE SEQUENCE autorite_competente_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Ajout de clé étrangère à la table dossier_autorisation_type
ALTER TABLE dossier ADD COLUMN autorite_competente integer;
ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_autorite_competente_fkey FOREIGN KEY (autorite_competente) REFERENCES autorite_competente(autorite_competente);

-- Donnees des tables 
INSERT INTO dossier_autorisation_type(dossier_autorisation_type, code, libelle) SELECT nextval('dossier_autorisation_type_seq'), nature, libelle FROM nature;
INSERT INTO dossier_autorisation_type_detaille(dossier_autorisation_type_detaille, code, libelle) SELECT nextval('dossier_autorisation_type_seq'), nature, libelle FROM nature;

INSERT INTO demande_genre VALUES (nextval('demande_genre_seq'), 'URBA', 'Pôle Urbanisme', 'Responsabilité de la DDU');
INSERT INTO demande_genre VALUES (nextval('demande_genre_seq'), 'ERP', 'Pôle ERP', 'Responsabilité de la DGUP');

INSERT INTO groupe VALUES (nextval('groupe_seq'), 'ADS', 'Autorisation ADS', '',1);
INSERT INTO groupe VALUES (nextval('groupe_seq'), 'CTX', 'Contentieux dans le domaine urbanisme', '',1);
INSERT INTO groupe VALUES (nextval('groupe_seq'), 'CU', 'Changement d''usage', '',1);
INSERT INTO groupe VALUES (nextval('groupe_seq'), 'RU', 'Renseignement d''urbanisme', '',1);
INSERT INTO groupe VALUES (nextval('groupe_seq'), 'ERP', 'ERP', '',2);

-- Table Demande

CREATE TABLE demande (
        demande integer,
        dossier_autorisation_type_detaille integer,
        demande_type integer,
        date_demande date,
        terrain_references_cadastrales character varying(100),
        terrain_adresse_voie_numero integer,
        complement character varying(30),
        terrain_adresse_lieu_dit character varying(30),
        terrain_adresse_localite character varying(30),
        terrain_adresse_code_postal character varying(5),
        terrain_adresse_bp character varying(15),
        terrain_adresse_cedex character varying(15),
        terrain_superficie double precision,
        nombre_lots integer
);

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_pkey PRIMARY KEY (demande);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_type_demande_type_fkey FOREIGN KEY (demande_type) REFERENCES demande_type(demande_type);

CREATE SEQUENCE demande_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table Demandeur

CREATE TABLE demandeur (
        demandeur integer,
        type_demandeur character varying(40),
        particulier_civilite character varying(10),
        particulier_nom character varying(40),
        particulier_prenom character varying(40),
        particulier_date_naissance date,
        particulier_commune_naissance character varying(30),
        particulier_departement_naissance character varying(80),
        personne_morale_denomination character varying(15),
        personne_morale_raison_sociale character varying(15),
        personne_morale_siret character varying(15),
        personne_morale_categorie_juridique character varying(15),
        personne_morale_civilite character varying(10),
        personne_morale_nom character varying(40),
        personne_morale_prenom character varying(40),
        numero character varying(5),
        voie character varying(40),
        complement character varying(39),
        lieu_dit character varying(39),
        localite character varying(30),
        code_postal character varying(5),
        bp character varying(5),
        cedex character varying(5),
        pays character varying(40),
        division_territoriale character varying(40),
        telephone_fixe character varying(14),
        telephone_mobile character varying(14),
        indicatif character varying(5),
        courriel character varying(40),
        notification boolean,
        frequent boolean,
        demande integer
);

ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_pkey PRIMARY KEY (demandeur);
ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_particulier_civilite_fkey FOREIGN KEY (particulier_civilite) REFERENCES civilite(civilite);
ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_personne_morale_civilite_fkey FOREIGN KEY (personne_morale_civilite) REFERENCES civilite(civilite);
ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_demande_fkey FOREIGN KEY (demande) REFERENCES demande(demande);

CREATE SEQUENCE demandeur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table lot

CREATE TABLE lot (
        lot integer,
        dossier character varying(12)
);

ALTER TABLE ONLY lot
    ADD CONSTRAINT lot_pkey PRIMARY KEY (lot);
ALTER TABLE ONLY lot
    ADD CONSTRAINT lot_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);

CREATE SEQUENCE lot_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table lien_lot_demandeur

CREATE TABLE lien_lot_demandeur (
        lien_lot_demandeur integer,
        lot integer,
        demandeur integer
);

ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_pkey PRIMARY KEY (lien_lot_demandeur);
ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot);
ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);

CREATE SEQUENCE lien_lot_demandeur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


---
--- Nouvelle gestion des tableaux de bord
---

CREATE TABLE om_dashboard (
    om_dashboard integer NOT NULL,
    om_profil integer NOT NULL,
    bloc character varying(10) NOT NULL,
    position integer,
    om_widget integer NOT NULL
);

ALTER TABLE ONLY om_dashboard
    ADD CONSTRAINT om_dashboard_pkey PRIMARY KEY (om_dashboard);
ALTER TABLE ONLY om_dashboard
    ADD CONSTRAINT om_dashboard_om_profil_fkey FOREIGN KEY (om_profil) REFERENCES om_profil(om_profil);
ALTER TABLE ONLY om_dashboard
    ADD CONSTRAINT om_dashboard_om_widget_fkey FOREIGN KEY (om_widget) REFERENCES om_widget(om_widget);

CREATE SEQUENCE om_dashboard_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

SELECT pg_catalog.setval('om_dashboard_seq', 1, false);

ALTER TABLE om_widget
    DROP CONSTRAINT om_widget_om_profil_fkey;

ALTER TABlE om_widget DROP COLUMN om_profil;

ALTER TABLE om_widget ADD COLUMN "type" character varying(40) NOT NULL DEFAULT 'web'::character varying;
ALTER TABLE om_widget ALTER COLUMN "lien" SET DEFAULT ''::character varying;
ALTER TABLE om_widget ALTER COLUMN "texte" SET DEFAULT ''::text;

