--
--  vim:et ts=4:
-- 
--  Author:         Frank Koormann <frank.koormann@intevation.de>
--                  Intevation GmbH, Germany
-- 
-- 
--  Description: 
--  Revert GREAT-ER Sediment Extension related inserts and updates of
--  para_tree_def_tab and related tables 
--      
--      Fixes provided:
--          - K_OW.e_range: [0;oo[ -> ]0;oo[  
--          - VP.e_range: [0;oo[ -> ]0;oo[  
--
--      Inserts and related updates: 
--          - Environmental parameters:
--              * Card "River" 
--              * Card "Sediment"        
--         
--          - Model parameters:
--              * Card "River"
--              * Card "Sediment"
--
--         See project description for details.
-- 

-- Don't need this for Oracle ...
-- BEGIN;

SPOOL downdate-db.log
-----------------------------------------------------------------------------
--
-- Fixes
--
-----------------------------------------------------------------------------

-- THE FIXES WILL BE KEPT. THESE ARE REASONABLE ANYWAY!

--UPDATE para_tree_def_tab SET
--        E_RANGE = ']0;oo[' 
--        WHERE field_id='K_OW'
--                AND block_id='SUBST'
--                AND group_id='PHYSICOCHEMICAL';
--
--UPDATE para_tree_def_tab
--        SET e_range = ']0;oo['
--        WHERE field_id='VP'
--                AND block_id='SUBST'
--                AND group_id='PHYSICOCHEMICAL';

-----------------------------------------------------------------------------
--  
--  Environmental Parameters, Card River: Rebuild
-- 
-----------------------------------------------------------------------------

-- Revert Updates

UPDATE para_tree_def_tab SET
    field_sort = '30',
    label      = 'corr_SS',
    remark     = 'correlation of SS to flow'
    WHERE field_id = 'SS_CORR'
            AND block_id = 'ENV'
            AND group_id = 'RIVER';

UPDATE para_tree_def_tab SET
    field_sort = '10'
    WHERE field_id = 'TEMP_CORR'
            AND block_id = 'ENV'
            AND group_id = 'RIVER';

UPDATE para_tree_def_tab SET
    field_sort = '20'
    WHERE field_id = 'WIND_CORR'
            AND block_id = 'ENV'
            AND group_id = 'RIVER';

-- Delete new parameters
DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = 'SS_RIVER';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = 'F_OC_RIVER';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = '%117';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = 'CATCHMENT_TYPE_SSCONC_BANDS';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = 'SSCONC_BANDS_FILENAME';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = '%127';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = '%137';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = 'TERRACE_FILENAME';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = '%147';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = '%157';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = 'DIFFUSE_CORR';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'RIVER'
            AND field_id = '%167';

--
-- Related phrases (phrase_grp_id, language, sort, value_pt, remark)
--

-- P9: CATCHMENT_TYPE_SSCONC_BANDS 
DELETE FROM phrase_tab WHERE phrase_grp_id = 9;

-----------------------------------------------------------------------------
--  
--  Environmental Parameters: New card "Sediment" with RHO_SED, EPS_SED
-- 
-----------------------------------------------------------------------------

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'SED'
            AND field_id = '%108';
-- Insert a subgroup to place the two parameters vertical.

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'SED'
            AND field_id = '%118';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'SED'
            AND field_id = '%128';

-- Actually add the two new parameters

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'SED'
            AND field_id = 'RHO_SED';

DELETE FROM para_tree_def_tab WHERE block_id = 'ENV' AND group_id = 'SED'
            AND field_id = 'EPS_SED';

-----------------------------------------------------------------------------
--  
--  Model Parameters: Redesign 'River' card 
-- 
-----------------------------------------------------------------------------

-- DELETE FROM para_tree_def_tab WHERE block_id='MOD' AND group_id='SELECTION_RIVER';

-- Updates
UPDATE para_tree_def_tab SET
    field_sort = '20'
    WHERE field_id = 'MODE_RIVER' 
        AND block_id='MOD' 
        AND group_id='SELECTION_RIVER';

UPDATE para_tree_def_tab SET
    field_sort = '25',
    label      = 'Mode 3 Submodel Switches'
    WHERE field_id = '%215' 
        AND block_id='MOD' 
        AND group_id='SELECTION_RIVER';

UPDATE para_tree_def_tab SET
    field_sort = '75',
    label      = 'Mode 3 Submodel Switches'
    WHERE field_id = '%225' 
        AND block_id='MOD' 
        AND group_id='SELECTION_RIVER';

-- New parameters

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_RIVER' 
            AND field_id = 'MODE_RIVER_COMMON_DATA';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_RIVER' AND field_id = 'USE_DIFFUSE_INPUT';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_RIVER' AND field_id = 'TERRACE_FILE_RIV';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_RIVER' AND field_id = '%275';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_RIVER' AND field_id = '%285';

--
-- Related phrases (phrase_grp_id, language, sort, value_pt, remark)
--

-- P8: MODE_RIVER_COMMON_DATA
DELETE FROM phrase_tab WHERE phrase_grp_id = 8;

-----------------------------------------------------------------------------
--  
--  Model Parameters: New 'Sediment' card
-- 
-----------------------------------------------------------------------------

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_SEDIMENT' AND field_id = '%206';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_SEDIMENT' AND field_id = 'MODE_SED';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_SEDIMENT' 
            AND field_id = 'MODE_SED_COMMON_DATA';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_SEDIMENT' AND field_id = '%216';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_SEDIMENT' 
            AND field_id = 'SSCONC_BANDS_FILE';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_SEDIMENT' 
            AND field_id = 'TERRACE_FILE_SED';

DELETE FROM para_tree_def_tab WHERE block_id = 'MOD' 
            AND group_id = 'SELECTION_SEDIMENT' AND field_id = '%226';

--
-- Related phrases (phrase_grp_id, language, sort, value_pt, remark)
--

-- P7: MODE_SED
DELETE FROM phrase_tab WHERE phrase_grp_id = 7;

-- P10: MODE_SED_COMMON_DATA
DELETE FROM phrase_tab WHERE phrase_grp_id = 10;

--
-- Cleanup the bin_obj_tab (delete ssc bands)
--

DELETE FROM bin_obj_tab WHERE obj_type = 'MODEL' 
                          AND obj_sub_type = 'SSC_GROUP_A';
DELETE FROM bin_obj_tab WHERE obj_type = 'MODEL' 
                          AND obj_sub_type = 'SSC_GROUP_B';
DELETE FROM bin_obj_tab WHERE obj_type = 'MODEL' 
                          AND obj_sub_type = 'SSC_GROUP_C';

COMMIT;

SPOOL off

EXIT
