--#########################################################################################################################--
--#########################################################################################################################--
--
-- KALS_APPLE_IDS_FROM_API.sql
-- RUN THESE STATEMENTS ONCE EACH MONTH, ONCE THE ITUNES BATCHES ARE LOADED.
--
--#########################################################################################################################--
--#########################################################################################################################--


DELETE TEMP_TC;

INSERT INTO TEMP_TC
   SELECT DISTINCT   RECORDING_SENDER_ID
   FROM              ROYALTIES, ROYALTIES_ORIGINAL_DATA
   WHERE             ROYALTIES.ID = ROYALTIES_ORIGINAL_DATA.ID
   AND               EXP_VERSION_ID IS NULL
   AND               ROYALTIES.BATCH_ID IN ( 'BCH3184063', 'BCH3184321', 'BCH3184322', 'BCH3184323', 'BCH3183544', 'BCH3183822',
                                             'BCH3183542', 'BCH3183540', 'BCH3183539', 'BCH3183538', 'BCH3183537', 'BCH3183536',
                                             'BCH3183535', 'BCH3183534', 'BCH3183820', 'BCH3183821', 'BCH3183533', 'BCH3183532',
                                             'BCH3183531', 'BCH3183530', 'BCH3183516', 'BCH3183529', 'BCH3183515', 'BCH3183513',
                                             'BCH3183526', 'BCH3183525', 'BCH3183524', 'BCH3183512', 'BCH3183511', 'BCH3183523',
                                             'BCH3183679', 'BCH3183680', 'BCH3183701', 'BCH3183702', 'BCH3183703', 'BCH3183704',
                                             'BCH3183705', 'BCH3183706', 'BCH3183509', 'BCH3184324', 'BCH3184325', 'BCH3184326',
                                             'BCH3184327', 'BCH3184328', 'BCH3184329', 'BCH3184330', 'BCH3184331', 'BCH3184332',
                                             'BCH3184333', 'BCH3184334', 'BCH3184335', 'BCH3184336', 'BCH3183480', 'BCH3183479',
                                             'BCH3183478', 'BCH3183497', 'BCH3183496', 'BCH3183477', 'BCH3183476', 'BCH3183816',
                                             'BCH3183492', 'BCH3183491', 'BCH3183489', 'BCH3183488', 'BCH3183486', 'BCH3183484',
                                             'BCH3183483', 'BCH3183482', 'BCH3183481', 'BCH3183460', 'BCH3183459', 'BCH3183458',
                                             'BCH3183457', 'BCH3183456', 'BCH3183454', 'BCH3183475', 'BCH3183474', 'BCH3183452',
                                             'BCH3183472', 'BCH3183451', 'BCH3183450', 'BCH3183449', 'BCH3183448', 'BCH3183447',
                                             'BCH3183471', 'BCH3183470', 'BCH3183469', 'BCH3183468', 'BCH3183467', 'BCH3183466',
                                             'BCH3183464', 'BCH3183463', 'BCH3183324', 'BCH3183322', 'BCH3183299', 'BCH3183298',
                                             'BCH3183297', 'BCH3183296', 'BCH3183295', 'BCH3183294', 'BCH3183293', 'BCH3183292',
                                             'BCH3183291', 'BCH3183289', 'BCH3183288', 'BCH3183287', 'BCH3183285', 'BCH3183257',
                                             'BCH3183256', 'BCH3183255', 'BCH3183254', 'BCH3183283', 'BCH3183674', 'BCH3183675',
                                             'BCH3183280', 'BCH3183279', 'BCH3183278', 'BCH3183277', 'BCH3183276', 'BCH3183246',
                                             'BCH3183245', 'BCH3183274', 'BCH3183273', 'BCH3183271', 'BCH3183269', 'BCH3183268',
                                             'BCH3183267', 'BCH3183266', 'BCH3183243', 'BCH3183242', 'BCH3183265', 'BCH3183264',
                                             'BCH3183263', 'BCH3183262', 'BCH3183261', 'BCH3183240', 'BCH3183239', 'BCH3183238',
                                             'BCH3183241', 'BCH3183236', 'BCH3183234', 'BCH3183233', 'BCH3183232', 'BCH3183231',
                                             'BCH3183230', 'BCH3183229', 'BCH3183226', 'BCH3183225', 'BCH3183224', 'BCH3183223',
                                             'BCH3183222', 'BCH3183221', 'BCH3183220', 'BCH3183179', 'BCH3183178', 'BCH3183218',
                                             'BCH3183643', 'BCH3183493', 'BCH3183707', 'BCH3183708', 'BCH3183709', 'BCH3183710',
                                             'BCH3183465', 'BCH3183180', 'BCH3183991', 'BCH3183990', 'BCH3183989', 'BCH3183988',
                                             'BCH3183987', 'BCH3183986', 'BCH3183985', 'BCH3183543', 'BCH3183824', 'BCH3183825',
                                             'BCH3183541', 'BCH3183971', 'BCH3183972', 'BCH3183973', 'BCH3183974', 'BCH3183975',
                                             'BCH3183976', 'BCH3183977', 'BCH3183978', 'BCH3183979', 'BCH3183514', 'BCH3183819',
                                             'BCH3183487', 'BCH3183485', 'BCH3183453', 'BCH3183323', 'BCH3183300', 'BCH3183969',
                                             'BCH3183970', 'BCH3183286', 'BCH3183272', 'BCH3183270', 'BCH3183237', 'BCH3183517',
                                             'BCH3183490', 'BCH3183826', 'BCH3183827', 'BCH3183828', 'BCH3183455', 'BCH3183446',
                                             'BCH3183290', 'BCH3183275', 'BCH3183219', 'BCH3183527', 'BCH3183817', 'BCH3183818',
                                             'BCH3183473', 'BCH3183284', 'BCH3183235' )
   MINUS
   SELECT DISTINCT   TO_CHAR(TRACK_ID)
   FROM              KALS_APPLE_TRACK_TO_PRODUCT_ID;

