���-- Create one view per species

-- DROP VIEW TotalSampleEngrEnc 

CREATE OR REPLACE VIEW TotalSampleEngrEnc AS

SELECT 
  echobase_operation_spatial.voyageid, 
  operation.id AS operation_id, 
  echobase_operation_spatial.coordinate, 
  sampletype.name AS sampletype_name, 
  sample.sampleweight, 
  sizecategory.name AS sizecategory_name, 
  species.baracoudacode, 
  sample.topiaid
FROM
  public.echobase_operation_spatial, 
  public.operation, 
  public.sample, 
  public.speciescategory, 
  public.sizecategory, 
  public.species, 
  public.sampletype
WHERE 
  echobase_operation_spatial.operationid = operation.topiaid AND
  operation.topiaid = sample.operation AND
  sample.speciescategory = speciescategory.topiaid AND
  sample.sampletype = sampletype.topiaid AND
  speciescategory.sizecategory = sizecategory.topiaid AND
  speciescategory.species = species.topiaid AND
  sampletype.name = 'Total' AND
  species.baracoudacode='ENGR-ENC';

-- DROP VIEW TotalSampleSardPil

CREATE OR REPLACE VIEW TotalSampleSardPil AS

SELECT 
  echobase_operation_spatial.voyageid, 
  operation.id AS operation_id, 
  echobase_operation_spatial.coordinate, 
  sampletype.name AS sampletype_name, 
  sample.sampleweight, 
  sizecategory.name AS sizecategory_name, 
  species.baracoudacode, 
  sample.topiaid
FROM 
  public.echobase_operation_spatial, 
  public.operation, 
  public.sample, 
  public.speciescategory, 
  public.sizecategory, 
  public.species, 
  public.sampletype
WHERE 
  echobase_operation_spatial.operationid = operation.topiaid AND
  operation.topiaid = sample.operation AND
  sample.speciescategory = speciescategory.topiaid AND
  sample.sampletype = sampletype.topiaid AND
  speciescategory.sizecategory = sizecategory.topiaid AND
  speciescategory.species = species.topiaid AND
  sampletype.name = 'Total' AND
  species.baracoudacode='SARD-PIL';

CREATE OR REPLACE VIEW TotalSampleTracTru AS

SELECT 
  echobase_operation_spatial.voyageid, 
  operation.id AS operation_id, 
  echobase_operation_spatial.coordinate, 
  sampletype.name AS sampletype_name, 
  sample.sampleweight, 
  sizecategory.name AS sizecategory_name, 
  species.baracoudacode, 
  sample.topiaid
FROM 
  public.echobase_operation_spatial, 
  public.operation, 
  public.sample, 
  public.speciescategory, 
  public.sizecategory, 
  public.species, 
  public.sampletype
WHERE 
  echobase_operation_spatial.operationid = operation.topiaid AND
  operation.topiaid = sample.operation AND
  sample.speciescategory = speciescategory.topiaid AND
  sample.sampletype = sampletype.topiaid AND
  speciescategory.sizecategory = sizecategory.topiaid AND
  speciescategory.species = species.topiaid AND
  sampletype.name = 'Total' AND
  species.baracoudacode='TRAC-TRU';

CREATE OR REPLACE VIEW TotalSampleSpraSpr AS

SELECT 
  echobase_operation_spatial.voyageid, 
  operation.id AS operation_id, 
  echobase_operation_spatial.coordinate, 
  sampletype.name AS sampletype_name, 
  sample.sampleweight, 
  sizecategory.name AS sizecategory_name, 
  species.baracoudacode, 
  sample.topiaid
FROM 
  public.echobase_operation_spatial, 
  public.operation, 
  public.sample, 
  public.speciescategory, 
  public.sizecategory, 
  public.species, 
  public.sampletype
