DROP PROCEDURE IF EXISTS CreateSystemRecord;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE CreateSystemRecord(IN_CONTEXTID varchar(50), IN_CHANNELID INT, IN_ASSETPROFILE varchar(500),
    IN_PROGRAMEXTERNALID varchar(20), IN_PROGRAMSTARTTIME bigint,IN_PROGRAMDURATION int,IN_RECORDINGDURATION INT,IN_ENABLENPVRTRICKPLAY INT,
    IN_ENABLENPVRSKIPJUMP INT,IN_EVENTUPDATESTATUS varchar(10),IN_SCHEDULEQUALIFIER1 varchar(640),IN_SCHEDULEQUALIFIER2 varchar(640),
    IN_SCHEDULEQUALIFIER3 varchar(640),IN_SCHEDULEQUALIFIER4 varchar(640)
    ,IN_SCHEDULEQUALIFIER5 varchar(640),IN_CHANNELLANGUAGE varchar(20),IN_RECORDINGSTARTTIME bigint,
    IN_PROGRAMDETAILRECORD varchar(2000), IN_EPISODEID varchar(5), IN_SERIESREFERENCE varchar(15),
    IN_programtitle varchar(128), IN_BLACK_LISTED_MDP VARCHAR(500), EPISODESCOPE VARCHAR(5),
    OUT OUT_RECORDID INT, IN_CURRENTTIME BIGINT, IN_HTTPPATH VARCHAR(256), IN_MSSPPATH VARCHAR(256), IN_RECPATH3GP VARCHAR(256),
    IN_ENABLEPRIVATECOPY INT, IN_SCHEDULEID VARCHAR(256), IN_CHANNELREFNUMBER VARCHAR(50), IN_ISMEDIATORDISABLE INT,
    IN_CHANNEL_SCHEDULEID_MAPPING VARCHAR(4000), IN_COPY_PROTECTIONS VARCHAR(256), IN_EXCLUDED_PLATFORMS varchar(100))
