"use strict";

const   pg              = require('pg'),
        EventEmitter    = require('events').EventEmitter,
        EE              = new EventEmitter,
        connect         = 'pg://postgres:9385411q@localhost:5432/',
        config          = {
        users:{ id:'SERIAL NOT NULL PRIMARY KEY',
            owner:'INT REFERENCES users',
            token:'VARCHAR(200)',
            captcha_key: 'VARCHAR(32)',
            ban: 'BOOLEAN NOT NULL DEFAULT FALSE',
            access: 'BIGINT DEFAULT 0',
            limit_ok: 'INT DEFAULT 0',
            limit_time: 'BIGINT DEFAULT 0',
            phone: 'BIGINT',
            send: 'INT DEFAULT 0',
            key: 'UUID DEFAULT gen_random_uuid()',
            captcha_balance: 'REAL DEFAULT 0',
            error: 'json',
            app:'INT',
            online: 'BOOLEAN NOT NULL DEFAULT FALSE'
            },
        bids:{id:'SERIAL NOT NULL PRIMARY KEY',
            aid:'INT REFERENCES users',
            cid:'INT',
            eid:'INT',
            maid:'INT[]',
            country:'INT',
            cities:'INT[]',
            sex:'INT DEFAULT 0',
            age_from:'INT',
            age_to:'INT',
            last_seen:'INT',
            count:'INT',
            status:'INT DEFAULT 0',
            go:'INT DEFAULT 0',
            date:'BIGINT DEFAULT extract(epoch from now())*1000',
            count_denied:'INT DEFAULT 0',
            count_already:'INT DEFAULT 0',
            count_ok:'INT DEFAULT 0',
            count_captcha:'INT DEFAULT 0',
            count_ok_having:'INT DEFAULT 0',
            from_having:'INT DEFAULT 0',
            error: 'json'
        },
        tasks:{ id:'SERIAL NOT NULL PRIMARY KEY',
            bid:'INT REFERENCES bids',
            uid:'INT',
            state:'INT DEFAULT 0',
            state_data:'BIGINT DEFAULT 0',
            aid:'INT REFERENCES users',
            info:'json'
        }
        /*money_y:{
            id:'SERIAL NOT NULL PRIMARY KEY',
            sender:'VARCHAR(100)',
            date:'BIGINT DEFAULT extract(epoch from now())*1000',
            amount:'REAL DEFAULT 0',
            operation_id:'VARCHAR(100)',
            currency:'VARCHAR(3)'
        }*/
};

var pool;
var listen_client;

function init_data_base() {

    var client = new pg.Client(connect+'postgres');
    client.connect();
    client.query('CREATE DATABASE vktoken', function (err, res) {

        if (err &&err.code != '42P04')
            console.error(err);
        else if (res)
            console.log(res);

        client.end();

        EE.emit('init_data_base');
    });

}

function init_table() {

    var client = new pg.Client(connect+'vktoken');
    client.connect();

    var k = 0;

    for (var T in config) {
        k++;

        client.query('CREATE TABLE '+T+'()', function (err, res) {
            k--;
            if (err && err.code != '42P07')
                console.error(err);
            else if (res)
                console.log(res);
        });

        for (var C in config[T]) {
            k++;

            client.query('ALTER TABLE '+T+' ADD COLUMN '+C+' '+config[T][C]+';', function (err, res) {
                k--;
                if (err && err.code != '42701')
                    console.error(err);
                else if (res)
                    console.log(res);

                if (!k)
                    EE.emit('init_table');

            });
        }
    }
}

function init_pool(max_pool) {

    var config = {
        user: 'postgres',
        database: 'vktoken',
        password: '9385411q',
        port: 5432,
        max: max_pool,
        min: 1,
        idleTimeoutMillis: 10000
    };

    var Pool = require('pg').Pool;
    pool = new Pool(config);

    EE.emit('init_pool');
}

function init_listen(lisen) {

    if(lisen){
        listen_client = new pg.Client(connect+'vktoken');
        listen_client.connect();
        listen_client.on('notification', function(msg) {
            EE.emit(msg.channel, msg.payload);
        });
    }

    EE.emit('init_listen');
}

function cb_db(err, res, cb) {

    if (typeof(cb) == 'function')
        if(err)
            cb(err);
        else
            cb(res);
}