WHERE 
  echobase_operation_spatial.operationid = operation.topiaid AND
  operation.topiaid = sample.operation AND
  sample.speciescategory = speciescategory.topiaid AND
  sample.sampletype = sampletype.topiaid AND
  speciescategory.sizecategory = sizecategory.topiaid AND
  speciescategory.species = species.topiaid AND
  sampletype.name = 'Total' AND
  species.baracoudacode='SPRA-SPR';

CREATE OR REPLACE VIEW TotalSampleScomSco AS

SELECT 
  echobase_operation_spatial.voyageid, 
  operation.id AS operation_id, 
  echobase_operation_spatial.coordinate, 
  sampletype.name AS sampletype_name, 
  sample.sampleweight, 
  sizecategory.name AS sizecategory_name, 
  species.baracoudacode, 
  sample.topiaid
FROM 
  public.echobase_operation_spatial, 
  public.operation, 
  public.sample, 
  public.speciescategory, 
  public.sizecategory, 
  public.species, 
  public.sampletype
WHERE 
  echobase_operation_spatial.operationid = operation.topiaid AND
  operation.topiaid = sample.operation AND
  sample.speciescategory = speciescategory.topiaid AND
  sample.sampletype = sampletype.topiaid AND
  speciescategory.sizecategory = sizecategory.topiaid AND
  speciescategory.species = species.topiaid AND
  sampletype.name = 'Total' AND
  species.baracoudacode='SCOM-SCO';

CREATE OR REPLACE VIEW TotalSampleMicrPou AS

SELECT 
  echobase_operation_spatial.voyageid, 
  operation.id AS operation_id, 
  echobase_operation_spatial.coordinate, 
  sampletype.name AS sampletype_name, 
  sample.sampleweight, 
  sizecategory.name AS sizecategory_name, 
  species.baracoudacode, 
  sample.topiaid
FROM 
  public.echobase_operation_spatial, 
  public.operation, 
  public.sample, 
  public.speciescategory, 
  public.sizecategory, 
  public.species, 
  public.sampletype
WHERE 
  echobase_operation_spatial.operationid = operation.topiaid AND
  operation.topiaid = sample.operation AND
  sample.speciescategory = speciescategory.topiaid AND
  sample.sampletype = sampletype.topiaid AND
  speciescategory.sizecategory = sizecategory.topiaid AND
  speciescategory.species = species.topiaid AND
  sampletype.name = 'Total' AND
  species.baracoudacode='MICR-POU';

-- Create a view with species catches as columns 

-- DROP VIEW TotalCatchSpOpWide 

CREATE OR REPLACE VIEW TotalCatchSpOpWide AS

SELECT
 echobase_operation_spatial.voyagename,
 echobase_operation_spatial.operationname,
 echobase_operation_spatial.coordinate,
 (Select SUM(totalsampleengrenc.sampleweight) From totalsampleengrenc Where totalsampleengrenc.operation_id = echobase_operation_spatial.operationname) as TotalCatchENGRENC,
 (Select SUM(totalsamplesardpil.sampleweight) From totalsamplesardpil Where totalsamplesardpil.operation_id = echobase_operation_spatial.operationname) as TotalCatchSARDPIL,
 (Select SUM(totalsamplespraspr.sampleweight) From totalsamplespraspr Where totalsamplespraspr.operation_id = echobase_operation_spatial.operationname) as TotalCatchSPRASPR,
 (Select SUM(totalsamplemicrpou.sampleweight) From totalsamplemicrpou Where totalsamplemicrpou.operation_id = echobase_operation_spatial.operationname) as TotalCatchMICRPOU,
 (Select SUM(totalsamplescomsco.sampleweight) From totalsamplescomsco Where totalsamplescomsco.operation_id = echobase_operation_spatial.operationname) as TotalCatchSCOMSCO,
 (Select SUM(totalsampletractru.sampleweight) From totalsampletractru Where totalsampletractru.operation_id = echobase_operation_spatial.operationname) as TotalCatchTRACTRU

>From echobase_operation_spatial