-- **************************************************************
--
-- TAMS II database upgrade script
-- From v.4.02.03.00 to v.4.03.01.00
-- This script must be run by a user with DBA authority.
-- Open this script using dbisqlc.  Do not copy and paste this script into dbisqlc.
-- This script can take several minutes to open and run.
--
-- **************************************************************

SET TEMPORARY OPTION ALLOW_NULLS_BY_DEFAULT = 'ON';
-- Settings only for Sybase SQL Anywhere 7.
IF (SELECT SUBSTRING(PROPERTY('ProductVersion'), 1, PATINDEX('%.%', PROPERTY('ProductVersion')) - 1)) = '7' THEN
  -- The AUTOMATIC_TIMESTAMP setting is deprecated in Sybase SQL Anywhere 11 - the default behavior is 'OFF'.
  SET TEMPORARY OPTION AUTOMATIC_TIMESTAMP = 'OFF';
END IF;

CREATE VARIABLE v_this_rev VARCHAR(20);
CREATE VARIABLE v_last_rev VARCHAR(20);
CREATE VARIABLE v_rev_date DATE;
CREATE VARIABLE v_rev_note LONG VARCHAR;
CREATE VARIABLE v_msg VARCHAR(254);
CREATE VARIABLE b_upgrade BIT;
CREATE VARIABLE i_script_type INTEGER; -- 0 = Developer Script (Project build), 1 = Production Script (Release build)

SET v_last_rev = 'v.4.02.03.00';
SET v_this_rev = 'v.4.03.01.00';
SET v_rev_date = '2024-02-01';
SET v_rev_note = 'This release implements change(s) TNSS-7042, TNSS-6971, STEL-2125, TNSS-7046, EOD-3, STEL-2497';

SET b_upgrade = 1;
SET i_script_type = 1;

IF i_script_type = 1 THEN
  IF (SELECT COUNT(*) FROM db_release
        WHERE release_num = v_last_rev
          OR (LEFT(release_num, 9) = LEFT(v_this_rev, 9)  -- Accept range of minor version numbers
              AND RIGHT(release_num, 3) BETWEEN '.00' AND RIGHT(v_this_rev, 3))) < 1 THEN
    SET v_msg = 'Database is not on revision ' || v_last_rev || '!';
    SET b_upgrade = 0;
    MESSAGE v_msg TYPE WARNING TO CLIENT;
  END IF;
END IF;

SETUSER tams;

IF b_upgrade = 1 THEN -- Begin the big IF statement!

IF i_script_type = 1 THEN
  SET v_msg = 'Updating database to version ' || v_this_rev || ', please wait...';
  MESSAGE v_msg TO CLIENT;

  DELETE FROM db_release
    WHERE release_num = v_last_rev
      OR (LEFT(release_num, 9) = LEFT(v_this_rev, 9)  -- Accept range of minor version numbers
          AND RIGHT(release_num, 3) BETWEEN '.00' AND RIGHT(v_this_rev, 3));

  SET v_msg = 'Upgrading from ' || v_last_rev || ' to ' || v_this_rev || '...' || '00% done.';
  INSERT INTO db_release VALUES ('NOLOG', v_rev_date, v_msg);
END IF;

--------------------------------------------------------
--
-- Structure changes
--===============================
MESSAGE 'Beginning structure changes.' TO CLIENT;

--BEGIN STEL-2125


IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'END_OF_DAY_MONITOR' AND column_name = 'DW_WIP_EXIST') THEN
	ALTER TABLE END_OF_DAY_MONITOR DROP DW_WIP_EXIST ;
END IF;


--END STEL-2125


--BEGIN EOD-3

IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'END_OF_DAY_PROFILE' AND column_name = 'POSTPONE_EOD_FLAG') THEN
	ALTER TABLE END_OF_DAY_PROFILE MODIFY POSTPONE_EOD_FLAG VARCHAR(1) DEFAULT 'N' 
	                                           CHECK(POSTPONE_EOD_FLAG IN ('Y', 'N'));
ELSE
	ALTER TABLE END_OF_DAY_PROFILE ADD POSTPONE_EOD_FLAG VARCHAR(1) DEFAULT 'N'
                                                          CHECK(POSTPONE_EOD_FLAG IN ('Y', 'N'));
END IF;

