-- 
--  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.7 Upgrade Script - Apache Derby
-- | 
-- | Upgrade from Application Version 0.9.12 to 1.0.0
-- | 
-- |--------------------------------------------------------------------------

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

UPDATE TBL_CONFIG SET PROPERTY_VALUE = '7' 
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 = '6');

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

-- +--------------------------------------------------------
-- | tbl_cost_change
-- +--------------------------------------------------------

-- (1)

create table tbl_cost_change (
    cost_change_id bigint not null,
    chg_amount numeric(16,8),
    chg_by varchar(50),
    chg_cost numeric(16,8),
    chg_date timestamp not null,
    chg_reason varchar(1000),
    chg_status varchar(20) not null,
    chg_type varchar(20) not null,
    currency_code varchar(3) not null,
    req_amount numeric(16,8) not null,
    req_date timestamp not null,
    req_reason varchar(1000),
    doc_id bigint not null,
    req_user_id bigint,
    primary key (cost_change_id)
);

-- (2-9)

create index ix_cost_change_1 on tbl_cost_change (req_date);
create index ix_cost_change_2 on tbl_cost_change (chg_type);
create index ix_cost_change_3 on tbl_cost_change (chg_status);
create index ix_cost_change_4 on tbl_cost_change (chg_date);
create index ix_cost_change_5 on tbl_cost_change (chg_by);
create index ix_cost_change_6 on tbl_cost_change (doc_id);
create index ix_cost_change_7 on tbl_cost_change (req_user_id);

-- (9-10)

alter table tbl_cost_change 
   add constraint FK_COST_CHANGE_TO_DOCLOG 
   foreign key (doc_id) 
   references tbl_doc_log;

alter table tbl_cost_change 
   add constraint FK_COST_CHANGE_TO_USER 
   foreign key (req_user_id) 
   references tbl_user;


-- +==========================================================================
-- | CHANGES
-- +==========================================================================

-- +--------------------------------------------------------
-- | tbl_account_trx
-- +--------------------------------------------------------

-- (11-15)

ALTER TABLE tbl_account_trx ADD COLUMN trx_weight_unit integer;
UPDATE tbl_account_trx SET trx_weight_unit = trx_weight;
ALTER TABLE tbl_account_trx ALTER COLUMN trx_weight_unit NOT NULL;

ALTER TABLE tbl_account_trx ADD COLUMN cost_change_id bigint;

alter table tbl_account_trx 
   add constraint FK_ACCOUNT_TRX_TO_COST_CHANGE 
   foreign key (cost_change_id) 
   references tbl_cost_change;

-- +--------------------------------------------------------
-- | tbl_doc_log
-- +--------------------------------------------------------

-- (16)

create index ix_doc_log_3 on tbl_doc_log (created_day);

-- +==========================================================================
-- | Additional indices on foreign keys
-- +==========================================================================

-- (17)

-- +--------------------------------------------------------
-- | tbl_account_attr
-- +--------------------------------------------------------
create index ix_account_attr_1 on tbl_account_attr (account_id);

-- (18-19)

-- +--------------------------------------------------------
-- | tbl_account_trx
-- +--------------------------------------------------------
create index ix_account_trx_6 on tbl_account_trx (pos_purchase_id);
create index ix_account_trx_7 on tbl_account_trx (cost_change_id);

-- (20)

-- +--------------------------------------------------------
-- | tbl_device_attr
-- +--------------------------------------------------------
create index ix_device_attr_1 on tbl_device_attr (device_id);

-- (21-23)

-- +--------------------------------------------------------
-- | tbl_device
-- +--------------------------------------------------------
create index ix_device_1 on tbl_device (card_reader_id);
create index ix_device_2 on tbl_device (printer_id);
create index ix_device_3 on tbl_device (printer_group_id);

-- (24-25)

-- +--------------------------------------------------------
-- | tbl_doc_in_out
-- +--------------------------------------------------------
create index ix_doc_in_out_1 on tbl_doc_in_out (doc_in_id);
create index ix_doc_in_out_2 on tbl_doc_in_out (doc_out_id);

-- (26)

-- +--------------------------------------------------------
-- | tbl_doc_in
-- +--------------------------------------------------------
create index ix_doc_in_1 on tbl_doc_in (print_in_id);

-- (27-29)

-- +--------------------------------------------------------
-- | tbl_doc_log
-- +--------------------------------------------------------
create index ix_doc_log_4 on tbl_doc_log (user_id);
create index ix_doc_log_5 on tbl_doc_log (doc_in_id);
create index ix_doc_log_6 on tbl_doc_log (doc_out_id);

-- (30-31)

-- +--------------------------------------------------------
-- | tbl_doc_out
-- +--------------------------------------------------------
create index ix_doc_out_1 on tbl_doc_out (print_out_id);
create index ix_doc_out_2 on tbl_doc_out (pdf_out_id);

-- (32)

-- +--------------------------------------------------------
-- | tbl_ipp_queue_attr
-- +--------------------------------------------------------
create index ix_queue_attr_1 on tbl_ipp_queue_attr (queue_id);

-- (33)

-- +--------------------------------------------------------
-- | tbl_pos_purchase_item
-- +--------------------------------------------------------
create index ix_pos_purchase_item_1 on tbl_pos_purchase_item (pos_purchase_id);

-- (34)

-- +--------------------------------------------------------
-- | tbl_print_in
-- +--------------------------------------------------------
create index ix_print_in_1 on tbl_print_in (queue_id);

-- (35)

-- +--------------------------------------------------------
-- | tbl_printer_attr
-- +--------------------------------------------------------
create index ix_printer_attr_1 on tbl_printer_attr (printer_id);

-- (36-37)

-- +--------------------------------------------------------
-- | tbl_print_out
-- +--------------------------------------------------------
create index ix_print_out_2 on tbl_print_out (cups_job_state);
create index ix_print_out_3 on tbl_print_out (printer_id);

-- (38)

-- +--------------------------------------------------------
-- | tbl_user_attr
-- +--------------------------------------------------------
create index ix_user_attr_1 on tbl_user_attr (user_id);

-- (39)

-- +--------------------------------------------------------
-- | tbl_user_card
-- +--------------------------------------------------------
create index ix_user_card_2 on tbl_user_card (user_id);

-- (40)

-- +--------------------------------------------------------
-- | tbl_user_email
-- +--------------------------------------------------------
create index ix_user_email_2 on tbl_user_email (user_id);

-- (41)

-- +--------------------------------------------------------
-- | tbl_user_group_attr
-- +--------------------------------------------------------
create index ix_user_group_attr_1 on tbl_user_group_attr (user_group_id);

-- (42)

-- +--------------------------------------------------------
-- | tbl_user_number
-- +--------------------------------------------------------
create index ix_user_number_2 on tbl_user_number (user_id);


-- end-of-script