COMMIT;

DELETE FROM    TEMP_TC
WHERE          X IS NULL;

DELETE FROM    KALS_APPLE_TRACK_TO_PRODUCT_ID
WHERE          PRODUCT_ID IS NULL;

COMMIT;

-----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
-- RUN THESE STATEMENTS ONLY AFTER THOSE ABOVE HAVE BEEN DONE. YOU MAY NEED TO RUN IT SEVERAL TIMES.

SET SERVEROUTPUT OFF
SET DEFINE OFF

DECLARE

   lResponse      VARCHAR2(4000);
   lProductId     VARCHAR2(20);
   lStart         INTEGER;
   lEnd           INTEGER;
   lSleepCounter  INTEGER := 0;

BEGIN

   FOR ROW IN (
                  SELECT *
                  FROM   TEMP_TC
               )
   LOOP

      lProductId := NULL;

      DBMS_OUTPUT.PUT(ROW.X||'=');

      lResponse := UTL_HTTP.REQUEST('http://itunes.apple.com/lookup?country=gb&media=music&entity=musicTrack&id='||ROW.X);

      --DBMS_OUTPUT.PUT_LINE(lResponse);

      lStart := INSTR(lResponse, 'collectionId":');

      IF lStart > 0 THEN

         lStart      := lStart + 14;
         lEnd        := INSTR(lResponse, ', ', lStart);
         lProductId  := SUBSTR(lResponse, lStart, lEnd-lStart);

      END IF;

      DBMS_OUTPUT.PUT_LINE(lProductId);

      DELETE FROM    TEMP_TC
      WHERE          X = ROW.X;

      INSERT INTO    KALS_APPLE_TRACK_TO_PRODUCT_ID (TRACK_ID, PRODUCT_ID, JSON_RESPONSE)
      VALUES         (ROW.X, lProductId, lResponse);

      COMMIT;

      lSleepCounter := lSleepCounter + 1;

      IF lSleepCounter = 100 THEN

         --DBMS_LOCK.SLEEP(10) ;
         lSleepCounter := 0;

      END IF;

   END LOOP;

END;
/

-----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
-- USE THIS QUERY TO CHECK IF THE ABOVE HAS FINISHED COMPLETELY. IT SHOULD RETURN 0 IF IT HAS.


SELECT   COUNT(*)
FROM     TEMP_TC;

-----------------------------------------------------------------------------------------------------------------------------
--#########################################################################################################################--
--#########################################################################################################################--