ALTER TABLE END_OF_DAY_PROFILE MODIFY POSTPONE_EOD_FLAG   NOT NULL;
COMMENT ON COLUMN END_OF_DAY_PROFILE.POSTPONE_EOD_FLAG IS 'Y=Yes; N=No';

--END EOD-3

MESSAGE '10% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '10% done.' WHERE release_num = 'NOLOG';
END IF;



MESSAGE '20% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '20% done.' WHERE release_num = 'NOLOG';
END IF;



MESSAGE '30% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '30% done.' WHERE release_num = 'NOLOG';
END IF;


--
-- Data changes
--===============================
MESSAGE 'Completed structure changes.' TO CLIENT;
MESSAGE 'Beginning data changes.' TO CLIENT;
MESSAGE '40% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '40% done.' WHERE release_num = 'NOLOG';
END IF;

-- Begin TNSS-7042
IF NOT EXISTS (SELECT * FROM ref_activity_cat WHERE id = 11) THEN
	INSERT INTO ref_activity_cat VALUES (11, 1, 'TRAINING');
End if;

DELETE FROM translation WHERE table_name = 'REF_ACTIVITY_CAT' AND column_name = 'DESCRIPTION' AND ref_id = 11;

INSERT INTO TRANSLATION(TABLE_NAME, COLUMN_NAME, REF_ID, REF_LANGUAGE_CD, TEXT) values ( 'REF_ACTIVITY_CAT', 'DESCRIPTION', 11, 'EN', 'TRAINING');
INSERT INTO TRANSLATION(TABLE_NAME, COLUMN_NAME, REF_ID, REF_LANGUAGE_CD, TEXT) values ( 'REF_ACTIVITY_CAT', 'DESCRIPTION', 11, 'ES', 'CAPACITACIÓN');
INSERT INTO TRANSLATION(TABLE_NAME, COLUMN_NAME, REF_ID, REF_LANGUAGE_CD, TEXT) values ( 'REF_ACTIVITY_CAT', 'DESCRIPTION', 11, 'FR', 'ENTRAÎNEMENT');

-- End TNSS-7042


MESSAGE '50% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '50% done.' WHERE release_num = 'NOLOG';
END IF;

-- Begin TNSS-6971
UPDATE communication_url SET url = 'https://help.genpt.com/tams/Content/Home.htm' WHERE name = 'HELP_AND_LEARNING_CENTER';
-- End TNSS-6971

-- Begin TNSS-7046
DELETE FROM communication_url WHERE id IN (148, 149, 150);
DELETE FROM communication_url WHERE name like 'PROLINK%';

INSERT INTO communication_url VALUES (148,'PROLINK_DEV', 'https://esbuat.genpt.net/prolinkregistration');
INSERT INTO communication_url VALUES (149,'PROLINK_QA',  'https://esbuat.genpt.net/prolinkregistration');
INSERT INTO communication_url VALUES (150,'PROLINK_PROD','https://esb.genpt.net/prolinkregistration');

-- End TNSS-7046

MESSAGE '60% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '60% done.' WHERE release_num = 'NOLOG';
END IF;



MESSAGE '70% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '70% done.' WHERE release_num = 'NOLOG';
END IF;



--
-- Stored Procedure Changes
--===============================
MESSAGE 'Completed data changes.' TO CLIENT;
MESSAGE 'Beginning stored procedure and function changes.' TO CLIENT;
MESSAGE '80% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '80% done.' WHERE release_num = 'NOLOG';
END IF;



--
-- Trigger Changes
--===============================
MESSAGE 'Completed stored procedure and function changes.' TO CLIENT;
MESSAGE 'Beginning trigger changes.' TO CLIENT;
MESSAGE '90% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '90% done.' WHERE release_num = 'NOLOG';
END IF;

--BEGIN STEL-2497

