BEGIN TRANSACTION;
PRAGMA foreign_keys = ON;

----------------------- Lists -------------------------------------
CREATE TABLE "ListFunctionCode"(
	"id" integer,
	"functionCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherFunctionDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);


CREATE TABLE "ListPresentationFormCode"(
	"id" integer,
	"PresentationFormCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherFormDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);



CREATE TABLE "ListHydroUnitType"(
	"id" integer,
	"hydroUnitType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherHydroUnitType" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListStatusCode"(
	"id" integer,
	"statusCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherListStatusDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListActivityType"(
	"id" integer,
	"activityType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherActivityTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListScreenType"(
	"id" integer,
	"screenType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherScreenTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);


CREATE TABLE "ListWaterPersistentCode"(
	"id" integer,
	"waterPersistenceCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherWaterPresistentDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

DROP TABLE IF EXISTS ListUnitOfMeasurements;
CREATE TABLE "ListUnitOfMeasurements"(
	"id" integer,
	"uomCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherUomDetails" varchar(255),
	PRIMARY KEY(id)
);


CREATE TABLE "ListSpringType"(
	"id" integer,
	"springType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherSpringTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);


CREATE TABLE "ListScreenExtentCode"(
	"id" integer,
	"screenExtentCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherScreenExtentDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListResultsQualityCode"(
	"id" integer,
	"resultsQualityCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherResultsQualityDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListResultsNatureType"(
	"id" integer,
	"resultsNatureType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherResultsNatureDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListPointType"(
	"id" integer,
	"pointType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherPointTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListNilReasonCode"(
	"id" integer,
	"nilReasonCode" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherNilReasonObservations" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListMediaType"(
	"id" integer,
	"mediaType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEn" varchar(255),
	"otherMediaType" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListInterpolationType"(
	"id" integer,
	"interpolationType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherInterpolationTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListHydrogeologicalParametersCode"(
	"id" integer,
	"hydrogeologicalParametersCode" varchar(255),
	"parameterCode" varchar(255),
	"uomCode" integer,
	"name" varchar(255),
	"nameEN" varchar(255),
	"nameENWithUnit" varchar(255),
	"paramClassification1" varchar(255),
	"paramClassification2" varchar(255),
	"otherHydrogeologicalParametersDetails" varchar(255),
	"Observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListHydroGeochRockType"(
	"id" integer,
	"hydroGeochRockType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherHydroGeochRockTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListGeometrySourceType"(
	"id" integer,
	"geometrySourceType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEn" varchar(255),
	"otherGeometryTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListCampaignType"(
	"id" integer,
	"campaignType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"otherCampignTypeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

CREATE TABLE "ListAquiferType"(
	"id" integer,
	"aquiferType" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEn" varchar(255),
	"otherAquiferType" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

DROP TABLE  IF EXISTS ListHydrochemicalParametersCode;
CREATE TABLE "ListHydrochemicalParametersCode"(
	"id" integer,
	"hydrochemicalParameterCode" varchar(255),
	"parameterNumber" varchar(255),
	"uomCodeId" integer,
	"name" varchar(255),
	"nameEN" varchar(255),
	"nameENWithUnit" varchar(255),
	"paramClassification1" varchar(255),
	"paramClassification2" varchar(255),
	"otherHydrochemicalParametersDetails" varchar(255),
	"observations" varchar(255),
	"CASCode" integer,
    FOREIGN KEY(uomCodeId) REFERENCES ListUnitOfMeasurements(id) ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY(id)
);

------------------------- Spatial Tables -----------------------------------
DROP TABLE IF EXISTS Points;
CREATE TABLE "Points" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"point" TEXT, 
"name" TEXT, 
"name2" TEXT, 
"name3" TEXT, 
"name4" TEXT,  
"coox" DOUBLE, 
"cooy" DOUBLE, 
"elevation" NUMERIC, 
"geometryCooSourceType" INTEGER, 
"geometryElevationSourceType" INTEGER, 
"elevationReferenceSystemId" INTEGER, 
"cooReferenceSystemId" INTEGER,
"beginLifespanVersion" DOUBLE,
"endLifespanVersion" DOUBLE,
"sourceReferenceSystemId" INTEGER, 
"nameCitation" INTEGER, 
"name2Citation" INTEGER, 
"name3Citation" INTEGER, 
"name4Citation" INTEGER, 
"adressPoint" TEXT, 
"accessPoint" TEXT, 
"pointType" INTEGER, 
"otherPointDetails" TEXT, 
"observations" TEXT,
FOREIGN KEY(geometryCooSourceType) REFERENCES ListGeometrySourceType(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(geometryElevationSourceType) REFERENCES ListGeometrySourceType(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(elevationReferenceSystemId) REFERENCES ReferenceSystems(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(cooReferenceSystemId) REFERENCES ReferenceSystems(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(sourceReferenceSystemId) REFERENCES ReferenceSystems(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(nameCitation) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(name2Citation) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(name3Citation) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(name4Citation) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(pointType) REFERENCES ListPointType(id) ON DELETE CASCADE ON UPDATE CASCADE);

SELECT AddGeometryColumn('Points', 'Geometry',  4326, 'POINT', 2); 

DROP TABLE IF EXISTS HydrogeologicalUnitsApparence;
CREATE TABLE "HydrogeologicalUnitsApparence" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"hydroUnitApparance" TEXT,
"hydroUnitId" INTEGER,
"approximateDepth" DOUBLE,
"approximateThickness" DOUBLE,
"beginLifespanVersion" DOUBLE,
"endLifespanVersion" DOUBLE,
"sourceReferenceSystemId" INTEGER,
"referenceSystemId" INTEGER,
"geometrySourceType" INTEGER,
"observations" TEXT,
FOREIGN KEY(hydroUnitId) REFERENCES HydrogeologicalUnits(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(sourceReferenceSystemId) REFERENCES ReferenceSystems(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(referenceSystemId) REFERENCES ReferenceSystems(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(geometrySourceType) REFERENCES ListGeometrySourceType(id) ON DELETE CASCADE ON UPDATE CASCADE);

SELECT AddGeometryColumn('HydrogeologicalUnitsApparence', 'Geometry',  4326, 'POLYGON', 2);

------------------------- General Tables --------------------------------
DROP TABLE  IF EXISTS ResponsibleParties;
CREATE TABLE "ResponsibleParties"(
	"id" integer,
	"responsibleParty" varchar(255),
	"individualName" varchar(255),
	"organisationName" varchar(255),
	"positionName" varchar(255),
	"deliveryPoint" varchar(255),
	"city" varchar(255),
	"administrativeArea" varchar(255),
	"postalCode" varchar(255),
	"country" varchar(255),
	"electronicMailAddress" varchar(255),
	"phone" varchar(255),
	"onlineResource" varchar(255),
	"linkage" varchar(255),
	"protocol" varchar(255),
	"applicationProfile" varchar(255),
	"onlineResourceName" varchar(255),
	"description" varchar(255),
	"functionCode" integer,
	"hoursOfService" varchar(255),
	"contactInstructions" varchar(255),
	"otherResponsiblePartyDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(functionCode) REFERENCES ListFunctionCode(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS Citations;
CREATE TABLE "Citations"(
	"id" integer,
	"citation" varchar(50),
	"title" varchar(255),
	"alternateTitle" varchar(50),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"presentationFormCode" integer,
	"citedResponsiblePartyId" integer,
	"citationSearch" varchar(255),
	"citationDate" timestamp,
	"otherCitationDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(presentationFormCode) REFERENCES ListPresentationFormCode(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(citedResponsiblePartyId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE
	
);

DROP TABLE IF EXISTS HydrogeologicalUnits;
CREATE TABLE "HydrogeologicalUnits"(
	"id" integer,
	"hydroUnit" varchar(50),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"hydroUnitType" integer,
	"citationId" integer,
	"otherHydroUnitsDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(citationId) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydroUnitType) REFERENCES ListHydroUnitType(id) ON DELETE CASCADE ON UPDATE CASCADE
	
);

DROP TABLE IF EXISTS Wells;
CREATE TABLE "Wells"(
	"id" integer,
	"well" varchar(255),
	"pointId" integer,
	"validFrom" timestamp,
	"validTo" timestamp,
	"lenght" float,
	"curb" float,
	"externalDiameter" float,
	"innerDiameter" float,
	"constructionDate" timestamp,
	"ownerId" integer,
	"contructorId" integer,
	"statusCode" integer,
	"statusCodeDate" timestamp,
	"activityType" integer,
	"otherWellsDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(pointId) REFERENCES Points(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(ownerId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(contructorId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(statusCode) REFERENCES ListStatusCode(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(activityType) REFERENCES ListActivityType(id) ON DELETE CASCADE ON UPDATE CASCADE
	
);

DROP TABLE IF EXISTS WellsHydrogeologicalUnit;
CREATE TABLE "WellsHydrogeologicalUnit"(
	"wellId" integer,
	"topLength" float,
	"bottomLength" float,
	"hydroUnitId" integer,
	"otherWellsHyrogeologicalUnitsDetails" varchar(255),
	"observations" varchar(255),
	FOREIGN KEY(wellId) REFERENCES Wells(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydroUnitId) REFERENCES HydrogeologicalUnits(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS Screens;
CREATE TABLE "Screens"(
	"id" integer,
	"screen" varchar(50),
	"responsiblePartyId" integer,
	"screenType" integer,
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"OtherScreenDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(responsiblePartyId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(screenType) REFERENCES ListScreenType(id) ON DELETE CASCADE ON UPDATE CASCADE
	
);

DROP TABLE IF EXISTS WellScreens;
CREATE TABLE "WellScreens"(
	"wellId" integer,
	"screenId" integer,
	"topLenght" float,
	"bottomLenght" float,
	"screenExtentCode" integer,
	"instalationDate" timestamp,
	"hydroUnitsId" integer,
	"otherWellScreensDetails" varchar(255),
	"observations" varchar(255),
	FOREIGN KEY(wellId) REFERENCES Wells(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(screenId) REFERENCES Screens(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(screenExtentCode) REFERENCES ListScreenExtentCode(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydroUnitsId) REFERENCES HydrogeologicalUnits(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS TimeMetadata;
CREATE TABLE "TimeMetadata"(
	"id" integer,
	"timeMetadata" varchar(255),
	"accuracy" float,
	"spacing" float,
	"interpolationType" integer,
	"nilReasonCode" integer,
	"otherTimeMetadataDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(interpolationType) REFERENCES ListInterpolationType(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(nilReasonCode) REFERENCES ListNilReasonCode(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS Springs;
CREATE TABLE "Springs"(
	"id" integer,
	"spring" varchar(255),
	"pointId" integer,
	"springType" integer,
	"persistentCode" integer,
	"approximateQuantityOfFlow" float,
	"approximateQuantityOfFlowUomCode" integer,
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"OtherSpringDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(pointId) REFERENCES Points(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(persistentCode) REFERENCES ListWaterPersistentCode(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(approximateQuantityOfFlowUomCode) REFERENCES ListUnitOfMeasurements(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS ReferenceSystems;
CREATE TABLE "ReferenceSystems"(
	"id" integer,
	"referenceSystem" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"domainOfValidity" varchar(255),
	"OtherReferenceSystemDetails" varchar(255),
	"observation" varchar(255),
	PRIMARY KEY(id)
);

DROP TABLE IF EXISTS Projects;
CREATE TABLE "Projects"(
	"id" integer,
	"project" varchar(50),
	"name" varchar(200),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"citationId" integer,
	"principalInvestigatorId" integer,
	"parentProject" varchar(255),
	"otherProjectDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
	FOREIGN KEY(citationId) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(principalInvestigatorId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE
);


DROP TABLE IF EXISTS Processes;
CREATE TABLE "Processes"(
	"id" integer,
	"process" varchar(255),
	"name" varchar(255),
	"nameEN" varchar(255),
	"description" varchar(255),
	"descriptionEN" varchar(255),
	"responsiblePartyId" integer,
	"citationId" integer,
	"otherProcessDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(responsiblePartyId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(citationId) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS Normatives;
CREATE TABLE "Normatives"(
	"id" integer,
	"normative" varchar(255),
	"responsiblePartyId" integer,
	"name" varchar(255),
	"nameEN" varchar(255),
	"date" timestamp,
	"citationId" integer,
	"otherNormativesDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(responsiblePartyId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(citationId) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS HydrogeologicalPointsObservations;
CREATE TABLE "HydrogeologicalPointsObservations"(
	"id" integer,
	"hydroPointObservation" varchar(255),
	"pointId" integer,
	"hydrogeologicalParametersCode" integer,
	"qualifier" varchar(255),
	"processId" integer,
	"phenomenomTime" timestamp,
	"timeMetadataId" integer,
	"beginDate" timestamp,
	"endDate" timestamp,
	"citationId" integer,
	"responsiblePartyId" integer,
	"resultsQualityCode" integer,
	"resultsNatureType" integer,
	"otherObservationsDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(pointId) REFERENCES Points(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydrogeologicalParametersCode) REFERENCES ListHydrogeologicalParametersCode(id) ON DELETE CASCADE ON UPDATE CASCADE,	
	FOREIGN KEY(processId) REFERENCES Processes(id) ON DELETE CASCADE ON UPDATE CASCADE,	
	FOREIGN KEY(timeMetadataId) REFERENCES TimeMetadata(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(citationId) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(responsiblePartyId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(resultsQualityCode) REFERENCES ListResultsQualityCode(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(resultsNatureType) REFERENCES ListResultsNatureType(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS HydrogeologicalPointsMeasurements;
CREATE TABLE "HydrogeologicalPointsMeasurements"(
	"id" integer,
	"hydroPointObsId" integer,
	"resultTime" timestamp,
	"value" float,
	"qualifier" varchar(255),
	"otherHydroMeasurements" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(hydroPointObsId) REFERENCES HydrogeologicalPointsObservations(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS Campaigns;
CREATE TABLE "Campaigns"(
	"id" integer,
	"campaign" varchar(50),
	"campaignType" integer,
	"projectId" integer,
	"beginDate" timestamp,
	"endDate" timestamp,
	"clientId" integer,
	"custodianId" integer,
	"dataOwnerId" integer,
	"contractorId" integer,
	"otherCampaignDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(campaignType) REFERENCES ListCampaignType(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(projectId) REFERENCES Projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(clientId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(custodianId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(dataOwnerId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(contractorId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS AquiferSystems;
CREATE TABLE "AquiferSystems"(
	"id" integer,
	"aquiferSystem" varchar(50),
	"name" varchar(255),
	"nameEN" varchar(255),
	"isLayered" bit,
	"otherAquiferSystemDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id)
);

DROP TABLE IF EXISTS Aquitards;
CREATE TABLE "Aquitards"(
	"id" integer,
	"hydroUnitId" integer,
	"hydroGeochRockType" integer,
	"approximatepermeabilityCoefficient" float,
	"permeabilityCoefficientUomCode" integer,
	"approximatestorativityCoefficient" float,
	"aquiferSystemId" integer,
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(hydroUnitId) REFERENCES HydrogeologicalUnits(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydroGeochRockType) REFERENCES ListHydroGeochRockType(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(permeabilityCoefficientUomCode) REFERENCES ListUnitOfMeasurements(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(aquiferSystemId) REFERENCES AquiferSystems(id) ON DELETE CASCADE ON UPDATE CASCADE
);


DROP TABLE IF EXISTS Aquifers;
CREATE TABLE "Aquifers"(
	"id" integer,
	"hydroUnitId" integer,
	"aquiferType" integer,
	"mediaType" integer,
	"isExploited" bit,
	"isMainInSystem" bit,
	"permeabilityCoefficient" float,
	"permeabilityCoefficientUomCode" integer,
	"storativityCoefficient" float,
	"vulnerabilityPollution" varchar(255),
	"hydroGeochRockType" integer,
	"aquiferSystemId" integer,
	"otherAquiferDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(hydroUnitId) REFERENCES HydrogeologicalUnits(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(aquiferType) REFERENCES ListAquiferType(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(mediaType) REFERENCES ListMediaType(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(permeabilityCoefficientUomCode) REFERENCES ListUnitOfMeasurements(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydroGeochRockType) REFERENCES ListHydroGeochRockType(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(aquiferSystemId) REFERENCES AquiferSystems(id) ON DELETE CASCADE ON UPDATE CASCADE	
);

DROP TABLE IF EXISTS Aquicludes;
CREATE TABLE "Aquicludes"(
	"id" integer,
	"hydroUnitId" integer,
	"hydroGeochRockType" integer,
	"aquiferSystemId" integer,
	"otherAquicludeDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(hydroUnitId) REFERENCES HydrogeologicalUnits(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydroGeochRockType) REFERENCES ListHydroGeochRockType(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(aquiferSystemId) REFERENCES AquiferSystems(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS HydrochemicalNormativeParameters;
CREATE TABLE "HydrochemicalNormativeParameters"(
	"normativeId" integer,
	"hydrochemicalParametersCode" integer,
	"parameterNormativeName" varchar(255),
	"family" varchar(255),
	"limMinNorm" float,
	"limMedNorm" float,
	"limMaxNorm" float,
	"paramClassification1" varchar(255),
	"paramClassification2" varchar(255),
	"otherHydrochemNormativeDetails" varchar(255),
	"observations" varchar(255),
	FOREIGN KEY(normativeId) REFERENCES Normatives(id) ON DELETE CASCADE ON UPDATE CASCADE,	
	FOREIGN KEY(hydrochemicalParametersCode) REFERENCES ListHydrochemicalParametersCode(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS HydrochemicalSamples;
CREATE TABLE "HydrochemicalSamples"(
	"id" integer,
	"sample" varchar(255),
	"pointId" integer,
	"samplingTime" timestamp,
	"campaignId" integer,
	"fieldName" varchar(255),
	"currentLocation" varchar(255),
	"sampleSize" float,
	"sampleSizeUom" integer,
	"sampleLenght" float,
	"samplingMethodId" integer,
	"responsiblePartyId" integer,
	"otherChemSampleDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(pointId) REFERENCES Points(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(campaignId) REFERENCES Campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(sampleSizeUom) REFERENCES ListUnitOfMeasurements(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(samplingMethodId) REFERENCES Processes(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(responsiblePartyId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS HydrochemicalMeasurements;
CREATE TABLE "HydrochemicalMeasurements"(
	"id" integer,
	"sampleId" integer,
	"hydrochemicalParameterCode" integer,
	"resultTime" timestamp,
	"value" float,
	"compValue" varchar(255),
	"responsiblePartyId" integer,
	"processId" integer,
	"citationId" integer,
	"otherChemMeasurementsDetails" varchar(255),
	"observations" varchar(255),
	PRIMARY KEY(id),
	FOREIGN KEY(sampleId) REFERENCES HydrochemicalSamples(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(hydrochemicalParameterCode) REFERENCES ListHydrochemicalParametersCode(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(responsiblePartyId) REFERENCES ResponsibleParties(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(processId) REFERENCES Processes(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(citationId) REFERENCES Citations(id) ON DELETE CASCADE ON UPDATE CASCADE
);

------------------------------- Stored Queries Tables --------------------------------
DROP TABLE IF EXISTS AK_SavedQueries;
CREATE TABLE AK_SavedQueries (
	id	INTEGER,
	queryName	TEXT,
	startDate	TEXT,
	endDate	TEXT,
	PRIMARY KEY(id)
);

DROP TABLE IF EXISTS AK_SavedQueryPoints;
CREATE TABLE AK_SavedQueryPoints (
	id	INTEGER,
	savedQueryId	INTEGER,
	pointId	INTEGER,	
	PRIMARY KEY(id)
	FOREIGN KEY(savedQueryId) REFERENCES AK_SavedQueries(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(pointId) REFERENCES Points(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS AK_SavedQuerySamples;
CREATE TABLE AK_SavedQuerySamples (
	id	INTEGER,
	savedQueryPointId	INTEGER,
	sampleID	INTEGER,
	Active	INTEGER,
    FOREIGN KEY(savedQueryPointId) REFERENCES AK_SavedQueryPoints(id) ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY(id)
);

DROP TABLE IF EXISTS AK_SavedQueriesHydro;
CREATE TABLE AK_SavedQueriesHydro (
	id	INTEGER,
	queryName	TEXT,
	startDate	TEXT,
	endDate	TEXT,
	PRIMARY KEY(id)
);

DROP TABLE IF EXISTS AK_SavedQueryPointsHydro;
CREATE TABLE AK_SavedQueryPointsHydro (
	id	INTEGER,
	savedQueryId	INTEGER,
	pointId	INTEGER,	
	PRIMARY KEY(id)
	FOREIGN KEY(savedQueryId) REFERENCES AK_SavedQueriesHydro(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(pointId) REFERENCES Points(id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS AK_SavedQuerySamplesHydro;
CREATE TABLE AK_SavedQuerySamplesHydro (
	id	INTEGER,
	savedQueryPointId	INTEGER,
	sampleID	INTEGER,
	Active	INTEGER,
    FOREIGN KEY(savedQueryPointId) REFERENCES AK_SavedQueryPointsHydro(id) ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY(id)
);

COMMIT;