/**
 * @author sbarthon
 * @date 16/09/21
 * @version 1.0.0
 * @project SIEau
 * @table INST_STEP_BASSINS et INST_STEP_BASSINS_ETABLISSEMENTS
 */

# --- !Ups

CREATE TABLE INST_STEP_BASSINS (
codeInstallation bigint,
idBassin bigint,
identifiantBassin character varying(25),
nomBassin text,
dateRaccordement timestamp with time zone
);

CREATE UNIQUE INDEX pk_inst_step_bassins ON INST_STEP_BASSINS (codeInstallation, idBassin);

CREATE TABLE INST_STEP_BASSINS_ETABLISSEMENTS (
codeStep bigint,
idBassin bigint,
idRaccordement bigint,
codeInstallation bigint,
dateDebut timestamp with time zone,
dateFin timestamp with time zone
);

CREATE INDEX idx_inst_step_bassins_etab ON INST_STEP_BASSINS_ETABLISSEMENTS (codeStep, idBassin, codeInstallation);

# --- !Downs

drop TABLE INST_STEP_BASSINS_ETABLISSEMENTS;
drop TABLE INST_STEP_BASSINS;