-- **************************************************************
--
-- TAMS II database upgrade script
-- From v.3.47.05.00 to v.3.48.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.3.47.05.00';
SET v_this_rev = 'v.3.48.01.00';
SET v_rev_date = '2023-11-28';
SET v_rev_note = 'This release implements change(s) TNSS-6692, TNSS-6652, TNSS-6877, STEL-2402';

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 TNSS-6652
IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'INDAGO_PROFILE' AND column_name = 'JMO_NOTIFICATION_EMAIL') THEN
	ALTER TABLE INDAGO_PROFILE MODIFY JMO_NOTIFICATION_EMAIL VARCHAR(100);
ELSE
	ALTER TABLE INDAGO_PROFILE ADD JMO_NOTIFICATION_EMAIL VARCHAR(100);
END IF;

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

ALTER TABLE INDAGO_PROFILE MODIFY JMO_NOTIFICATION_CD NOT NULL;

COMMENT ON COLUMN INDAGO_PROFILE.JMO_NOTIFICATION_CD IS 'N=Do not send notification; Y=Send all notifications (success & failure); F=Send failure only notifications';
-- End TNSS-6652


--Begin STEL-2402



IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'CUSTOMER_CHANGE_QUEUE' AND column_name = 'RETRY_COUNT') THEN
	ALTER TABLE CUSTOMER_CHANGE_QUEUE MODIFY RETRY_COUNT SMALLINT DEFAULT 0;
ELSE
	ALTER TABLE CUSTOMER_CHANGE_QUEUE ADD RETRY_COUNT SMALLINT DEFAULT 0 ;                                          
END IF;

ALTER TABLE CUSTOMER_CHANGE_QUEUE MODIFY RETRY_COUNT NOT NULL ;


--End STEL-2402



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-6877
DELETE FROM communication_url WHERE id IN (142, 143, 144);

INSERT INTO communication_url VALUES (142,'CUSTOMER_DOMAIN_FEED_SERVICE_DEV', 'https://scorecard.customer.dev.genpt.com/customer-score-card/api/v1/tams/publish' );
INSERT INTO communication_url VALUES (143,'CUSTOMER_DOMAIN_FEED_SERVICE_QA',  'https://scorecard.customer.qa.genpt.com/customer-score-card/api/v1/tams/publish');
INSERT INTO communication_url VALUES (144,'CUSTOMER_DOMAIN_FEED_SERVICE_PROD','https://scorecard.customer.genpt.com/customer-score-card/api/v1/tams/publish');

-- End TNSS-6877

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;



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;

-- Begin TNSS-6692
CALL tams_dba.sp_drop_object('function', 'fn_get_next_fotab_identifier');


CREATE FUNCTION fn_get_next_fotab_identifier (
  IN  in_loc INTEGER,
  IN  in_type VARCHAR(2))
  RETURNS VARCHAR(5)
BEGIN
    DECLARE max_identifier VARCHAR(6);
    DECLARE type_pattern VARCHAR(3);
    DECLARE identifier_number INTEGER;
    DECLARE new_identifier_number INTEGER;

    SET type_pattern = STRING(in_type, '%');
    
    SELECT MAX(IDENTIFIER)
        INTO max_identifier
        FROM FACTORY_ORDER_TABLE
        WHERE IDENTIFIER LIKE type_pattern;
    
    SET identifier_number = CAST(SUBSTRING(max_identifier, 3, 3) AS INTEGER);
    
    IF (identifier_number is null or identifier_number >= 999)
        THEN
            SET new_identifier_number = 1;
        ELSE
            SET new_identifier_number = identifier_number + 1;
        END IF;
    
    WHILE EXISTS (SELECT IDENTIFIER
                      FROM FACTORY_ORDER_TABLE
                      WHERE IDENTIFIER = STRING(in_type, (select REPLICATE('0',3 - LENGTH(new_identifier_number))), new_identifier_number)) LOOP
        SET new_identifier_number = new_identifier_number + 1;
    
        IF (new_identifier_number >= 999) THEN
            RETURN 'ERROR'
        END IF;
        
    END LOOP;
        
    RETURN STRING(in_type, (select REPLICATE('0',3 - LENGTH(new_identifier_number))), new_identifier_number);
END;

GRANT EXECUTE ON fn_get_next_fotab_identifier TO tams_application;
GRANT EXECUTE ON fn_get_next_fotab_identifier TO support_l34;


-- End TNSS-6692

--
-- 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;



--
-- 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;