--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v4.1.0 depuis la version v4.0.0
--
-- @package opencimetiere
-- @version SVN : $Id$
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v4.1.0-b1 depuis la version v4.0.0
--------------------------------------------------------------------------------

--
CREATE TABLE contrat (
    contrat integer NOT NULL,
    emplacement integer NOT NULL,
    datedemande date NOT NULL,
    datevente date NOT NULL,
    origine character varying(50) NOT NULL,
    dateterme date,
    terme character varying(15) NOT NULL,
    duree real NOT NULL,
    montant numeric(6,2),
    monnaie character varying(30),
    valide boolean,
    observation text
);
ALTER TABLE ONLY contrat
    ADD CONSTRAINT contrat_pkey PRIMARY KEY (contrat);
ALTER TABLE ONLY contrat
    ADD CONSTRAINT contrat_emplacement_fkey FOREIGN KEY (emplacement) REFERENCES emplacement(emplacement);
ALTER TABLE contrat
    ADD CONSTRAINT check_contrat_terme_duree
    CHECK ((terme = 'temporaire' AND duree > 0) OR (terme = 'perpetuite' AND duree = 0));
CREATE SEQUENCE contrat_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE contrat_seq OWNED BY contrat.contrat;
ALTER TABLE contrat 
    ADD CONSTRAINT check_contrat_origine
    CHECK (origine = 'achat' OR origine = 'renouvellement' OR origine = 'transformation');
ALTER TABLE courrier ADD COLUMN contrat integer;
ALTER TABLE ONLY courrier
    ADD CONSTRAINT courrier_contrat_fkey FOREIGN KEY (contrat) REFERENCES contrat(contrat);

--
ALTER TABLE emplacement DROP COLUMN renouvellementconcession;

--
CREATE TABLE tarif (
    tarif integer NOT NULL,
    annee integer NOT NULL,
    origine character varying(50) NOT NULL,
    terme character varying(15) NOT NULL,
    duree real,
    nature character varying(15),
    sepulture_type integer,
    montant numeric(6,2) NOT NULL,
    monnaie character varying(30) NOT NULL
);
ALTER TABLE ONLY tarif
    ADD CONSTRAINT tarif_pkey PRIMARY KEY (tarif);
ALTER TABLE ONLY tarif
    ADD CONSTRAINT tarif_sepulture_type_fkey FOREIGN KEY (sepulture_type) REFERENCES sepulture_type(sepulture_type);
ALTER TABLE tarif 
    ADD CONSTRAINT check_tarif_origine
    CHECK (origine = 'achat' OR origine = 'renouvellement');
ALTER TABLE tarif
    ADD CONSTRAINT tarif_unique_key UNIQUE (annee, origine, terme, duree, nature, sepulture_type);
CREATE UNIQUE INDEX tarif_unique_index_key 
    ON tarif (annee, origine, terme, duree, nature, coalesce(sepulture_type,-1));
ALTER TABLE tarif 
    ADD CONSTRAINT check_tarif_nature
    CHECK (nature = 'concession' OR nature = 'colombarium' OR nature = 'enfeu' OR nature = 'terraincommunal');
ALTER TABLE tarif 
    ADD CONSTRAINT check_tarif_terme_duree
    CHECK ((terme = 'temporaire' AND duree > 0) OR (terme = 'perpetuite' AND duree = 0));
CREATE SEQUENCE tarif_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE tarif_seq OWNED BY tarif.tarif;

--
ALTER TABLE autorisation ALTER COLUMN nature SET NOT NULL;
ALTER TABLE autorisation
    ADD CONSTRAINT check_autorisation_nature
    CHECK (nature = 'concessionnaire' OR nature = 'ayantdroit' OR nature = 'autre');
ALTER TABLE autorisation RENAME COLUMN telephone TO telephone1;
ALTER TABLE autorisation_archive RENAME COLUMN telephone TO telephone1;
ALTER TABLE autorisation ADD COLUMN telephone2 character varying(15);
ALTER TABLE autorisation_archive ADD COLUMN telephone2 character varying(15);
ALTER TABLE autorisation ADD COLUMN courriel character varying(100);
ALTER TABLE autorisation_archive ADD COLUMN courriel character varying(100);

--
ALTER TABLE defunt ADD COLUMN parente character varying(100);
ALTER TABLE defunt_archive ADD COLUMN parente character varying(100);
ALTER TABLE defunt ADD COLUMN lieunaissance character varying(40);
ALTER TABLE defunt_archive ADD COLUMN lieunaissance character varying(40);

--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v4.1.0-b2 depuis la version v4.1.0-b1
--------------------------------------------------------------------------------

--
ALTER TABLE contrat ALTER COLUMN montant TYPE numeric(11,2);
ALTER TABLE tarif ALTER COLUMN montant TYPE numeric(11,2);
--
CREATE TABLE contrat_archive (
    contrat integer NOT NULL,
    emplacement integer NOT NULL,
    datedemande date NOT NULL,
    datevente date NOT NULL,
    origine character varying(50) NOT NULL,
    dateterme date,
    terme character varying(15) NOT NULL,
    duree real NOT NULL,
    montant numeric(11,2),
    monnaie character varying(30),
    valide boolean,
    observation text
);
ALTER TABLE ONLY contrat_archive
    ADD CONSTRAINT contrat_archive_pkey PRIMARY KEY (contrat);
CREATE SEQUENCE contrat_archive_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE contrat_archive_seq OWNED BY contrat_archive.contrat;

--
ALTER TABLE courrier_archive ADD COLUMN contrat integer;

--
DROP SEQUENCE IF EXISTS numdossier_seq;
ALTER TABLE ONLY operation
    ADD CONSTRAINT operation_numdossier_unique_key UNIQUE (numdossier);

--
ALTER TABLE operation_defunt ADD COLUMN defunt_parente character varying(100);
ALTER TABLE operation_defunt ADD COLUMN defunt_lieunaissance character varying(40);