BEGIN

  DECLARE PID INT;
  DECLARE NPVRASSETCOUNT INT;
  DECLARE V_STATUS VARCHAR(20);
  DECLARE V_STATUS_DETAILS VARCHAR(20);

    DROP TABLE IF EXISTS ASSET_DATA;
    CREATE temporary TABLE ASSET_DATA engine=memory

    SELECT * FROM (
    SELECT IF(ch.CHANNELID <> IN_CHANNELID, findScheduleId(CONCAT('INCLUDEDID_', ch.CHANNELEXTERNALID), IN_CHANNEL_SCHEDULEID_MAPPING), IN_SCHEDULEID) ASSET_SCHEDULEID,
    externalID, parentexternalID, ACCESSURL, MULTICASTIP, MULTICASTPORT, RECSUBSYSTEM, RECORDINGPATH,ch.ASSETPROFILE, ch.CHANNELEXTERNALID
    FROM (SELECT channelID, mtp.externalID, mtp.parentexternalID, ACCESSURL, MULTICASTIP,
    MULTICASTPORT, (mtp.RECORDINGSUBSYSTEM) RECSUBSYSTEM, mtp.RECORDINGPATH
    FROM (select distinct c.mtpexternalid, c.ACCESSURL, c.MULTICASTIP, c.MULTICASTPORT, c.CHANNELID
    from CHANNELSTREAM c
    INNER JOIN CHANNELSTREAMMAPPING csm
    ON c.channelID = csm.channelId and
    c.mtpexternalid = csm.MTPEXTERNALID
    where (c.channelid = IN_CHANNELID
    OR c.channelID IN (select INCLUDEDCHANNELID from INCLUDEDCHANNELS ic where ic.CHANNELID = IN_CHANNELID))
    AND NOT find_in_set(csm.MDPEXTERNALID, IN_BLACK_LISTED_MDP)
    AND c.ENABLENPVR = 1)
    T INNER JOIN MEDIATYPEPROFILES mtp
    ON (T.mtpexternalid = mtp.externalid
    OR T.MTPEXTERNALID = mtp.PARENTEXTERNALID)
    WHERE mtp.CONTEXTID = IN_CONTEXTID) TEMP
    INNER JOIN CHANNEL ch
    ON TEMP.CHANNELID = ch.CHANNELID
    ORDER BY IF(ch.CHANNELID = IN_CHANNELID, 0, 1)
    ) TEMP GROUP BY EXTERNALID;

    SELECT COUNT(*) INTO NPVRASSETCOUNT FROM ASSET_DATA;

    IF (NPVRASSETCOUNT = 0) THEN
        SET OUT_RECORDID = 0;
    ELSE

        SELECT PROGRAMID INTO PID
        FROM PROGRAMDETAILS
        WHERE  CONTEXTID = IN_CONTEXTID
        AND PROGRAMEXTERNALID = IN_PROGRAMEXTERNALID;

        IF PID IS NULL THEN
        INSERT IGNORE INTO PROGRAMDETAILS (ProgramExternalID, contextID, episodeID, SERIESREFERENCE, programtitle, PROGRAMDETAILRECORD, ENABLEINDIVIDUALCOPY)
        Values(IN_PROGRAMEXTERNALID, IN_CONTEXTID, IN_EPISODEID, IN_SERIESREFERENCE, IN_programtitle, IN_PROGRAMDETAILRECORD, IN_ENABLEPRIVATECOPY);
        END IF;

        INSERT INTO SYSTEMNPVRRECORDING
        (CHANNELID,PROGRAMEXTERNALID,CONTEXTID,PROGRAMSTARTTIME,PROGRAMDURATION,RECORDINGDURATION,ENABLENPVRTRICKPLAY,
        ENABLENPVRSKIPJUMP,EVENTUPDATESTATUS,SCHEDULEQUALIFIER1,SCHEDULEQUALIFIER2,SCHEDULEQUALIFIER3,SCHEDULEQUALIFIER4
        ,SCHEDULEQUALIFIER5,CHANNELLANGUAGE,RECORDINGSTARTTIME,EPISODESCOPE, SYSREQUESTEDEPISODE, SCHEDULEID, CHANNELREFNUMBER, COPYPROTECTIONS, EXCLUDEDPLATFORMS)
        VALUES(IN_CHANNELID,IN_PROGRAMEXTERNALID,IN_CONTEXTID,IN_PROGRAMSTARTTIME,IN_PROGRAMDURATION,IN_RECORDINGDURATION,IN_ENABLENPVRTRICKPLAY,
        IN_ENABLENPVRSKIPJUMP,IN_EVENTUPDATESTATUS,IN_SCHEDULEQUALIFIER1,IN_SCHEDULEQUALIFIER2,IN_SCHEDULEQUALIFIER3,IN_SCHEDULEQUALIFIER4
        ,IN_SCHEDULEQUALIFIER5,IN_CHANNELLANGUAGE,IN_RECORDINGSTARTTIME,EPISODESCOPE, IF(EVENTUPDATESTATUS = 'Complete', 1, 0), IN_SCHEDULEID, 
        IN_CHANNELREFNUMBER, IN_COPY_PROTECTIONS, IN_EXCLUDED_PLATFORMS);

        SELECT LAST_INSERT_ID() into OUT_RECORDID;

        IF (IN_ISMEDIATORDISABLE = 1) THEN
            IF ((UNIX_TIMESTAMP() * 1000) > IN_RECORDINGSTARTTIME) THEN
                SET V_STATUS = "RecordFailed";
                SET V_STATUS_DETAILS = "Recording Failed";
            ELSE
                SET V_STATUS = "ScheduleSuccess";
                SET V_STATUS_DETAILS = "Schedule Success";
            END IF;
        ELSE
            SET V_STATUS = "Scheduling";
            SET V_STATUS_DETAILS = "Scheduling";
        END IF;

        INSERT INTO NPVRASSET (RECORDID, MTPExternalID, STATUS, STATUSDETAIL, ASSETSTARTDELTA, ASSETPROFILE, ASSETURL, ASSETSTARTTIME, SCHEDULEID, CHANNELEXTERNALID)
        SELECT OUT_RECORDID, externalID, V_STATUS, V_STATUS_DETAILS, 0, ASSETPROFILE,
        generateRecordFileName(RECSUBSYSTEM, ACCESSURL, MULTICASTIP, MULTICASTPORT, IN_RECORDINGSTARTTIME, RECORDINGPATH,IN_HTTPPATH, IN_MSSPPATH, IN_RECPATH3GP,
        IN_PROGRAMSTARTTIME, IN_RECORDINGDURATION, IN_PROGRAMEXTERNALID), IN_RECORDINGSTARTTIME, ASSET_SCHEDULEID, CHANNELEXTERNALID
        FROM ASSET_DATA
        WHERE parentexternalID IS NULL;

        INSERT INTO NPVRASSET (RECORDID, MTPExternalID, STATUS, STATUSDETAIL, ASSETSTARTDELTA, ASSETPROFILE, ASSETURL, PARENTASSETID, ASSETSTARTTIME, SCHEDULEID, CHANNELEXTERNALID)
        SELECT OUT_RECORDID, externalID, V_STATUS, V_STATUS_DETAILS, 0, ASSETPROFILE,
        generateRecordFileName(RECSUBSYSTEM, ACCESSURL, MULTICASTIP, MULTICASTPORT, IN_RECORDINGSTARTTIME, RECORDINGPATH,IN_HTTPPATH, IN_MSSPPATH, IN_RECPATH3GP,
        IN_PROGRAMSTARTTIME, IN_RECORDINGDURATION, IN_PROGRAMEXTERNALID),
        (SELECT ID FROM NPVRASSET WHERE MTPEXTERNALID = PARENTEXTERNALID AND RECORDID = OUT_RECORDID),
        IN_RECORDINGSTARTTIME, ASSET_SCHEDULEID, CHANNELEXTERNALID
        FROM ASSET_DATA
        WHERE parentexternalID IS NOT NULL;
    END IF;

    DROP TABLE ASSET_DATA;

    END$$
