"use strict";

const logger = require('komodo-sdk/logger');
const sqlite3 = require('sqlite3').verbose();
const moment = require('moment');
const cron = require('cron');

const db = new sqlite3.Database('data.sqlite', (err) => {
    if (err) {
        logger.warn('Error connecting to local database', {err: err});
        process.exit(2);
    }

    logger.info('Local database connected');

    initTables();
    cleanup();

});

function getConnection() { return db; }

function initTables() {
    logger.verbose('Preparing database tables');
    db.serialize(function() {
        db.run('CREATE TABLE IF NOT EXISTS ussd( created TEXT, created_date TEXT, direction TEXT, imsi TEXT, msg TEXT )');
        db.run('CREATE INDEX IF NOT EXISTS idx_ussd_created ON ussd(created)');
        db.run('CREATE INDEX IF NOT EXISTS idx_ussd_created_date_created ON ussd(created_date, created)');

        db.run('CREATE TABLE IF NOT EXISTS sms( created TEXT, created_date TEXT, direction TEXT, imsi TEXT, partner TEXT, msg TEXT )');
        db.run('CREATE INDEX IF NOT EXISTS idx_sms_created ON sms(created)');
        db.run('CREATE INDEX IF NOT EXISTS idx_sms_created_date_created ON sms(created_date, created)');

        db.run('CREATE TABLE IF NOT EXISTS pendingtrx ( created TEXT, created_date TEXT, trx_id TEXT, destination TEXT, product TEXT )');
        db.run('CREATE INDEX IF NOT EXISTS idx_pendingtrx_created ON pendingtrx(created)');
        db.run('CREATE UNIQUE INDEX IF NOT EXISTS idx_pendingtrx_trx_id ON pendingtrx(trx_id)');
        db.run('CREATE INDEX IF NOT EXISTS idx_pendingtrx_created_date ON pendingtrx(created_date, destination, product)');
    })
}

function cleanup(max_days) {
    logger.info('Doing database cleanup');

    if (!max_days) {
        max_days = 31;
    }

    const oldest = moment().add(-1 * max_days, 'days').format('YYYY-MM-DD');
    db.serialize(function() {
        db.run('DELETE FROM ussd WHERE created < ?', oldest);
        db.run('DELETE FROM sms WHERE created < ?', oldest);
        db.run('DELETE FROM pendingtrx WHERE created_date < ?', oldest);
        db.run('VACUUM');
    });
}

const cleanCronJob = new cron.CronJob({
    cronTime: '00 00 00 * * *',
    onTick: function() { cleanup(); },
    start: false
})

exports.getConnection = getConnection;
exports.cleanup = cleanup;