CALL tams_dba.sp_drop_object('Trigger', 'tr_au_store_profile');

 CREATE TRIGGER tr_au_store_profile
  AFTER UPDATE ON STORE_PROFILE
  REFERENCING OLD AS deleted NEW AS inserted
  for each row BEGIN
  -- Description: This trigger is fired after a store profile record is updated. This trigger calls
  -- a stored procedure that validates employee access based on business rules.
  --
  -- Modification History:
  -- 2023-01-23 : RSC :  Jira# 2497 : Add Customer Domain update to serving DC change 
  -- 2023-09-18 : RSC : Add Customer Domain update to store number change
  -- 2012-02-01 : CKB : Bug# TII-14554: IPC Set INVOICING_PROFILE.USE_INTEGRATED_PAYMENTS to 'N' if STORE_NUM is modified.
  -- 2011-12-05 : CKB : BUg# TII-12686: IPC Set IPC_STORE_NUMBER to null if STORE_NUM is modified.
  -- 2010-09-09 : CJS : Bug# 56079: Set answer to 'L' for all reports that use ref_report_question.id 15 (Pricing Mode)
  --                                if landed factors is turned on.
  -- 2008-07-30 : CJS : Bug# 34622: Stored Procedure and Triggers required for Store Employee Access Validation project
  -- 2008-07-30 : CJS : Written.
  IF ISNULL(STRING(deleted.GPC_OWNED),'') <> ISNULL(STRING(inserted.GPC_OWNED),'') THEN
    CALL sp_apply_seav_rules(inserted.LOC, inserted.MODIFIED_BY_EMPLOYEE_ID, inserted.LAST_MODIFIED_DATE);
  END IF;
  IF ISNULL(STRING(deleted.EMPLOYEE_ACCESS_VALIDATION),'') <> ISNULL(STRING(inserted.EMPLOYEE_ACCESS_VALIDATION),'') THEN
    CALL sp_apply_seav_rules(inserted.LOC, inserted.MODIFIED_BY_EMPLOYEE_ID, inserted.LAST_MODIFIED_DATE);
  END IF;
  IF ISNULL(STRING(deleted.CURRENCY_CONVERSION_FACTOR),'') <> ISNULL(STRING(inserted.CURRENCY_CONVERSION_FACTOR),'') THEN
    IF inserted.CURRENCY_CONVERSION_FACTOR > 0 THEN
      UPDATE report_answer
        SET answer = 'L'
        WHERE ref_report_question_id = 15;
    ELSE
      UPDATE report_answer
        SET answer = 'S'
        WHERE ref_report_question_id = 15;
    END IF;
  END IF;
  IF ISNULL(STRING(deleted.STORE_NUM),'') <> ISNULL(STRING(inserted.STORE_NUM),'') THEN
    UPDATE store_profile    
      SET ipc_store_number = NULL;
    UPDATE invoicing_profile
      SET use_integrated_payments = 0;
    -- sending Customer_id = 0 for store_profile update
    -- the old store number is in the deleted_value column
    IF ((Select send_customer_domain from tams.store_profile) = 'Y') THEN
        Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (0,deleted.loc,'U','STORE_PROFILE',deleted.store_num);
    END IF;
  END IF; 
  -- sending prior value in deleted field 
  IF ISNULL(STRING(deleted.NAPA_ACCT_DC_CODE), '') <> ISNULL(STRING(inserted.NAPA_ACCT_DC_CODE), '') THEN
      IF ((Select send_customer_domain from tams.store_profile) = 'Y') THEN
          Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value)
  			 Values (0,deleted.loc,'U','STORE_PROFILE',deleted.napa_acct_dc_code);
      END IF;
  END IF; 
END;

--End STEL-2497
--
-- View Changes
--===============================
MESSAGE 'Completed trigger changes.' TO CLIENT;
MESSAGE 'Beginning view changes.' TO CLIENT;



MESSAGE 'Completed view changes.' TO CLIENT;

--------------------------------------------------------

IF i_script_type = 1 THEN
  DELETE FROM db_release WHERE release_num = 'NOLOG' AND release_date = v_rev_date;
  INSERT INTO db_release VALUES(v_this_rev, v_rev_date, v_rev_note);
END IF;

MESSAGE 'Synchronizing TABLE_SEQUENCE....' TO CLIENT;
CALL sp_sync_table_sequence(1);

END IF; -- End the big IF statement!

SETUSER ;

DROP VARIABLE v_this_rev;
DROP VARIABLE v_last_rev;
DROP VARIABLE v_rev_date;
DROP VARIABLE v_rev_note;
DROP VARIABLE v_msg;

IF b_upgrade = 1 THEN
  MESSAGE '100% done.' TO CLIENT;
ELSE
  MESSAGE 'Upgrade aborted.' TO CLIENT;
END IF;

DROP VARIABLE b_upgrade;
DROP VARIABLE i_script_type;