const appRootDir = process.cwd(),
      path = require('path'),
      Sequelize = require('sequelize'),
      moment = require('moment'),
      util = require('util');
require('linqjs');

const EventEmitter = require('events').EventEmitter;

class PlayerDB {
    constructor({utils,config, logger}) {
        this.utils=utils;
        this.config=config;
        this.logger = logger;
        this.dbversion = '1.2';
        this._dbpath = path.join(appRootDir, 'signage.db');
        this._downloadStatusenum = {
            NotDownloaded: 0,
            Downloaded: 1,
            NotFound: 2,
            DownloadError: 3,
            Forbidden: 4,
            Unauthorized: 5
        };
        this._sequelize = new Sequelize('signage', '', '', {
            dialect: 'sqlite',
            storage: this._dbpath,
            logging: false,
            pool: {
                max: 1,//this is the trick, 1 connection allowed in pooling..
                min: 0,
                idle: 10000
            },
            freezeTableName: true
        });

        this.Announces = this._sequelize.define('Announces', {
            announceID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            MusicVolume: { type: Sequelize.INTEGER },
            Volume: { type: Sequelize.INTEGER },
            announceName: { type: Sequelize.STRING(150) },
            createdDate: { type: Sequelize.DATE },
            daysEffected: { type: Sequelize.INTEGER },
            delayTime: { type: Sequelize.INTEGER },
            deviceID: { type: Sequelize.INTEGER },
            endDate: { type: Sequelize.STRING(10) },
            isActive: { type: Sequelize.INTEGER },
            isClosingAnnounce: { type: Sequelize.INTEGER },
            isOpeningAnnounce: { type: Sequelize.INTEGER },
            lastUpdateDate: { type: Sequelize.STRING(19) },
            orderID: { type: Sequelize.INTEGER },
            startDate: { type: Sequelize.STRING(10) },
            startTime: { type: Sequelize.STRING(8) }
        }, { timestamps: false });
        this.Announces_Details = this._sequelize.define('Announces_Details', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            FileID: { type: Sequelize.INTEGER },
            FileName: { type: Sequelize.STRING(100) },
            OriginalName: { type: Sequelize.STRING(100) },
            FileSize: { type: Sequelize.INTEGER },
            announceID: { type: Sequelize.INTEGER },
            isDownloaded: { type: Sequelize.INTEGER },
            orderID: { type: Sequelize.INTEGER }
        }, { timestamps: false });
        this.DownloadExceptions = this._sequelize.define('DownloadExceptions', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            DownloadStartTime: { type: Sequelize.STRING(8) },
            DownloadEndTime: { type: Sequelize.STRING(8) },
            SpeedLimit: { type: Sequelize.INTEGER },
            deviceID: { type: Sequelize.INTEGER }
        }, { timestamps: false });
        this.GlobalTimeExceptions = this._sequelize.define('GlobalTimeExceptions', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            daynumber: { type: Sequelize.INTEGER },
            deviceID: { type: Sequelize.INTEGER },
            startTime: { type: Sequelize.STRING(8) },
            endTime: { type: Sequelize.STRING(8) }
        }, { timestamps: false });
        this.LastPlayedSongs = this._sequelize.define('LastPlayedSongs', {
            playlistID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            FileName: { type: Sequelize.STRING(100) }
        }, { timestamps: false });
        this.License = this._sequelize.define('License', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false, autoIncrement: true },
            licdata: { type: Sequelize.STRING(500) }
        }, { timestamps: false });
        this.Schedules = this._sequelize.define('Schedules', {
            scheduleID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            scheduleName: { type: Sequelize.STRING(150) },
            deviceID: { type: Sequelize.INTEGER },
            startDate: { type: Sequelize.STRING(10) },
            endDate: { type: Sequelize.STRING(10) },
            startTime: { type: Sequelize.STRING(8) },
            endTime: { type: Sequelize.STRING(8) },
            isLooping: { type: Sequelize.BOOLEAN },
            delayTime: { type: Sequelize.INTEGER },
            daysEffected: { type: Sequelize.INTEGER },
            createdDate: { type: Sequelize.DATE },
            isActive: { type: Sequelize.BOOLEAN },
            hasPriority: { type: Sequelize.BOOLEAN },
            orderID: { type: Sequelize.INTEGER },
            lastUpdateDate: { type: Sequelize.STRING(19) },
            Volume: { type: Sequelize.INTEGER },
            isShuffleEnabled: { type: Sequelize.BOOLEAN }
        }, { timestamps: false });
        this.Schedules_Details = this._sequelize.define('Schedules_Details', {
            scheduleID: { type: Sequelize.INTEGER },
            FileID: { type: Sequelize.INTEGER },
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            FileName: { type: Sequelize.STRING(100) },
            OriginalName: { type: Sequelize.STRING(100) },
            orderID: { type: Sequelize.INTEGER },
            FileSize: { type: Sequelize.INTEGER },
            isDownloaded: { type: Sequelize.INTEGER },


            Duration: { type: Sequelize.INTEGER },
            BPM: { type: Sequelize.INTEGER },
            KeyNote: { type: Sequelize.INTEGER }
        }, { timestamps: false });
        this.TimeExceptions = this._sequelize.define('TimeExceptions', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            deviceID: { type: Sequelize.INTEGER },
            endDate: { type: Sequelize.STRING(10) },
            endTime: { type: Sequelize.STRING(8) },
            startDate: { type: Sequelize.STRING(10) },
            startTime: { type: Sequelize.STRING(8) }
        }, { timestamps: false });
        this.Streams = this._sequelize.define('Streams', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            url: { type: Sequelize.STRING(300) },
            isActive: { type: Sequelize.BOOLEAN },
            deviceID: { type: Sequelize.INTEGER }
        }, { timestamps: false });
        this.Settings = this._sequelize.define('Settings', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false, autoIncrement: true },
            SettingName: { type: Sequelize.STRING(300) },
            SettingValue: { type: Sequelize.STRING(300) },
            deviceID: { type: Sequelize.INTEGER }
        }, { timestamps: false });
        this.CustomerKeys = this._sequelize.define('CustomerKeys', {
            customerID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            ID: { type: Sequelize.INTEGER },
            Name: { type: Sequelize.STRING(50) },
            AuthKey: { type: Sequelize.STRING(50) },
            createdDate: { type: Sequelize.STRING(20) }
        }, { timestamps: false });
        this.ThirdPartyFiles = this._sequelize.define('ThirdPartyFiles', {
            ID: { type: Sequelize.INTEGER, primaryKey: true, allowNull: false },
            FileName: { type: Sequelize.INTEGER },
            OriginalName: { type: Sequelize.STRING(100) },
            FileSize: { type: Sequelize.INTEGER },
            customerID: { type: Sequelize.INTEGER }
        }, { timestamp: false });
        this.Schedules.hasMany(this.Schedules_Details, { foreignKey: 'scheduleID', sourceKey: 'scheduleID' });
        this.Schedules_Details.belongsTo(this.Schedules, { foreignKey: 'scheduleID', targetKey: 'scheduleID' });

        this.Announces.hasMany(this.Announces_Details, { foreignKey: 'announceID', sourceKey: 'announceID' });
        this.Announces_Details.belongsTo(this.Announces, { foreignKey: 'announceID', targetKey: 'announceID' });

        this.ThirdPartyFiles.hasMany(this.CustomerKeys, { foreignKey: 'customerID', sourceKey: 'customerID' });
        this.CustomerKeys.belongsTo(this.ThirdPartyFiles, { foreignKey: 'customerID', sourceKey: 'customerID' });

        this.Schedules.removeAttribute('id');
        this.Announces.removeAttribute('id');
        this.Announces_Details.removeAttribute('id');
        this.Schedules_Details.removeAttribute('id');
        this.GlobalTimeExceptions.removeAttribute('id');
        this.TimeExceptions.removeAttribute('id');
        this.Streams.removeAttribute('id');
        this.Settings.removeAttribute('id');
        this.CustomerKeys.removeAttribute('id');
        this.ThirdPartyFiles.removeAttribute('id');



        //define db events

        this.GlobalTimeExceptions.afterBulkCreate((instances, options) => {
            let arr = instances.select(c => c.FileName).distinct();
            //console.log('afterBulkCreate event of GlobalTimeExceptions');
            this.logger.System('Global time exception bulk create');
            this.emit(this.eventTypes.GlobalTimeExceptions_bulkInsert, arr);
        });

        this.TimeExceptions.afterBulkCreate((instances, options) => {
            let arr = instances.select(c => c.FileName).distinct();
            //console.log('afterBulkCreate event of TimeExceptions');
            this.logger.System('Time exception bulk create');
            this.emit(this.eventTypes.TimeExceptions_bulkInsert, arr);
        });

        this.Schedules_Details.afterBulkCreate((instances, options) => {
            let arr = instances.select(c => c.FileName).distinct();
            //console.log('afterBulkCreate event of Schedules');
            this.emit(this.eventTypes.Schedules_Details_bulkInsert, arr);
        });

        this.Schedules_Details.afterBulkUpdate((options) => {
            //console.log('schedules details after bulk update event' + options.where.FileName)
            let isDownloaded = options.attributes.isDownloaded;
            let arr = [];
            arr.push({ FileName: options.where.FileName, isDownloaded: isDownloaded });
            this.emit(this.eventTypes.Schedules_Details_bulkUpdate, arr);
        });

        this.Schedules_Details.afterUpdate((options) => {
            //console.log('schedules details after bulk update event' + options.where.FileName)
            let isDownloaded = options.attributes.isDownloaded;
            let arr = [];
            arr.push({ FileName: options.where.FileName, isDownloaded: isDownloaded });
            this.emit(this.eventTypes.Schedules_Details_Update, arr);
        });

        this.Announces_Details.afterBulkCreate((instances, options) => {
            let arr = instances.select(c => c.FileName).distinct();
            //console.log('afterBulkCreate event of Announces_Details');
            this.logger.System('Announce Details bulk create');
            this.emit(this.eventTypes.Announces_Details_bulkInsert, arr);
        });

        this.Announces_Details.afterBulkUpdate((options) => {
            //console.log('Announces_Details after bulk update event' + options.where.FileName)
            this.logger.System('Announce Details bulk update');
            let isDownloaded = options.attributes.isDownloaded;
            let arr = [];
            arr.push({ FileName: options.where.FileName, isDownloaded: isDownloaded });
            this.emit(this.eventTypes.Announces_Details_bulkUpdate, arr);
        });

        this.Announces_Details.afterUpdate((options) => {
            let isDownloaded = options.attributes.isDownloaded;
            let arr = [];
            arr.push(({ FileName: options.where.FileName, isDownloaded: isDownloaded }));
            this.emit(this.eventTypes.Announces_Details_Update, arr);
        });



        this.eventTypes = {
            Schedules_Details_Changed: 'Schedules_Details_Changed',
            Announces_Details_bulkInsert: 'Announces_Details_bulkInsert',
            Schedules_Details_bulkInsert: 'Schedules_Details_bulkInsert',
            TimeExceptions_bulkInsert: 'TimeExceptions_bulkInsert',
            GlobalTimeExceptions_bulkInsert: 'GlobalTimeExceptions_bulkInsert',
            Announces_Details_bulkUpdate: 'Announces_Details_bulkUpdate',
            Schedules_Details_bulkUpdate: 'Schedules_Details_bulkUpdate',
            Schedules_Details_Update: 'Schedules_Details_Update',
            Announces_Details_Update: 'Announces_Details_Update',
            StreamAvailable: 'StreamAvailable',
            StreamUnavailable: 'StreamUnavailable',
            StreamUrlChanged: 'StreamUrlChanged',
            GlobalExInserted: 'GlobalExInserted',
            TimeExInserted: 'TimeExInserted',
            DownloadExInserted: 'DownloadExInserted'
        };
    }

    async initsync() {
        this.db = await this._sequelize.sync();
        this.logger.System('db synced');
        let q = (await  this.Settings.findAll({ where: { SettingName: 'version' } })).first();
        if (!q) {
            //set the db version
            let obj = { SettingName: 'version', SettingValue: this.dbversion, deviceID: this.config.Settings.deviceID };
            await this.Settings.create(obj);
            q = (await  this.Settings.findAll({ where: { SettingName: 'version' } })).first();
        }
        if (q.dataValues.SettingValue != this.dbversion) {
            //force db sync and reset all data
            this.db = await this._sequelize.sync({ force: true });
            await  this.initsync();
        }

        let v11 = await this._sequelize.query(`select count(*) as tableexists from
        ( SELECT name FROM sqlite_master WHERE type='view' AND name='todaysTimeExceptions')`, { type: this._sequelize.QueryTypes.SELECT });


        let v1 = await this._sequelize.query(`create view if not exists todaysTimeExceptions as
                select distinct
                 datetime(startDate || ' ' || startTime) as startTime,
                 datetime(endDate || ' ' || endTime) as endTime ,deviceID
                from
                TimeExceptions
                where
                datetime('now','localtime') between datetime(startDate || ' ' || startTime)  and datetime(endDate || ' ' || endTime)
                order by datetime(startDate || ' ' || startTime) limit 1`);

        let v2 = await this._sequelize.query(`create view if not exists todaysGlobalExceptions as
                select
                 datetime(strftime('%Y-%m-%d', date('now','localtime')) || ' ' || startTime) as startTime,
                 datetime(strftime('%Y-%m-%d', date('now','localtime')) || ' ' || endTime) as endTime,
                 daynumber ,deviceID
                from
                 GlobalTimeExceptions
                where
                 daynumber= strftime('%w',date('now'),'localtime') `);

        let v3 = await this._sequelize.query(`create view if not exists todaysExceptions as
                select
                (case  when  tstartTime is  null then gstartTime else tstartTime end ) as startTime ,
                (case  when  tendTime is  null then gendTime else tendTime end ) as endTime , deviceID
                from
                (
                select
                 t2.startTime as gstartTime,t2.endTime as gendTime,t1.startTime as tstartTime,t1.endTime as tendTime,t1.deviceID as deviceID
                 from
                 todaysGlobalExceptions  as t2
                 left outer join   todaysTimeExceptions as t1

                 on t1.deviceID=t2.deviceID
                 ) as t3`);

        let v4 = await this._sequelize.query(` create view if not exists  schedulesNow as
                select
                 strftime('%Y-%m-%d %H:%M:%S', startDate || ' ' || startTime ) as startTime,
                 strftime('%Y-%m-%d %H:%M:%S', endDate || ' ' || endTime ) as endTime ,
                 Schedules.scheduleID,Schedules.scheduleName,
                Schedules.daysEffected,
                Schedules.isActive,Schedules.hasPriority,Schedules.orderID,Schedules.isShuffleEnabled,Schedules.Volume,
                strftime('%Y-%m-%d %H:%M:%S',Schedules.lastUpdateDate) as lastUpdateDate

                from Schedules
                where  cast (Schedules.daysEffected & strftime('%w',date('now'),'localtime') as integer) ==cast( strftime('%w',date('now'),'localtime') as integer)
                and  ( datetime('now','localtime') between strftime('%Y-%m-%d %H:%M:%S', startDate || ' ' || startTime )   and strftime('%Y-%m-%d %H:%M:%S', endDate || ' ' || endTime ) )
                and   ( datetime('now','localtime') between  datetime( strftime('%Y-%m-%d', date('now','localtime')) || ' ' || startTime) and datetime( strftime('%Y-%m-%d', date('now','localtime')) || ' ' || endTime) )

                order by  datetime( strftime('%Y-%m-%d', date('now','localtime')) || ' ' || startTime),datetime( strftime('%Y-%m-%d', date('now','localtime')) || ' ' || endTime)
                `);

        let v5 = await this._sequelize.query(`create view if not exists schedulesNowWithExceptions as
                select
                case when (strftime('%s',scheduleStart)-strftime('%s',expStart))>0 then scheduleStart else expStart end as startTime,
                case when (strftime('%s',expEnd)-strftime('%s',scheduleend))>0 then scheduleEnd else expEnd end as endTime,
                scheduleName,hasPriority,isShuffleEnabled,scheduleID

                from
                (
                select
                 datetime( date('now','localtime') || ' ' || strftime('%H:%M:%S',schedulesNow.startTime)) as scheduleStart,
                 datetime( date('now','localtime') || ' ' || strftime('%H:%M:%S',schedulesNow.endTime)) as scheduleEnd,
                 datetime( date('now','localtime') || ' ' || strftime('%H:%M:%S',todaysExceptions.startTime)) as expStart,
                 datetime( date('now','localtime') || ' ' || strftime('%H:%M:%S',todaysExceptions.endTime)) as expEnd,
                 schedulename,dayseffected,isActive,hasPriority,orderID,isShuffleEnabled,scheduleID
                from todaysExceptions, schedulesNow where isActive=1
                ) `);

        let v6 = await this._sequelize.query(`  create view if not exists  schedulesNowWithFiles as
                select
                startTime,endTime,scheduleName,hasPriority,isShuffleEnabled,t2.scheduleID,FileID,FileName,OriginalName,orderID,FileSize,isDownloaded
                 from

                (select distinct mstartTime as startTime,mendTime as endTime,scheduleName,hasPriority,isShuffleEnabled,scheduleID from
                (
                select min(startTime) as mstartTime ,max(endTime) mendTime from schedulesNowWithExceptions ) as t1,
                schedulesNowWithExceptions) as t2,Schedules_Details where t2.scheduleID=Schedules_Details.scheduleID
                order by orderID`);

        this.schedulesNowWithFiles = this._sequelize.define('schedulesNowWithFiles', {
            startTime: { type: Sequelize.STRING(19) },
            endTime: { type: Sequelize.STRING(19) },
            scheduleName: { type: Sequelize.STRING(150) },
            hasPriority: { type: Sequelize.BOOLEAN },
            isShuffleEnabled: { type: Sequelize.BOOLEAN },
            scheduleID: { type: Sequelize.INTEGER },
            FileID: { type: Sequelize.INTEGER },
            FileName: { type: Sequelize.STRING(100) },
            OriginalName: { type: Sequelize.STRING(100) },
            orderID: { type: Sequelize.INTEGER },
            FileSize: { type: Sequelize.INTEGER },
            isDownloaded: { type: Sequelize.BOOLEAN }
        }, { timestamps: false, tableName: 'schedulesNowWithFiles' });

        this.schedulesNow = this._sequelize.define('schedulesNow', {
            startTime: { type: Sequelize.STRING(19) },
            endTime: { type: Sequelize.STRING(19) },
            scheduleID: { type: Sequelize.INTEGER },
            scheduleName: { type: Sequelize.STRING(150) },
            daysEffected: { type: Sequelize.INTEGER },
            isActive: { type: Sequelize.BOOLEAN },
            hasPriority: { type: Sequelize.BOOLEAN },
            orderID: { type: Sequelize.INTEGER },
            isShuffleEnabled: { type: Sequelize.BOOLEAN },
            Volume: { type: Sequelize.INTEGER },
            lastUpdateDate: { type: Sequelize.STRING(19) }
        }, { timestamps: false, tableName: 'schedulesNow' });

        this.schedulesNowWithExceptions = this._sequelize.define('schedulesNowWithExceptions', {
            startTime: { type: Sequelize.STRING(19) },
            endTime: { type: Sequelize.STRING(19) },
            schedulename: { type: Sequelize.STRING(150) },
            hasPriority: { type: Sequelize.BOOLEAN },
            isShuffleEnabled: { type: Sequelize.BOOLEAN },
            scheduleID: { type: Sequelize.INTEGER }
        }, { timestamps: false, tableName: 'schedulesNowWithExceptions' });

        this.todaysExceptions = this._sequelize.define('todaysExceptions', {
            startTime: { type: Sequelize.STRING(19) },
            endTime: { type: Sequelize.STRING(19) },
            deviceID: { type: Sequelize.INTEGER }
        }, { timestamps: false, tableName: 'todaysExceptions' });

        this.todaysGlobalExceptions = this._sequelize.define('todaysGlobalExceptions', {
            startTime: { type: Sequelize.STRING(19) },
            endTime: { type: Sequelize.STRING(19) },
            daynumber: { type: Sequelize.INTEGER },
            deviceID: { type: Sequelize.INTEGER }
        }, { timestamps: false, tableName: 'todaysGlobalExceptions' });

        this.todaysTimeExceptions = this._sequelize.define('todaysTimeExceptions', {
            startTime: { type: Sequelize.STRING(19) },
            endTime: { type: Sequelize.STRING(19) },
            deviceID: { type: Sequelize.INTEGER }
        }, { timestamps: false, tableName: 'todaysTimeExceptions' });

        this.todaysTimeExceptions.removeAttribute('id');
        this.todaysGlobalExceptions.removeAttribute('id');
        this.todaysExceptions.removeAttribute('id');
        this.schedulesNowWithExceptions.removeAttribute('id');
        this.schedulesNow.removeAttribute('id');
        this.schedulesNowWithFiles.removeAttribute('id');

        // let q1 = (await this.schedulesNow.findAll()).select(c => c.dataValues);

    }

    // ---------- v8.7.9 queries ----------

    async getDownloadedScheduleCount() {
        const downloaded = await this._sequelize.query(`select count(*) as Downloaded,scheduleID from Schedules_Details where isDownloaded=1 group by scheduleID`, { type: this._sequelize.QueryTypes.SELECT });
        if(downloaded.length > 0) {
            return downloaded;
        }
        return [];
    }

    async getDownloadedScheduleFilesID() {
        const downloaded = await this._sequelize.query(`select FileID from Schedules_Details where isDownloaded=1 group by  FileID`, {type: this._sequelize.QueryTypes.SELECT});
        if(downloaded.length > 0) {
            return downloaded;
        }
        return [];
    }

    async getDownloadedAnnounceCount() {
        const downloaded = await this._sequelize.query(`select  FileID from Announces_Details where isDownloaded=1 group by FileID`, { type: this._sequelize.QueryTypes.SELECT });
        if(downloaded.length > 0) {
            return downloaded;
        }
        return [];
    }

    async getDownloadedWithDetails() {
        const schedules = await this.getDownloadedScheduleFilesID();
        const announces = await this.getDownloadedAnnounceCount();
        let all = [];
        if(schedules.length > 0) {
            let q  = schedules.select(c => c.FileID);
            all.push(q);
        }
        if(announces.length > 0) {
            let q = announces.select(c => c.FileID);

            all.push(q);
        }
        return all;
    }

    // ---------- v8.7.9 queries ----------

    //------ Time based exceptions processed here ----------------
    async InsertExceptions(data) {
        this.logger.SyncConnection('we have new Exceptions received from server.. starting processing.. ');

        await this._processDownloadExceptions(data);

        await this._processTimeExceptions(data);

        await this._processGlobalExceptions(data);

    }

    async _processGlobalExceptions(data) {
        if (data.globalExceptions) {
            let texOnServer = await data.globalExceptions;
            //get data from database
            let texOnDB = (await this.GlobalTimeExceptions.findAll()).select((i) => i.dataValues);
            if (texOnDB) {
                this.logger.SyncConnection('we have globalExceptions data on db, now lets compare it with server');

                if (await this._compareGlobalExceptions(texOnServer, texOnDB)) {
                    this.logger.SyncConnection('it seems we both have same globalExceptions data,skiping..');
                }
                else {
                    //data is different,
                    //first destroy old data
                    await this.GlobalTimeExceptions.destroy({ where: { deviceID: { gt: 0 } } });
                    //insert time exceptions
                    this.logger.SyncConnection('we have new globalExceptions received from server,inserting');
                    await  this.GlobalTimeExceptions.bulkCreate(texOnServer);
                    this.logger.SyncConnection('globalExceptions inserted');

                    // ---------- v8.7.9 events ----------
                    this.emit(this.eventTypes.GlobalExInserted);
                    // ---------- v8.7.9 events ----------
                }

            } else {
                this.logger.SyncConnection('we dont have any globalExceptions data on db, inserting..');
                //first destroy old
                await  this.GlobalTimeExceptions.bulkCreate(texOnServer);
                // ---------- v8.7.9 events ----------
                this.emit(this.eventTypes.GlobalExInserted);
                // ---------- v8.7.9 events ----------
                this.logger.SyncConnection('Time Exceptions inserted');
            }

        } else {
            //destroy data, because we dont have timeexceptions anymore
            this.logger.SyncConnection('destroy globalExceptions on db , server returns no TimeExceptions');
            await this.GlobalTimeExceptions.destroy({ where: { deviceID: { gt: 0 } } });
        }

    }

    async _processTimeExceptions(data) {
        if (data.TimeExceptions) {
            let texOnServer = data.TimeExceptions;
            texOnServer.forEach((i) => {
                i.startDate = i.startDate.substr(0, 10);
                i.endDate = i.endDate.substr(0, 10);
            });
            let texOnDB = (await this.TimeExceptions.findAll()).select((i) => i.dataValues);
            if (texOnDB) {
                this.logger.SyncConnection('we have TimeExceptions data on db, now lets compare it with server');
                if (await this._compareTimeExceptions(texOnServer, texOnDB)) {
                    this.logger.SyncConnection('it seems we both have same TimeExceptions data,skiping..');
                }
                else {
                    //data is different,
                    //first destroy old data
                    await this.TimeExceptions.destroy({ where: { deviceID: { gt: 0 } } });
                    //insert time exceptions
                    this.logger.SyncConnection('we have new time exceptions received from server,inserting');
                    await  this.TimeExceptions.bulkCreate(texOnServer);
                    // ---------- v8.7.9 events ----------
                    this.emit(this.eventTypes.TimeExInsterted);
                    // ---------- v8.7.9 events ----------
                    this.logger.SyncConnection('Time Exceptions inserted');
                }

            } else {
                this.logger.SyncConnection('we dont have any TimeExceptions data on db, inserting..');
                //first destroy old
                await  this.TimeExceptions.bulkCreate(texOnServer);
                this.logger.SyncConnection('Time Exceptions inserted');

                // ---------- v8.7.9 events ----------
                this.emit(this.eventTypes.TimeExInsterted);
                // ---------- v8.7.9 events ----------
            }

        } else {
            //destroy data, because we dont have timeexceptions anymore
            this.logger.SyncConnection('destroy Time Exceptions on db , server returns no TimeExceptions');
            await this.TimeExceptions.destroy({ where: { deviceID: { gt: 0 } } });
        }
    }

    async _processDownloadExceptions(data) {

        let dexception = {
            ID: 1,
            DownloadStartTime: data.downloadStartTime,
            DownloadEndTime: data.downloadEndTime,
            SpeedLimit: data.SpeedLimit,
            deviceID: data.globalExceptions[ 0 ].deviceID
        };

        let dexOnDB = await this.DownloadExceptions.findAll({ limit: 1 });
        //get db values
        let DownloadExceptions = null;
        if (dexOnDB.length > 0) DownloadExceptions = dexOnDB.first().dataValues;
        //check if db values are null or there is a change?
        if (DownloadExceptions == null || (DownloadExceptions.SpeedLimit != dexception.SpeedLimit ||
            DownloadExceptions.DownloadStartTime != dexception.DownloadStartTime ||
            DownloadExceptions.DownloadEndTime != dexception.DownloadEndTime ||
            DownloadExceptions.deviceID != dexception.deviceID)
        ) {
            this.logger.SyncConnection('server and local DownloadExceptions are different, writing server variables to db' + JSON.stringify(dexception));
            //clear timeExceptions
            this.logger.SyncConnection('deleting old DownloadExceptions from db');
            await this.DownloadExceptions.destroy({ where: { deviceID: { gt: 0 } } });
            //insert the new data
            this.logger.SyncConnection('inserting new DownloadExceptions');
            await this.DownloadExceptions.create(dexception);
            this.logger.SyncConnection('new DownloadExceptions inserted');

            // ---------- v8.7.9 events ----------
            this.emit(this.eventTypes.DownloadExInserted);

            // ---------- v8.7.9 events ----------
        } else {
            this.logger.SyncConnection('server and local DownloadExceptions are same, skipping insert.');
        }

    }

    async _compareGlobalExceptions(srv, db) {
        if (srv.length != db.length) return false;

        let o1 = srv, o2 = db;
        for(let i = 0; i < db.length; i++) {
            if (!(
                o1[ i ].ID == o2[ i ].ID &&
                o1[ i ].daynumber === o2[ i ].daynumber &&
                o1[ i ].deviceID === o2[ i ].deviceID &&
                o1[ i ].startTime === o2[ i ].startTime &&
                o1[ i ].endTime === o2[ i ].endTime)
            ) {
                return false;
            }
        }
        return true;
    }

    async _compareTimeExceptions(srv, db) {
        if (srv.length != db.length) return false;
        let o1 = srv, o2 = db;
        for(let i = 0; i < db.length; i++) {
            if (!(
                o1[ i ].ID == o2[ i ].ID &&
                o1[ i ].deviceID === o2[ i ].deviceID &&
                o1[ i ].startTime === o2[ i ].startTime &&
                o1[ i ].endTime === o2[ i ].endTime &&
                o1[ i ].startDate === o2[ i ].startDate &&
                o1[ i ].endDate === o2[ i ].endDate)
            ) {
                return false;
            }
        }
        return true;
    }

    async getTodaysTimeException() {
        //we store date and time datas as string on db..
        //when we get them back, we convert them to a moment date
        let nowdate = moment().utc(true);
        let nowstr = nowdate.toJSON().substr(0, 11);
        let q = (await this.TimeExceptions.findAll()).select((i) => i.dataValues).where((i) => i.deviceID == this.config.Settings.deviceID).select((c) => {
            return {
                startTime: moment(nowstr + c.startTime).utc(true),
                endTime: moment(nowstr + c.endTime).utc(true),
                startDate: moment(c.startDate + 'T00:00:01').utc(true),
                endDate: moment(c.endDate + 'T23:59:59').utc(true),

            };
        });
        //if there's a time ex just for today return this
        let q2 = q.where(c => nowdate.isSame(c.startDate, 'day'));
        if(q2.length > 0) {
            return q2[0];
        }
        //if there are 2 or more time exceptions for one day, return this
        let q1 = q.where((c) => nowdate.isBetween(c.startDate, c.endDate, null, '[]') && c.startTime.isAfter(nowdate)).first();
        if(q1) {
            return q1;
        }
        //if there's an array of time exceptions return this.
        let q3 = q.where((c) => nowdate.isBetween(c.startDate, c.endDate, null, '[]'));
        if(q3){
            return q3[0];
        }
    }

    async getTimeExceptions() {
        let now = moment().utc(true);
        let nowJson = now.toJSON().substr(0, 11);
        let q = (await this.TimeExceptions.findAll()).select((i) => i.dataValues).where((i) => i.deviceID == this.config.Settings.deviceID).select((c) => {
            return {
                ID: c.ID,
                startTime: moment(nowJson + c.startTime).utc(true),
                endTime: moment(nowJson + c.endTime).utc(true),
                startDate: moment(c.startDate + 'T00:00:01').utc(true),
                endDate: moment(c.endDate + 'T23:59:59').utc(true)
            };
        });
        return q;
    }

    async getTodaysGlobalException() {

        let nowdate = moment().utc(true);
        //we need the daynumber, sunday is 0, monday is 1 , etc...
        let today = nowdate.weekday();
        let now = nowdate.toJSON().substr(0, 11);//including 'T'
        let q1 = (await this.GlobalTimeExceptions.findAll()).select((i) => i.dataValues).where((i) => i.daynumber == today && i.deviceID == this.config.Settings.deviceID)
            let q=q1.select((i) => {
            return {
                startTime: moment(now + i.startTime).utc(true),
                endTime: moment(now + i.endTime).utc(true),
                daynumber: i.daynumber

            };
        }).first();
        return q;
    }

    async getGlobalExceptions() {
        let now = moment().utc(true);
        let nowJson = now.toJSON().substr(0, 11);
        let q = (await this.GlobalTimeExceptions.findAll()).select((i) => i.dataValues).where((i) => i.deviceID == this.config.Settings.deviceID).select((i) => {

            return {
                ID: i.ID,
                daynumber: i.daynumber,
                startTime: moment(nowJson + i.startTime).utc(true),
                endTime: moment(nowJson + i.endTime).utc(true),
                startDate: moment(nowJson + '00:00:01').utc(true),
                endDate: moment(now.add(5, 'years').toJSON().substr(0, 10) + 'T23:59:59').utc(true)
            };

        });
        return q;
    }

    async getAvailableDownloadTime() {
      let now = moment().utc(true);
      let nowJson = now.toJSON().substr(0, 11);
      let q = (await this.DownloadExceptions.findAll({where: { deviceID: this.config.Settings.deviceID}})).select((c) => c.dataValues).select((c) => {
            return {
              deviceID: c.deviceID,
              startTime: moment(nowJson + c.DownloadStartTime).utc(true),
              endTime: moment(nowJson + c.DownloadEndTime).utc(true),
              speedLimit: c.SpeedLimit,
            };
          });
      return q;
    }

    async getAvailablePlayTime() {
        //if we have special TimeException, first return that
        let timeExceptions = await this.getTodaysTimeException();
        if (timeExceptions) return timeExceptions;

        //else, check global exception
        let global =  await this.getTodaysGlobalException();
        return global;
    }

    IsNowOkForPlayback(startTime, endTime) {
        let nowdate = moment().utc(true);
        return nowdate.isBetween(startTime, endTime, null, '[]');
    }

    //------/ Time based exceptions processed here ----------------

    //------------- Stream Url Process ---------------
    async InsertStreamUrl(data) {
        this.logger.StreamUrl('processing new stream url request :' + JSON.stringify(data));
        if(data==null || data.Url == null) {
          this.emit(this.eventTypes.StreamUnavailable);
        }
        let q = (await this.Streams.findAll()).first();
        if (q == null && data == null) {
            this.logger.StreamUrl('StreamUrl, no need to process anything.. ');
            this.emit(this.eventTypes.StreamUnavailable);
            return;
        }
        if (q && data == null) {
            //this means server cleared the stream but we have on db

            this.logger.StreamUrl('we have stream url in db but it is disabled on server');
            this.emit(this.eventTypes.StreamUnavailable);
            await this.Streams.destroy({ where: { ID: { gt: 0 } } });
            return;
        }

        if (q && data.Enabled) {
            q = q.dataValues;
            if (q.isActive && q.deviceID == data.deviceID && q.url == data.Url) {
                this.logger.StreamUrl('StreamUrl,server and local db are same, skipping..');
                return;
            }
        }

        this.logger.StreamUrl('deleting old stream url data from db');
        await this.Streams.destroy({ where: { ID: { gt: 0 } } });

        if (data.Enabled) {
            this.logger.StreamUrl('inserting new stream url data to db');
            let obj = { ID: 1, url: data.Url, isActive: data.Enabled, deviceID: data.deviceID };
            await this.Streams.create(obj);
            this.emit(this.eventTypes.StreamUrlChanged, data.Url);
            this.emit(this.eventTypes.StreamAvailable);
        }

    }

 async checkIfStreamAvailable() {
        try {
            // Query the Streams table for any active stream
            let stream = (await this.Streams.findAll()).first();
            console.log('------------------------------------');
            console.log('Stream:', stream.dataValues);
            console.log('------------------------------------');
            var url = stream.dataValues.url;
            // If we found an active stream
            if (stream && stream.dataValues) {
                console.log('found stream:', url)
                this.emit(this.eventTypes.StreamUrlChanged, url);
                this.emit(this.eventTypes.StreamAvailable);
                return url;
            }
            else {
                console.log('no stream found')
                // If no active stream found, emit StreamUnavailable
                this.emit(this.eventTypes.StreamUnavailable);
                return null;
            }
        } catch (error) {
            console.log('error checking stream availability:', error);
            this.logger.StreamUrl('Error checking stream availability: ' + error);
            this.emit(this.eventTypes.StreamUnavailable);
            return null;
        }
    }
    //-------------// Stream Url Process ---------------

}

util.inherits(PlayerDB, EventEmitter);
module.exports = PlayerDB;
