---
-- #%L
-- EchoBase :: Domain
-- $Id:$
-- $HeadURL:$
-- %%
-- Copyright (C) 2011 - 2014 Ifremer, Codelutin
-- %%
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU Affero General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
-- 
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
-- 
-- You should have received a copy of the GNU Affero General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- #L%
---
-----------------------------------------------------------------------------------------------------------------------
---- DROP EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE;
DROP VIEW IF EXISTS cellmapview CASCADE;

DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE ;
DROP VIEW IF EXISTS meanMapcellBiomassEngrEnc CASCADE;
DROP VIEW IF EXISTS meanMapcellBiomassSardPil CASCADE;
DROP VIEW IF EXISTS meanMapcellBiomassTracTru CASCADE;
DROP VIEW IF EXISTS meanMapcellBiomassSpraSpr CASCADE;
DROP VIEW IF EXISTS meanMapcellBiomassScomSco CASCADE;
DROP VIEW IF EXISTS meanMapcellBiomassMicrPou CASCADE;
DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE;
DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE;
DROP VIEW IF EXISTS TotalSampleSardPil CASCADE;
DROP VIEW IF EXISTS TotalSampleTracTru CASCADE;
DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE;
DROP VIEW IF EXISTS TotalSampleScomSco CASCADE;
DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE;

DROP INDEX IF EXISTS cellEsduViewSpeciesBiomass_uidx CASCADE;
DROP INDEX IF EXISTS cellEsduViewSpeciesAbundance_uidx CASCADE;
DROP INDEX IF EXISTS cellEsduViewSpeciesMeanLength_uidx CASCADE;
DROP INDEX IF EXISTS cellEsduViewEchotypeNasc_uidx CASCADE;
DROP INDEX IF EXISTS cellEsduViewEchotype_idx CASCADE;
DROP INDEX IF EXISTS cellEsduViewSpecies_idx CASCADE;
DROP INDEX IF EXISTS cellEsduViewSpeciesResultGrouped_idx CASCADE;
DROP INDEX IF EXISTS cellmapview_idx CASCADE;
DROP INDEX IF EXISTS cellmapview_uidx CASCADE;
DROP INDEX IF EXISTS TotalSampleView_idx CASCADE;

DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotypeNasc CASCADE;
DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesBiomass CASCADE;
DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesAbundance CASCADE;
DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesMeanLength CASCADE;
DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotype CASCADE;
DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesResultGrouped CASCADE;
DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpecies CASCADE;
DROP MATERIALIZED VIEW IF EXISTS cellmapview CASCADE;
DROP MATERIALIZED VIEW IF EXISTS TotalSampleView CASCADE;

DROP TRIGGER IF EXISTS echobase_refresh_views_trigger ON entitymodificationlog CASCADE;
DROP FUNCTION IF EXISTS echobase_to_numeric(string VARCHAR) CASCADE;
DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE;

-----------------------------------------------------------------------------------------------------------------------
---- CREATE FUNCTION - TRIGGER ----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR)
  RETURNS REAL AS $$
BEGIN
  RETURN string::real;
  EXCEPTION WHEN invalid_text_representation
  THEN
    RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION echobase_refresh_views()
  RETURNS TRIGGER AS $$
DECLARE
  operationtype VARCHAR;
  result       RECORD;
BEGIN

  IF (TG_OP = 'DELETE')
  THEN
    result = OLD;
  ELSE
    result = NEW;
  END IF;

  operationtype = result.entitytype;

  IF (operationtype ILIKE '%Import%' OR operationtype ILIKE '%Removed%')
  THEN
    -- Can update views
    RAISE NOTICE 'reload cellEsduViewEchotype';
    REFRESH MATERIALIZED VIEW cellEsduViewEchotype;
    REINDEX INDEX cellEsduViewEchotype_idx;

    RAISE NOTICE 'reload cellEsduViewSpecies';
    REFRESH MATERIALIZED VIEW cellEsduViewSpecies;
    REINDEX INDEX cellEsduViewSpecies_idx;

    RAISE NOTICE 'reload cellEsduViewSpeciesResultGrouped';
    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped;
    REINDEX INDEX cellEsduViewSpeciesResultGrouped_idx;

    RAISE NOTICE 'reload cellEsduViewEchotypeNasc';
    REFRESH MATERIALIZED VIEW cellEsduViewEchotypeNasc;

    RAISE NOTICE 'reload cellEsduViewSpeciesBiomass';
    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesBiomass;
    REINDEX INDEX cellEsduViewSpeciesBiomass_uidx;

    RAISE NOTICE 'reload cellEsduViewSpeciesAbundance';
    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesAbundance;
    REINDEX INDEX cellEsduViewSpeciesAbundance_uidx;

    RAISE NOTICE 'reload cellEsduViewSpeciesMeanLength';
    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesMeanLength;
    REINDEX INDEX cellEsduViewSpeciesMeanLength_uidx;

    RAISE NOTICE 'reload cellmapview';
    REFRESH MATERIALIZED VIEW cellmapview;
    REINDEX INDEX cellmapview_idx;
    REINDEX INDEX cellmapview_uidx;
  END IF;

  RETURN result;