DELIMITER ;

==================================================

DROP PROCEDURE IF EXISTS SCHEDULE_SERIES_RECORD;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SCHEDULE_SERIES_RECORD`(IN_USERID INT, IN_CONTEXTID varchar(500),
IN_SERIESREFNO varchar(20), IN_EPSCOPE_SCOPE varchar(30),
IN_ChannelBound tinyint(1), IN_AUTO_DELETE_WHEN_SPACE_NEEDED INT, IN_CHANNELID INT
, IN_EPISODE_LIMIT INT, OUT TEMPID INT, IN_CURRENTTIME BIGINT, RECORDIDS TEXT, ENABLESTARTSTOPMARKS INT,
IN_SERIESEXISTS INT, ENABLEPRIVATECOPY INT, OUT PRIVATEASSETRECORDIDS TEXT, IN_PROGRAMTITLE VARCHAR(128))
BEGIN

SELECT SERIESID INTO TEMPID FROM SYSTEMNPVRSERIESRECORDING
WHERE CONTEXTID = IN_CONTEXTID AND SERIESREFNO = IN_SERIESREFNO
LIMIT 1;

IF(TEMPID IS NULL) THEN

    INSERT INTO SYSTEMNPVRSERIESRECORDING (ContextID, SERIESREFNO, Title) VALUES(IN_CONTEXTID, IN_SERIESREFNO, IN_PROGRAMTITLE);
    SELECT LAST_INSERT_ID() INTO TEMPID;
END IF;

COMMIT;

IF(IN_SERIESEXISTS = 0) THEN

    INSERT INTO USERNPVRSERIESRECORDING (userID, SERIESID, CHANNELID, DELETEWHENSPACENEEDED, CHANNELBOUND, EPISODESCOPE, EPISODESTOKEEP, SCHEDULETIMESTAMP)
    VALUES(IN_USERID, TEMPID, IN_CHANNELID, IN_AUTO_DELETE_WHEN_SPACE_NEEDED, IN_ChannelBound, IN_EPSCOPE_SCOPE, IN_EPISODE_LIMIT, CURRENT_TIMESTAMP);
ELSE
    UPDATE USERNPVRSERIESRECORDING
    SET MARKEDFORDELETE = 0,
    CHANNELID = IN_CHANNELID,
    DELETEWHENSPACENEEDED = IN_AUTO_DELETE_WHEN_SPACE_NEEDED,
    CHANNELBOUND = IN_ChannelBound,
    EPISODESCOPE = IN_EPSCOPE_SCOPE,
    EPISODESTOKEEP = IN_EPISODE_LIMIT,
    SCHEDULETIMESTAMP = CURRENT_TIMESTAMP
    WHERE USERID = IN_USERID
    AND SERIESID = TEMPID;
END IF;

CREATE temporary TABLE IF NOT EXISTS TEMP_TABLE_IDS(ID BIGINT) ENGINE=MEMORY;

CALL POPULATE_TEMP_TABLE(RECORDIDS);

CALL CreateUserRecording(IN_USERID, TEMPID, IN_AUTO_DELETE_WHEN_SPACE_NEEDED, IN_CURRENTTIME, ENABLESTARTSTOPMARKS, ENABLEPRIVATECOPY, IN_CONTEXTID, PRIVATEASSETRECORDIDS);

DELETE FROM TEMP_TABLE_IDS;

END$$
DELIMITER ;

==================================

DROP PROCEDURE IF EXISTS UPDATE_NPVRASSET_STATUS;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE UPDATE_NPVRASSET_STATUS(IN_RECORDID BIGINT, IN_STATUS VARCHAR(20), 
IN_STATUSDETAIL VARCHAR(500), IN_BATCH_SIZE INT, OUT OUT_RECORDS_UPDATED INT)
BEGIN

DECLARE V_ROWCOUNT INT DEFAULT 1;

SET OUT_RECORDS_UPDATED = 0;

WHILE (V_ROWCOUNT > 0) DO

    UPDATE NPVRASSET SET STATUS = IN_STATUS, STATUSDETAIL = IN_STATUSDETAIL
    WHERE RECORDID = IN_RECORDID
    AND STATUS IN ('Scheduling', 'Rescheduling', 'ScheduleSuccess', 'RescheduleSuccess')
    AND STATUS <> IN_STATUS
    LIMIT IN_BATCH_SIZE;

    SELECT ROW_COUNT() INTO V_ROWCOUNT;
    SET OUT_RECORDS_UPDATED = OUT_RECORDS_UPDATED + V_ROWCOUNT;

    IF V_ROWCOUNT > 0 THEN
        COMMIT;
    END IF;

END WHILE;

END$$
DELIMITER ;

======================================

DROP PROCEDURE IF EXISTS UPDATE_SERIES_RECORD;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE UPDATE_SERIES_RECORD(IN_USERID INT, IN_SERIESID INT, IN_EPSCOPE_SCOPE varchar(30),
IN_ChannelBound tinyint(1), DELETE_FLAG_CHANGED INT, EPISODE_LIMIT_DECREASED INT, IN_AUTO_DELETE_WHEN_SPACE_NEEDED INT, IN_EPISODE_LIMIT INT,
IN_CURRENTTIME BIGINT, RECORDIDS TEXT, OUT OUT_DELETE_REC_IDS TEXT, ENABLESTARTSTOPMARKS INT, ENABLEPRIVATECOPY INT, IN_CONTEXTID VARCHAR(50),
OUT PRIVATEASSETRECORDIDS TEXT, IN_ENABLEREBROADCASTHAINDLING INT)
BEGIN

DECLARE DELETED_RECORDIDS TEXT;

DROP TABLE IF EXISTS TEMP_TABLE_IDS;
CREATE temporary TABLE TEMP_TABLE_IDS(ID BIGINT) ENGINE=MEMORY;

CALL POPULATE_TEMP_TABLE(RECORDIDS);

DROP TABLE IF EXISTS DELETEDRECORDIDS;

CREATE TEMPORARY TABLE DELETEDRECORDIDS ENGINE=MEMORY
SELECT U.RECORDID FROM USERNPVRRECORDING U
WHERE userID = IN_USERID
AND SERIESID = IN_SERIESID
AND U.RECORDID NOT IN (SELECT ID FROM TEMP_TABLE_IDS)
AND EXISTS
    (
        SELECT 1
        FROM SYSTEMNPVRRECORDING S
        WHERE U.RECORDID = S.RECORDID
        AND IN_CURRENTTIME < RECORDINGSTARTTIME
    )
AND U.MARKEDFORDELETE = 0;

UPDATE USERNPVRSERIESRECORDING
SET
DELETEWHENSPACENEEDED = IN_AUTO_DELETE_WHEN_SPACE_NEEDED,
CHANNELBOUND = IN_ChannelBound,
EPISODESCOPE = IN_EPSCOPE_SCOPE,
EPISODESTOKEEP = IN_EPISODE_LIMIT
WHERE userID = IN_USERID
AND SERIESID = IN_SERIESID;

IF(DELETE_FLAG_CHANGED = 1) THEN
CALL UPDATE_DELETEWHENSPACENEEDEDFLAG(IN_USERID, IN_SERIESID, IN_AUTO_DELETE_WHEN_SPACE_NEEDED);
END IF;

IF(EPISODE_LIMIT_DECREASED = 1) THEN
CALL VALIDATE_DELETE_SERIES_EPISODES(IN_USERID, IN_SERIESID, IN_EPISODE_LIMIT, DELETED_RECORDIDS, IN_ENABLEREBROADCASTHAINDLING);
END IF;

CALL CreateUserRecording(IN_USERID, IN_SERIESID, IN_AUTO_DELETE_WHEN_SPACE_NEEDED, IN_CURRENTTIME, ENABLESTARTSTOPMARKS, ENABLEPRIVATECOPY, IN_CONTEXTID, PRIVATEASSETRECORDIDS);

UPDATE USERNPVRRECORDING U
SET MARKEDFORDELETE = 1,
STATUS = 'ScheduleCancelled',
STATUSDETAILS = 'Schedule Cancelled'
WHERE userID = IN_USERID
AND U.RECORDID IN(SELECT D.RECORDID FROM DELETEDRECORDIDS D);

SELECT GROUP_CONCAT(RECORDID SEPARATOR ",") ids
INTO OUT_DELETE_REC_IDS
FROM DELETEDRECORDIDS;

IF(length(DELETED_RECORDIDS) > 0
            AND length(OUT_DELETE_REC_IDS) > 0) THEN
            select CONCAT(CONCAT(DELETED_RECORDIDS, ','), OUT_DELETE_REC_IDS)
            INTO OUT_DELETE_REC_IDS;
        ELSEIF(length(DELETED_RECORDIDS) > 0) THEN
            SET OUT_DELETE_REC_IDS = DELETED_RECORDIDS;
        END IF;

DROP TABLE IF EXISTS TEMP_TABLE_IDS;

DROP TABLE IF EXISTS DELETEDRECORDIDS;

END$$
DELIMITER ;

===========================================

DROP PROCEDURE IF EXISTS UPDATEUSERNPVRSTATUSBYRECORDID;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE UPDATEUSERNPVRSTATUSBYRECORDID(BATCHSIZE INT, IN_RECORDID BIGINT, IN_STATUS VARCHAR(20), IN_STATUSDETAILS VARCHAR(500), IN_ISFAILED INT, OUT TOTALROWSUPDATED INT)
BEGIN

    SET TOTALROWSUPDATED = 0;

    UPDATE_LOOP: LOOP

        UPDATE USERNPVRRECORDING
        SET STATUS=IN_STATUS, STATUSDETAILS=IN_STATUSDETAILS, ISFAILED = IN_ISFAILED
        WHERE RECORDID = IN_RECORDID
        AND MARKEDFORDELETE=0
        AND ISFAILED = 0
        AND STATUS <> IN_STATUS;

        SET TOTALROWSUPDATED = TOTALROWSUPDATED + ROW_COUNT();

        IF(ROW_COUNT() = 0) THEN
            LEAVE UPDATE_LOOP;
        END IF;

        COMMIT;
    END LOOP UPDATE_LOOP;

END $$
DELIMITER ;

==================================

DROP PROCEDURE IF EXISTS VALIDATE_QUOTA_AND_EPISODELIMIT;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE VALIDATE_QUOTA_AND_EPISODELIMIT(IN_RECORDID BIGINT, IN_NEEDEDMINUTES INT, IN_BATCHSIZE INT, INOUT USERIDSTONOTIFY LONGTEXT, INOUT USER_PROCESSED_COUNT INT, IN_ENABLEREBROADCASTHAINDLING INT)
BEGIN
DECLARE VAR_LOCAL_USERIDS LONGTEXT;
DECLARE VAR_UPDATED_ROW_COUNT INT;
DECLARE VAR_NEEDEDSECONDS INT;

SET USER_PROCESSED_COUNT = 0;

IF(IN_ENABLEREBROADCASTHAINDLING = 1) THEN
    CALL DELETE_OLDEST_SERIES_RECORD_REBROADCAST_ENABLED(IN_RECORDID, IN_BATCHSIZE, USERIDSTONOTIFY);
ELSE
    CALL DELETE_OLDEST_SERIES_RECORD_REBROADCAST_DISABLED(IN_RECORDID, IN_BATCHSIZE, USERIDSTONOTIFY);
END IF;

CALL VALIDATE_NPVR_QUOTA_AND_DELETE_OLDEST_RECORD(IN_RECORDID, IN_NEEDEDMINUTES, IN_BATCHSIZE, USERIDSTONOTIFY, USER_PROCESSED_COUNT, IN_ENABLEREBROADCASTHAINDLING);

CALL UPDATE_STATUS_FOR_QUOTA_CHECK(IN_RECORDID, IN_BATCHSIZE, USER_PROCESSED_COUNT);

END $$
DELIMITER ;