-- 
--  This file is part of the SavaPage project <https://www.savapage.org>.
--  Copyright (c) 2011-2020 Datraverse B.V.
--  Author: Rijk Ravestein.
-- 
--  SPDX-FileCopyrightText: 2011-2020 Datraverse B.V. <info@datraverse.com>
--  SPDX-License-Identifier: AGPL-3.0-or-later
-- 
--  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 Affero 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 <https://www.gnu.org/licenses/>.
-- 
--  For more information, please contact Datraverse B.V. at this
--  address: info@datraverse.com
-- 

-- |--------------------------------------------------------------------------
-- | Schema Version 1.6 Upgrade Script - Apache Derby
-- | 
-- | Upgrade from Application Version 0.9.11 to 0.9.12
-- | 
-- |--------------------------------------------------------------------------

-- +==========================================================================
-- | Set MINOR version
-- |
-- | Force "division by zero" error if current version is not 1.5
-- +==========================================================================

UPDATE TBL_CONFIG SET PROPERTY_VALUE = '6' 
WHERE PROPERTY_NAME = 'system.schema-version-minor'
  AND 1 = (SELECT 1 / COUNT(*) FROM TBL_CONFIG WHERE 
    PROPERTY_NAME = 'system.schema-version'
      AND PROPERTY_VALUE = '1')
  AND 1 = (SELECT 1 / COUNT(*) FROM TBL_CONFIG WHERE 
    PROPERTY_NAME = 'system.schema-version-minor'
      AND PROPERTY_VALUE = '5');

-- +==========================================================================
-- | NEW TABLE
-- +==========================================================================

-- +--------------------------------------------------------
-- | tbl_user_group_account
-- +--------------------------------------------------------

-- (1)

create table tbl_user_group_account (
    user_group_account_id bigint not null,
    created_by varchar(50) not null,
    created_date timestamp not null,
    modified_by varchar(50),
    modified_date timestamp,
    account_id bigint not null,
    user_group_id bigint not null,
    primary key (user_group_account_id)
);

-- (2-3)

create index ix_user_group_account_1 on tbl_user_group_account (user_group_id);
create index ix_user_group_account_2 on tbl_user_group_account (account_id);

-- (4-5)

alter table tbl_user_group_account 
    add constraint FK_USER_GROUP_ACCOUNT_TO_ACCOUNT 
    foreign key (account_id) 
    references tbl_account;
    
alter table tbl_user_group_account 
    add constraint FK_USER_GROUP_ACCOUNT_TO_USER_GROUP 
    foreign key (user_group_id) 
    references tbl_user_group;

-- end-of-script