END
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER echobase_refresh_views_trigger
AFTER INSERT OR UPDATE ON entitymodificationlog
EXECUTE PROCEDURE echobase_refresh_views();

-----------------------------------------------------------------------------------------------------------------------
---- CELL RESULT VIEW -------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW cellEsduViewEchotype AS
  SELECT
    c.voyagename,
    c.coordinate,
    c.shape,
    c.cellname,
    d.name,
    echobase_to_numeric(r.resultvalue) as resultvalue,
    e.name as echotypeName,
    e.meaning as echotypeMeaning,
    r.topiaid AS resultid
  FROM
    echobase_cell_spatial c,
    result r,
    datametadata d,
    category cat,
    echotype e
  WHERE
    c.cellid = r.cell
    AND r.datametadata  = d.topiaid
    AND r.category = cat.topiaid
    AND cat.echotype IS NOT NULL
    AND cat.echotype = e.topiaid
    AND c.celltypename = 'Elementary Distance Sampling Unit';

CREATE INDEX cellEsduViewEchotype_idx ON cellEsduViewEchotype(name);

CREATE MATERIALIZED VIEW cellEsduViewEchotypeNasc AS
  SELECT
    *
  FROM
    cellEsduViewEchotype v
  WHERE
    v.name = 'NASC';

CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(resultid);

CREATE MATERIALIZED VIEW cellEsduViewSpecies AS
  SELECT
    c.voyagename,
    c.coordinate,
    c.shape,
    c.cellname,
    d.name,
    echobase_to_numeric(r.resultvalue) as resultvalue,
    s.baracoudacode,
    r.topiaid AS resultid
  FROM
    echobase_cell_spatial c,
    result r,
    datametadata d,
    category cat,
    speciescategory scat,
    species s
  WHERE
    c.cellid = r.cell
    AND r.datametadata  = d.topiaid
    AND r.category = cat.topiaid
    AND cat.speciescategory IS NOT NULL
    AND cat.speciescategory = scat.topiaid
    AND scat.species = s.topiaid
    AND c.celltypename = 'Elementary Distance Sampling Unit';

CREATE INDEX cellEsduViewSpecies_idx ON cellEsduViewSpecies(name);

CREATE MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped AS
  SELECT
    count(*) AS nbResults,
    name,
    cellname,
    baracoudacode,
    sum(resultvalue) as resultvalue,
    cellname || '-' || baracoudacode || '-' || name as id
  FROM cellEsduViewSpecies
  WHERE
    baracoudacode in ('ENGR-ENC', 'SARD-PIL', 'TRAC-TRU', 'SPRA-SPR', 'SCOM-SCO', 'MICR-POU')
    AND name in ('Biomass','Abundance','MeanLength')
  GROUP BY baracoudacode, cellname, name;

CREATE INDEX cellEsduViewSpeciesResultGrouped_idx ON cellEsduViewSpecies(baracoudacode, cellname, name);

CREATE MATERIALIZED VIEW cellEsduViewSpeciesBiomass AS
  SELECT
    distinct v2.coordinate,
    v2.voyagename,
    v.*
  FROM
    cellEsduViewSpeciesResultGrouped v
    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
  WHERE
    v.name = 'Biomass';

CREATE UNIQUE INDEX cellEsduViewSpeciesBiomass_uidx ON cellEsduViewSpeciesBiomass(id);

CREATE MATERIALIZED VIEW cellEsduViewSpeciesAbundance AS
  SELECT
    distinct v2.coordinate,
    v2.voyagename,
    v.*
  FROM
    cellEsduViewSpeciesResultGrouped v
    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
  WHERE
    v.name = 'Abundance';

CREATE UNIQUE INDEX cellEsduViewSpeciesAbundance_uidx ON cellEsduViewSpeciesAbundance(id);

CREATE MATERIALIZED VIEW cellEsduViewSpeciesMeanLength AS
  SELECT
    distinct v2.coordinate,
    v2.voyagename,
    v.*
  FROM
    cellEsduViewSpeciesResultGrouped v
    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
  WHERE
    v.name = 'MeanLength';

CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id);

-----------------------------------------------------------------------------------------------------------------------
---- CELL DATA VIEW ---------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW echobase_cell_spatial_data AS
  SELECT
    s.*,
    m.topiaId   AS metaDataId,
    m.name      AS metaDataName,
    d.datavalue AS dataValue,
    d.topiaid   AS dataId
  FROM
    echobase_cell_spatial s,
    data d,
    datametadata m
  WHERE
    s.cellid = d.cell AND
    d.datametadata = m.topiaid;

