/**
 * @author Titouan Pasquet
 * @date 28/01/21
 * @version 1.0.0
 * @project RSEau
 */

# --- !Ups

create table agri_parcelles
(
	identifiant int,
	codification varchar(25),
	adresse varchar(255),
	codecommune varchar(25),
	parcelle varchar(255),
	section varchar(255),
	surface double precision,
	codeproprietaire int,
	codestatut int,
	loginmaj varchar(255),
	datemaj timestamp with time zone,
	x double precision,
	y double precision,
	projection int
);
alter table agri_parcelles
	add constraint agri_parcelles_pk
		unique (identifiant);

create table agri_exploitation_parcelles
(
	codeexploitation bigint,
	codeparcelle int,
	datedebut timestamp with time zone,
	datefin timestamp with time zone,
	codeetat int
);

create index agri_exploitation_parcelles_codeexploitation_codeparcelle_index
	on agri_exploitation_parcelles (codeexploitation, codeparcelle);


create table agri_parcelles_points_prelevements
(
	codeparcelle int,
	codeinstallation int,
	affectation real,
	datedebut timestamp with time zone,
	datefin timestamp with time zone
);

create index agri_parcelles_points_prelevements_codeparcelle_codeinstallatio
	on agri_parcelles_points_prelevements (codeparcelle, codeinstallation);

# --- !Downs

drop table agri_parcelles;
drop table agri_exploitation_parcelles;
drop table agri_parcelles_points_prelevements;