// metods----------------------------------------
var user = {
    add_new                 :function (id, cb) {
        pool.query('INSERT INTO users(id, owner) VALUES($1,$1)', [id], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    get_json                :function (id, cb) {
        pool.query('SELECT array_to_json(ARRAY(SELECT row_to_json(users) FROM users ' +
            'WHERE owner = $1 OR id = $1)) AS users', [id], function (err, res) {
            cb(res);
        });
    },
    get                     :function (maid, cb) {
        pool.query('SELECT temp.id, temp.token, temp.owner, users.captcha_key, true AS on FROM users AS temp ' +
            'JOIN users ON (temp.owner = users.id) WHERE temp.id = ANY ($1)', [maid], function (err, res) {
            cb(res);
        });
    },
    get_id_form_key         :function (key, cb) {
        pool.query('SELECT id FROM users WHERE key = $1', [key], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    get_key_form_id         :function (id, cb) {
        pool.query('SELECT key, ban FROM users WHERE id = $1', [id], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    get_all_do_continue     :function (cb) {
        pool.query('SELECT limit_time, id FROM users '+
            'WHERE limit_time <> 0 AND access > extract(epoch from now())*1000 ', function (err, res) {
            cb(res);
        });
    },
    get_captcha_key         :function (id, cb) {
        pool.query('SELECT captcha_key FROM users WHERE id = $1', [id], function (err, res) {
            cb(res.rows[0].captcha_key);
        });
    },
    update_token            :function (id, token, cb) {
        pool.query('UPDATE users ' +
            'SET token = $2, error = null ' +
            'WHERE users.id = $1', [id, token], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    update_phone            :function (id, phone, cb) {
        pool.query('UPDATE users ' +
            'SET phone = $2 ' +
            'WHERE users.id = $1', [id, phone], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    update_app              :function (id, app, cb) {
        pool.query('UPDATE users SET app = $2 ' +
            'WHERE id = $1', [id, app]);
    },
    update_captcha_balance  :function (id, balance) {
        pool.query('UPDATE users ' +
            'SET captcha_balance = $2 ' +
            'WHERE id = $1', [id, balance], function (err, res) {
            cb_db(err, res);
        });
    },
    update_access           :function (id, access) {
        pool.query('UPDATE users ' +
            'SET access = $2, error = null ' +
            'WHERE id = $1', [id, access]);
    },
    update_captcha          :function (id, key, balance) {
        pool.query('UPDATE users ' +
            'SET captcha_key = $2, captcha_balance = $3 ' +
            'WHERE users.id = $1', [id, key, balance]);
    },
    set_error               :function (id, err) {
        pool.query('UPDATE users SET error = $2 WHERE id = $1', [id, err]);
    },
    set_owner               :function (id, owner, cb) {
        pool.query('UPDATE users ' +
            'SET owner = $2 '+
            'WHERE users.id = $1', [id, owner], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    set_real_limit          :function (id) {
        pool.query('UPDATE users SET limit_time = 0, limit_ok = (SELECT count(*) FROM tasks ' +
            'JOIN bids ON (tasks.bid = bids.id ' +
            'AND $1 = ANY(bids.maid) ' +
            'AND tasks.state = 1 ' +
            'AND tasks.aid = $1 ' +
            'AND tasks.state_data >= extract(epoch from now())*1000 - 13*60*60*1000)) ' +
            'WHERE users.id = $1', [id], function (err, res) {
            cb_db(err, res);
        });
    },
    update_real_limit       :function (id) {
        pool.query('UPDATE users SET limit_ok = (SELECT count(*) FROM tasks ' +
            'JOIN bids ON (tasks.bid = bids.id ' +
            'AND $1 = ANY(bids.maid) ' +
            'AND tasks.state = 1 ' +
            'AND tasks.aid = $1 ' +
            'AND tasks.state_data >= extract(epoch from now())*1000 - 13*60*60*1000)) ' +
            'WHERE users.id = $1', [id], function (err, res) {
            cb_db(err, res);
        });
    },
    set_online              :function (id, online) {
        pool.query('UPDATE users SET online = $2 WHERE id = $1', [id, online]);
    },
    set_online_default      :function () {
        pool.query('UPDATE users SET online = false WHERE online = true');
    },
    limit_ok_inc            :function (id) {
        pool.query('UPDATE users SET limit_ok = limit_ok + 1 WHERE id = $1', [id]);
    },
    continuation            :function (id) {
        pool.query('SELECT continuation($1)', [id]);
    }
};

var bid  = {
    add_json                :function (aid, v, cb) {
        var maid = v.maid,
            tasks = v.tasks,
            cities = v.cities===null?null:'{'+String(v.cities)+'}';

        delete v.tasks;
        delete v.maid;
        delete v.cities;
        v.aid = aid;

        pool.query('INSERT INTO bids(maid,aid,cid,eid,country,cities,sex,age_from,age_to,count,last_seen) ' +
            'SELECT $2 AS maid,aid,cid,eid,country,$3 AS cities,sex,age_from,age_to,count,last_seen ' +
            'FROM json_populate_record(null::bids, $1) RETURNING id', [JSON.stringify(v), '{'+String(maid)+'}', cities], function (err, res) {

            if (res.rowCount&&tasks) {
                var bid = res.rows[0].id;
                task.add_uids(bid, tasks, function () {
                    bid.start_invite(bid);
                });
            }

            cb_db(err, res, cb);
        });
    },
    count_ok_inc            :function (id) {
        pool.query('UPDATE bids SET count_ok = count_ok+1 WHERE id = $1', [id]);
    },
    count_denied_inc        :function (id) {
        pool.query('UPDATE bids SET count_denied = count_denied+1 WHERE id = $1', [id]);
    },
    count_captcha_inc       :function (id) {
        pool.query('UPDATE bids SET count_captcha = count_captcha+1 WHERE id = $1', [id]);
    },
    count_already_inc       :function (id) {
        pool.query('UPDATE bids SET count_already = count_already+1 WHERE id = $1', [id]);
    },
    get_json                :function (id, cb){
        pool.query('SELECT array_to_json(ARRAY( SELECT row_to_json(bids) FROM bids ' +
            'INNER JOIN users ON bids.aid = users.id ' +
            'WHERE (users.owner = $1 OR users.id = $1) AND bids.status <> 5 ' +
            'ORDER BY bids.id DESC)) AS bids', [id], function (err, res) {
            cb_db(err,res,cb);
        });
    },
    get_all_do_task         :function (cb) {
        pool.query('SELECT row_to_json(bids) AS bid FROM bids '+
            'WHERE go = 0 AND status = 1', function (err, res) {
            cb_db(err,res,cb);
        });
    },
    get_all_do_invite       :function (cb) {
        pool.query('SELECT row_to_json(bids) AS bid FROM bids '+
            'WHERE go = 1 AND status = 1', function (err, res) {
            cb_db(err,res,cb);
        });
    },
    update_status           :function (id, aid, status, from_having, cb) {
        pool.query('UPDATE bids ' +
            'SET status = $3, from_having = COALESCE($4, from_having) ' +
            'WHERE bids.id = $1 AND bids.aid = $2', [id, aid, status, from_having], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    update_maid             :function (id, maid) {
        pool.query('UPDATE bids SET maid = $2 WHERE id = $1', [id, '{'+String(maid)+'}']);
    },
    set_status_sys          :function (bid, status, err, cb) {
        pool.query('UPDATE bids ' +
            'SET status = $2, error = $3' +
            'WHERE bids.id = $1', [bid, status, err], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    start_invite            :function (bid, cb) {
        pool.query('UPDATE bids ' +
            'SET status = 0, go = 1, from_having = 0, ' +
            'count = (SELECT count(*) FROM tasks WHERE bid = $1), ' +
            'count_ok_having = (SELECT count(DISTINCT t1.uid) AS count_ok_having FROM tasks AS t1 ' +
            'JOIN tasks ON (t1.uid = tasks.uid AND tasks.state = 1) ' +
            'WHERE t1.bid = $1) ' +
            'WHERE id = $1', [bid], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    set_error               :function (id, err) {
        pool.query('UPDATE bids SET error = $2 WHERE id = $1', [id, err]);
    },
    set_done                :function (id, err) {
        pool.query('UPDATE bids SET error = $2 WHERE id = $1 and go = 0', [id, err]);
    },
    performed               :function (cb) {
        pool.query('SELECT count(*) AS count FROM bids WHERE status = 1 AND go = 1', function (err, res) {
            cb(res.rows[0].count);
        });
    }
};

var task = {
    add_uids                :function (bid_id, data, cb) {
        pool.query('INSERT INTO tasks(bid, uid) ' +
        'SELECT $1 AS bid, uid FROM (SELECT SUBSTRING(str,\'([0-9]+)\')::INT AS uid ' +
        'FROM regexp_split_to_table($2, \'\\s|,|:|\\n|\\.\') As str) AS tasks ' +
        'WHERE NOT uid ISNULL', [bid_id, data], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    add_json                :function (bid_id, data, cb) {
        pool.query('INSERT INTO tasks(bid, uid) ' +
            'SELECT bids.id AS bid, x.id AS uid ' +
            'FROM json_to_recordset($2) AS x(id INT, sex INT, city JSON, country JSON, last_seen JSON, bdate TEXT) ' +
            'JOIN bids ON bids.id = $1 ' +
            'WHERE (bids.sex = 0 OR x.sex = bids.sex) ' +
            'AND (bids.cities ISNULL OR to_number(x.city->>\'id\',\'99999999999\') = ANY(bids.cities)) ' +
            'AND (bids.country = 0 OR x.country->>\'id\' = bids.country::TEXT) ' +
            'AND (bids.last_seen = 0 OR EXTRACT(EPOCH FROM now()) - to_number(x.last_seen->>\'time\',\'9999999999\') <= bids.last_seen*86400) ' +
            'AND (bids.age_to = 0 OR EXTRACT(YEAR FROM now()) - to_number(right(x.bdate, 4), \'9999\') BETWEEN bids.age_from AND bids.age_to) ', [bid_id, JSON.stringify(data)], function (err, res) {
                cb_db(err, res, cb);
        });
    },
    get_all                 :function (bid_id, cb) {
        pool.query('SELECT tasks.id, uid, eid, bid FROM tasks ' +
            'JOIN bids ON tasks.bid = bids.id ' +
            'JOIN users ON bids.aid = users.id ' +
            'WHERE tasks.bid = $1 AND (tasks.state = 0 OR tasks.state = 14) AND users.ban = FALSE AND bids.status = 1 AND bids.go = 1', [bid_id], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    update_state            :function (id, aid, state, info, cb) {

        if(info === undefined) info = null;

        pool.query('UPDATE tasks SET aid = $2, state = $3, info = $4 WHERE id = $1', [id, aid, state, info], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    del                     :function (bid_id, cb) {
        pool.query('DELETE FROM tasks WHERE bid = $1', [bid_id], function (err, res) {
            cb(res);
        });
    },
    invite                  :function (task, sender, url, cb) {
        pool.query('SELECT invite($1, $2, $3)', [task, sender, url], function (err, res) {
            cb_db(err, res, cb);
        });
    },
    invite_cb               :function (res, cb) {
        pool.query('SELECT invite_cb($1)', [res], function (err, res) {
            cb_db(err, res, cb);
        });
    }
};

var money = {
    add_json_money_y        :function (data, cb) {
        pool.query('INSERT INTO money_y(sender,amount,operation_id,currency) ' +
            'SELECT sender,amount::REAL,operation_id,currency ' +
            'FROM json_populate_record(null::money_y, \''+data+'\')', function (err, res) {
            cb_db(err, res, cb);
        });
    }
};

// metods----------------------------------------

function APIDB() {
    this.User               = user;
    this.Bid                = bid;
    this.Task               = task;
    this.Money              = money;
    this.on                 = function (type, cb) {
        listen_client.query('LISTEN "'+type+'"');
        EE.addListener(type, cb);
    }
}

function start(max, lisen, cb) {

    EE.on('init_data_base', function() {
        init_table();
    });

    EE.on('init_table', function() {
        init_pool(max);
    });

    EE.on('init_pool', function () {
        init_listen(lisen);
    });

    EE.on('init_listen', function () {
        EE.emit('connected');
    });

    EE.on('connected', function() {
        cb(new APIDB);
    });

    init_data_base();
}

exports = module.exports = start;