-----------------------------------------------------------------------------------------------------------------------
---- MAP CELL VIEW ----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW cellmapview AS
  SELECT
    c.voyagename,
    c.coordinate,
    c.shape,
    c.cellname,
    d.name,
    s.baracoudacode,
    echobase_to_numeric(r.resultvalue) as resultvalue,
    r.topiaid AS resultid
  FROM
    echobase_cell_spatial c,
    result r,
    datametadata d,
    category cat,
    speciescategory scat,
    species s
  WHERE
    c.cellid = r.cell
    AND r.datametadata  = d.topiaid
    AND r.category = cat.topiaid
    AND cat.speciescategory = scat.topiaid
    AND scat.species = s.topiaid
    AND c.celltypename = 'Map cell';

CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name);
CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(resultid);

CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS
  SELECT
    *
  FROM
    cellmapview v
  WHERE
    v.baracoudacode = 'ENGR-ENC'
    AND v.name = 'meanMapcellBiomass';

CREATE OR REPLACE VIEW meanMapcellBiomassSardPil AS
  SELECT
    *
  FROM
    cellmapview v
  WHERE
    v.baracoudacode = 'SARD-PIL'
    AND v.name = 'meanMapcellBiomass';

CREATE OR REPLACE VIEW meanMapcellBiomassTracTru AS
  SELECT
    *
  FROM
    cellmapview v
  WHERE
    v.baracoudacode = 'TRAC-TRU'
    AND v.name = 'meanMapcellBiomass';

CREATE OR REPLACE VIEW meanMapcellBiomassSpraSpr AS
  SELECT
    *
  FROM
    cellmapview v
  WHERE
    v.baracoudacode = 'SPRA-SPR'
    AND v.name = 'meanMapcellBiomass';

CREATE OR REPLACE VIEW meanMapcellBiomassScomSco AS
  SELECT
    *
  FROM
    cellmapview v
  WHERE
    v.baracoudacode = 'SCOM-SCO'
    AND v.name = 'meanMapcellBiomass';

CREATE OR REPLACE VIEW meanMapcellBiomassMicrPou AS
  SELECT
    *
  FROM
    cellmapview v
  WHERE
    v.baracoudacode = 'MICR-POU'
    AND v.name = 'meanMapcellBiomass';

-----------------------------------------------------------------------------------------------------------------------
---- OPERATION VIEW ---------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW TotalSampleView 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
    echobase_operation_spatial,
    operation,
    sample,
    speciescategory,
    sizecategory,
    species,
    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;

CREATE INDEX  TotalSampleView_idx ON TotalSampleView(baracoudacode, sampletype_name);

CREATE OR REPLACE VIEW TotalSampleEngrEnc AS
  SELECT
    *
  FROM
    TotalSampleView v
  WHERE
    v.sampletype_name = 'Total' AND
    v.baracoudacode = 'ENGR-ENC';

CREATE OR REPLACE VIEW TotalSampleSardPil AS
  SELECT
    *
  FROM
    TotalSampleView v
  WHERE
    v.sampletype_name = 'Total' AND
    v.baracoudacode = 'SARD-PIL';

CREATE OR REPLACE VIEW TotalSampleTracTru AS
  SELECT
    *
  FROM
    TotalSampleView v
  WHERE
    v.sampletype_name = 'Total' AND
    v.baracoudacode = 'TRAC-TRU';

CREATE OR REPLACE VIEW TotalSampleSpraSpr AS
  SELECT
    *
  FROM
    TotalSampleView v
  WHERE
    v.sampletype_name = 'Total' AND
    v.baracoudacode = 'SPRA-SPR';

CREATE OR REPLACE VIEW TotalSampleScomSco AS
  SELECT
    *
  FROM
    TotalSampleView v
  WHERE
    v.sampletype_name = 'Total' AND
    v.baracoudacode = 'SCOM-SCO';

CREATE OR REPLACE VIEW TotalSampleMicrPou AS
  SELECT
    *
  FROM
    TotalSampleView v
  WHERE
    v.sampletype_name = 'Total' AND
    v.baracoudacode = 'MICR-POU';

-- Create a view with species catches as columns

CREATE OR REPLACE VIEW TotalCatchSpOpWide AS
  SELECT
    e.voyagename,
    e.operationname,
    e.coordinate,
    (SELECT SUM(t.sampleweight) FROM totalsampleengrenc t WHERE t.operation_id = e.operationname) AS TotalCatchENGRENC,
    (SELECT SUM(t.sampleweight) FROM totalsamplesardpil t WHERE t.operation_id = e.operationname) AS TotalCatchSARDPIL,
    (SELECT SUM(t.sampleweight) FROM totalsamplespraspr t WHERE t.operation_id = e.operationname) AS TotalCatchSPRASPR,
    (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU,
    (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO,
    (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU,
    e.operationid
  FROM echobase_operation_spatial e;