/*
 * @author Oleg Khalidov <brooth@gmail.com>.
 * -----------------------------------------------
 * Freelance software development:
 * Upwork: https://www.upwork.com/fl/khalidovoleg
 * Freelancer: https://www.freelancer.com/u/brooth
 */
import { Router, Response } from 'express'
import { Pool, Connection, PoolConnection } from 'mysql'
import { Observable, } from 'rxjs'
import fetch from 'node-fetch';
import * as uuid from 'uuid'
import { parseNumber } from 'libphonenumber-js'
import * as microtime from 'microtime'

import { SyncContact, ContactPhone, Contact, ContactEmail, GSearchResult } from '../models/domain.models'
import { AuthRequest } from './auth.controller'
import { QueryObservable, GetConnectionObservable } from '../utils/rx.utils';
import { User } from '../models/auth.models';
import { loadContactNotes } from './contact_notes.controller';
import { C } from '../c';
import { googleGeocodeLocation } from './location.controller';
import { loadContactComments } from './contact_comments.controller';
import * as md5 from 'md5';

export class ContactsController {
    private db: Pool

    constructor(connection: Pool) {
        this.db = connection
        this.lookupPhoneLocation = this.lookupPhoneLocation.bind(this)
    }

    router(): Router {
        const router = Router()
        router.get('/', this.get.bind(this))
        router.get('/holders', this.loadHolders.bind(this))
        router.get('/dislike', this.getDisliked.bind(this))
        router.get('/count', this.getContactsCount.bind(this))
        router.get('/common', this.findCommonContacts.bind(this))
        router.post('/', this.add.bind(this))
        router.patch('/', this.sync.bind(this))
        router.patch('/public/:id', this.changePublic.bind(this))
        router.get('/fillHash', this.fillHash.bind(this))
        router.get('/deleteDuplicates', this.deleteDuplicates.bind(this))
        return router
    }

    private findCommonContacts(req: AuthRequest, res: Response) {
        console.log('ContactsController.findCommonContacts()')

        const ids = req.query.ids.split(',')
        GetConnectionObservable(this.db)
            .concatMap(connection => QueryObservable<Contact[]>(connection,
                'SELECT c.id, cp.value FROM contacts c' +
                ' INNER JOIN contact_phones cp on cp.contactId = c.id' +
                ' WHERE c.id IN (?)',
                [ids])
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                }))
            .subscribe((data: { id: string, value: string }[]) => {
                const results: { [key: string]: string[] } = {}
                for (let i = 0; i < data.length; i++) {
                    const c1 = data[i]
                    for (let j = 0; j < data.length; j++) {
                        const c2 = data[j]
                        if (i == j || c1.id === c2.id)
                            continue

                        let common = false
                        if (c1.value === c2.value) {
                            common = true
                        } else {
                            const phone1Parts = c1.value.match(/\d+/g)
                            const phone2Parts = c2.value.match(/\d+/g)
                            if (phone1Parts && phone2Parts) {
                                const formattedPhone1 = phone1Parts.join('')
                                const formattedPhone2 = phone2Parts.join('')
                                if (formattedPhone1 === formattedPhone2)
                                    common = true
                            }
                        }
                        if (common) {
                            if (!results[c1.id])
                                results[c1.id] = []
                            if (!results[c1.id].includes(c2.id))
                                results[c1.id].push(c2.id)
                        }
                    }
                }
                res.send({ results });
            }, error => {
                console.error(error)
                res.status(500).send()
            })
    }

    private getContactsCount(req: AuthRequest, res: Response) {
        console.log('ContactsController.getContactsCount()')

        GetConnectionObservable(this.db)
            .concatMap(connection => QueryObservable<Contact[]>(connection,
                'SELECT count(*) as count FROM contacts')
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                }))
            .subscribe(results => {
                res.send({ result: results[0].count });
            }, error => {
                console.error(error)
                res.status(500).send()
            })
    }

    private loadHolders(req: AuthRequest, res: Response) {
        console.log('ContactsController.loadHolders()')

        GetConnectionObservable(this.db)
            .concatMap(connection => QueryObservable<Contact[]>(connection,
                'SELECT id, name FROM contacts WHERE holderId IS NULL and id != ?',
                [req.user.contactId])
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                }))
            .subscribe(results => {
                res.send({ results });
            }, error => {
                console.error(error)
                res.status(500).send()
            })
    }

    private changePublic(req: AuthRequest, res: Response) {
        console.log('ContactsController.changePublic()', req.params, req.body)

        const updateTs = microtime.now()
        const contactId = req.params.id
        const value = req.body.public === true
        const restrictedGroup = req.body.restrictedTo || []
        const restrict = value && restrictedGroup.length > 0
        const queries = [
            'UPDATE contacts SET public = ?, public_restricted =?, updateTs = ? WHERE id = ?']
        const params = [value, restrict, updateTs, contactId]
        if (restrict) {
            restrictedGroup.forEach((holderId: string) => {
                queries.push('INSERT INTO public_restrictions VALUES(?, ?, ?)')
                params.push(uuid.v4(), contactId, holderId)
            })
        } else {
            queries.push('DELETE FROM public_restrictions WHERE contactId = ?')
            params.push(contactId)
        }

        GetConnectionObservable(this.db)
            .concatMap(connection => QueryObservable<Contact[]>(connection,
                queries.join(';'), params)
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                }))
            .subscribe(_ => {
                res.send({ updateTs });
            }, error => {
                console.error(error)
                res.status(500).send()
            })
    }

    private getDisliked(req: AuthRequest, res: Response) {
        console.log('ContactsController.getDisliked()')

        GetConnectionObservable(this.db)
            .concatMap(connection => QueryObservable<Contact[]>(connection,
                'SELECT uc.id, uc.localId as lid, uc.name as n, false as lk, h.id as hid, h.name as hn' +
                ' FROM contacts uc' +
                '  LEFT JOIN contacts h ON h.id = uc.holderId' +
                ' WHERE uc.holderId != ? AND uc.deleteTs IS NULL' +
                ' AND EXISTS(SELECT id FROM contact_likes cl WHERE uc.id = cl.contactId AND cl.value = 0 LIMIT 1)' +
                ' LIMIT 1000',
                [req.user.contactId])
                .concatMap(results =>
                    attachContactDetails(connection, results.map(contact => {
                        contact.p = !!contact.p
                        if (contact.lk != null)
                            contact.lk = !!contact.lk
                        const c = contact as any
                        c.h = {
                            id: c.hid,
                            n: c.hn,
                        }
                        return contact
                    }), null, false, true))
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                }))
            .subscribe(results => {
                res.send({ results });
            }, error => {
                console.error(error)
                res.status(500).send()
            })
    }

    private get(req: AuthRequest, res: Response) {
        console.log('ContactsController.get()', req.query)

        GetConnectionObservable(this.db)
            .concatMap(connection => (
                req.query.southEastLatitude
                    ? this.searchInRegion(req, connection)
                        .concatMap(results =>
                            attachContactDetails(connection, results, req.user.contactId, 'if public', false))
                    : req.query.query != null
                        ? this.searchByQuery(req, connection)
                            .concatMap(results =>
                                attachContactDetails(connection, results, req.user.contactId, 'if public', false))
                        : loadUserContacts(connection, req.user,
                            parseInt(req.query.since), parseInt(req.query.limit)))
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                }))
            .subscribe(results => {
                res.send({ results });
            }, error => {
                console.error(error)
                res.status(500).send()
            })
    }

    private searchByQuery(req: AuthRequest, connection: PoolConnection): Observable<Contact[]> {
        console.log('ContactsController.searchByQuery()')

        const queries = req.query.query.split('|') as string[]
        return Observable.from(queries)
            .map(query => '%' + query.trim().toLowerCase() + '%')
            .concatMap(q => QueryObservable<Contact[]>(connection,
                'SELECT uc.id, uc.name as n, cl.value as lk,' +
                '  COUNT(DISTINCT c.id) as noc,' +
                '  AVG(cp.latitude) as clt,' +
                '  AVG(cp.longitude) as cln' +
                ' FROM contacts uc' +
                '  LEFT JOIN contact_likes cl ON uc.id = cl.contactId AND cl.userId = ?' +
                '  LEFT JOIN contacts c ON c.deleteTs IS NULL AND uc.id = c.holderId AND c.public = 0' +
                '  LEFT JOIN contact_phones cp ON c.id = cp.contactId' +
                '  LEFT JOIN contact_emails ce ON c.id = ce.contactId' +
                '  LEFT JOIN contact_notes cn ON c.id = cn.contactId' +
                '  LEFT JOIN gsearch_results gsr ON gsr.phoneId = cp.id' +
                ' WHERE uc.id != ?' +
                '   AND uc.holderId IS NULL' +
                '   AND (' +
                '    LOWER(c.name) like ?' +
                '    OR LOWER(cp.label) like ?' +
                '    OR cp.value like ?' +
                '    OR cp.country like ?' +
                '    OR cp.locality like ?' +
                '    OR LOWER(ce.label) like ?' +
                '    OR ce.value like ?' +
                '    OR LOWER(cn.text) like ?' +
                '    OR LOWER(gsr.title) like ?' +
                '    OR LOWER(gsr.description) like ?' +
                '   )' +
                ' GROUP BY uc.id, lk',
                [req.user.id, req.user.contactId,
                    q, q, q, q, q, q, q, q, q, q, q, q])
                .concatMap(holders =>
                    QueryObservable<Contact[]>(connection,
                        'SELECT DISTINCT c.id, c.name as n, hl.value as lk, h.id as hid,' +
                        '  h.name as hn' +
                        ' FROM contacts c' +
                        '  LEFT JOIN contacts h ON h.id = c.holderId' +
                        '  LEFT JOIN users hu ON hu.contactId = h.id' +
                        '  LEFT JOIN contact_likes hl ON hl.userId = hu.id AND hl.contactId = c.id' +
                        '  LEFT JOIN contact_phones cp ON c.id = cp.contactId' +
                        '  LEFT JOIN contact_emails ce ON c.id = ce.contactId' +
                        '  LEFT JOIN gsearch_results gsr ON gsr.phoneId = cp.id AND c.public = 1' +
                        '  LEFT JOIN contact_comments cc ON c.id = cc.contactId AND cc.authorId = ?' +
                        '  LEFT JOIN public_restrictions pr ON c.id = pr.contactId AND pr.restrictedTo = ?' +
                        ' WHERE c.deleteTs IS NULL' +
                        '   AND (' +
                        '    (c.holderId != ? AND c.public = 1 AND ' +
                        '     (c.public_restricted = 0 OR pr.id IS NOT NULL))' +
                        '    OR (c.holderId IS NULL AND c.id NOT IN (?))' +
                        '   )' +
                        '   AND (' +
                        '    LOWER(c.name) like ?' +
                        '    OR LOWER(cp.label) like ?' +
                        '    OR cp.value like ?' +
                        '    OR cp.country like ?' +
                        '    OR cp.locality like ?' +
                        '    OR LOWER(ce.label) like ?' +
                        '    OR ce.value like ?' +
                        '    OR LOWER(gsr.title) like ?' +
                        '    OR LOWER(gsr.description) like ?' +
                        '    OR LOWER(cc.text) like ?' +
                        '   )',
                        [req.user.contactId, req.user.contactId, req.user.contactId,
                        [req.user.contactId, ...holders.map(h => h.id)],
                            q, q, q, q, q, q, q, q, q, q, q])
                        .map(publics => {
                            publics.forEach((s: any) => {
                                if (s.hid != null) {
                                    s.h = {
                                        id: s.hid,
                                        n: s.hn,
                                    }
                                }
                                if (s.lk != null) s.lk = !!s.lk
                                delete s.hid
                                delete s.hn
                                holders.push(s)
                            })
                            return holders
                        }))
            )
            .reduce((prev, next) => {
                const results = Array.from(prev)
                next.forEach(n => {
                    if (prev.findIndex(p => p.id == n.id) == -1)
                        results.push(n)
                })
                return results
            })
    }

    private searchInRegion(req: AuthRequest, connection: PoolConnection): Observable<Contact[]> {
        console.log('ContactsController.searchInRegion()')

        const holdersQuery = [
            'SELECT uc.id, uc.name as n, ucl.value as lk, ucp.country as cn, ucp.locality as lc,',
            '    COUNT(DISTINCT c.id) as noc,',
            '    GROUP_CONCAT(DISTINCT c.id) as cs,' +
            '    AVG(ucp.latitude) as clt,',
            '    AVG(ucp.longitude) as cln',
            ' FROM contacts uc',
            '    LEFT JOIN contact_likes ucl ON uc.id = ucl.contactId AND ucl.userId = ?',
            '    LEFT JOIN contacts c ON c.deleteTs IS NULL AND uc.id = c.holderId AND c.public = 0',
            '    LEFT JOIN contact_phones ucp ON c.id = ucp.contactId']
        const holdersQueryParams = [req.user.id]

        const publicsQuery = [
            'SELECT DISTINCT c.id, c.name as n, hl.value as lk, h.id as hid, h.name as hn',
            ' FROM contacts c',
            '  LEFT JOIN contacts h ON h.id = c.holderId' +
            '  LEFT JOIN users hu ON hu.contactId = h.id' +
            '  LEFT JOIN contact_likes hl ON hl.userId = hu.id AND hl.contactId = c.id' +
            '  LEFT JOIN contact_comments cc ON c.id = cc.contactId AND cc.authorId = ?' +
            '  LEFT JOIN public_restrictions pr ON c.id = pr.contactId AND pr.restrictedTo = ?' +
            '  INNER JOIN contact_phones cp ON c.id = cp.contactId' +
            '       AND cp.latitude BETWEEN ? AND ?' +
            '       AND cp.longitude BETWEEN ? AND ?'
        ]
        const publicsQueryParams = [
            req.user.contactId, req.user.contactId,
            req.query.northWestLatitude, req.query.southEastLatitude,
            req.query.southEastLongitude, req.query.northWestLongitude]

        if (req.query.query) {
            const commonQueries =
                ' LEFT JOIN contact_emails ce ON c.id = ce.contactId' +
                ' LEFT JOIN gsearch_results gsr ON gsr.phoneId = cp.id'
            holdersQuery.push(' LEFT JOIN contact_phones cp ON c.id = cp.contactId')
            holdersQuery.push(commonQueries)
            publicsQuery.push(commonQueries)
        }

        if (req.query.holderId) {
            holdersQuery.push(' WHERE uc.id = ?')
            holdersQueryParams.push(req.query.holderId)
            publicsQuery.push(' WHERE c.holderId = ?')
            publicsQueryParams.push(req.query.holderId)
        } else {
            holdersQuery.push(' WHERE uc.id != ?')
            holdersQueryParams.push(req.user.contactId)
            publicsQuery.push(' WHERE c.deleteTs IS NULL')
        }

        holdersQuery.push(
            '    AND uc.holderId IS NULL' +
            '    AND ucp.latitude BETWEEN ? AND ?' +
            '    AND ucp.longitude BETWEEN ? AND ?')
        holdersQueryParams.push(
            req.query.northWestLatitude, req.query.southEastLatitude,
            req.query.southEastLongitude, req.query.northWestLongitude)

        publicsQuery.push(
            '   AND (' +
            '    (c.holderId != ? AND c.public = 1 AND ' +
            '     (c.public_restricted = 0 OR pr.id IS NOT NULL))' +
            '    OR (c.holderId IS NULL AND c.id != ?)' +
            '   )')
        publicsQueryParams.push(req.user.contactId, req.user.contactId)

        if (req.query.query) {
            const queries = req.query.query.split('|') as string[]
            const filters = queries
                .map(p => '%' + p.toLowerCase() + '%')
                .map(q => {
                    holdersQueryParams.push(q, q, q, q, q, q, q);
                    publicsQueryParams.push(q, q, q, q, q, q, q, q);
                    return [
                        ' LOWER(c.name) like ? ',
                        ' LOWER(cp.label) like ? ',
                        ' cp.value like ? ',
                        ' LOWER(ce.label) like ? ',
                        ' ce.value like ? ',
                        ' LOWER(gsr.title) like ?',
                        ' LOWER(gsr.description) like ?',
                    ]
                })
                .reduce((a1, a2) => a1.concat(a2))
            holdersQuery.push(' AND (' + filters.join(' OR ') + ')')
            publicsQuery.push(' AND (' + filters.join(' OR ') + ' OR LOWER(cc.text) like ?)')
        }
        holdersQuery.push(' GROUP BY lc, cn, uc.id, lk')
        return QueryObservable<Contact[]>(connection, holdersQuery.join(''), holdersQueryParams)
            .do(holders => holders.forEach((h: any) => h.cs = h.cs.split(',')))
            .concatMap(holders =>
                QueryObservable<Contact[]>(connection, publicsQuery.join(''), publicsQueryParams)
                    .map(publics => {
                        publics.forEach((s: any) => {
                            if (s.hid != null) {
                                s.h = {
                                    id: s.hid,
                                    n: s.hn,

                                }
                            }
                            if (s.lk != null) s.lk = !!s.lk
                            delete s.hid
                            delete s.hn
                            holders.push(s)
                        })
                        return holders
                    }))
    }

    private lookupPhoneLocationByK780(number: string): Observable<Partial<ContactPhone> | null> {
        const uri = `http://api.k780.com/?app=phone.get&phone=${number}&appkey=${C.K780_APP_KEY}&sign=${C.K780_APP_SIGN}&format=json`
        return Observable.from(fetch(uri))
            .concatMap(res => res.json())
            .concatMap(json => {
                if (json.success !== '1' || !json.result || !json.result.att)
                    return Observable.of(null)
                return googleGeocodeLocation({ address: json.result.att })
                    .map(location => {
                        if (!location)
                            return null
                        return {
                            c: location.country,
                            cc: location.countryCode,
                            lc: location.locality,
                            lt: location.latitude,
                            ln: location.longitude
                        }
                    })
            })
            .catch(err => {
                console.log('k780 request failed', err)
                return Observable.of(null)
            })
    }

    private lookupChinaLocalPhoneLocation(connection: Connection, number: string):
        Observable<Partial<ContactPhone> | null> {
        return QueryObservable<any[]>(
            connection,
            'SELECT * FROM phonet WHERE area_code = ? limit 1',
            [number.slice(0, 4)])
            .concatMap(results => results.length ? Observable.of(results) :
                QueryObservable<any[]>(connection,
                    'SELECT * FROM phonet WHERE area_code = ? limit 1',
                    [number.slice(0, 3)]))
            .concatMap(results => {
                if (results.length) {
                    const phone: Partial<ContactPhone> = {
                        lc: results[0].province + ', ' + results[0].city,
                        lt: parseFloat(results[0].Lat),
                        ln: parseFloat(results[0].Lon),
                    }
                    // yeah, they exist
                    if (phone.lt! > 90 || phone.ln! > 180 ||
                        phone.lt! < -90 || phone.ln! < -180) {
                        console.log('Invalid coordinates found for phone %s, %d, %d',
                            number, phone.lt, phone.ln)
                        return Observable.of(null);
                    }
                    return Observable.of(phone);
                }
                return this.lookupPhoneLocationByK780(number)
            })
    }

    private lookupChinaMobilePhoneLocation(connection: Connection,
        fullNumber: string, shortNumber: string): Observable<Partial<ContactPhone> | null> {
        return QueryObservable<any[]>(connection,
            'SELECT * FROM phonet WHERE phone = ?',
            [shortNumber.slice(0, 7)])
            .concatMap(results => {
                if (results.length) {
                    const phone: Partial<ContactPhone> = {
                        lc: results[0].province + ', ' + results[0].city,
                        lt: parseFloat(results[0].Lat),
                        ln: parseFloat(results[0].Lon),
                    }
                    if (phone.lt! > 90 || phone.ln! > 180 ||
                        phone.lt! < -90 || phone.ln! < -180) {
                        console.log('Invalid coordinates found for phone %s, %d, %d',
                            shortNumber, phone.lt, phone.ln)
                        return Observable.of(null);
                    }
                    return Observable.of(phone);
                }
                return this.lookupPhoneLocationByK780('+' + fullNumber)
            })
    }

    private lookupInternalPhoneLocation = (connection: Connection, user: User, number: string):
        Observable<Partial<ContactPhone> | null> =>
        QueryObservable<any[]>(connection,
            'SELECT countryCode as cc FROM contact_phones' +
            ' WHERE contactId = ? AND value = ?',
            [user.contactId, user.phone])
            .concatMap(holderPhones => {
                if (holderPhones.length == 0) {
                    console.error('cannot find user phone, user: %s, contactId: %s, phone: %s',
                        user.id, user.contactId, user.phone)
                    return Observable.of(null);
                }
                const holderPhone = holderPhones[0]
                if (holderPhone.cc == 'CN') {
                    return (number.startsWith('0')
                        ? this.lookupChinaLocalPhoneLocation(connection, number)
                        : this.lookupChinaMobilePhoneLocation(connection, number, number))
                        .map(result => {
                            if (result != null)
                                return {
                                    ...result,
                                    vl: true,
                                    c: '中国',
                                    cc: holderPhone.cc,
                                }

                            return result
                        })
                } else {
                    return Observable.of(null);
                }
            })

    private lookupPhoneLocation(connection: Connection, user: User, _phone: ContactPhone):
        Observable<ContactPhone> {
        const phone: ContactPhone = { ..._phone, vl: false }

        const numberParts = phone.v.match(/\d+/g)
        if (numberParts == null) {
            console.log('invalid phone number %s', phone.v)
            return Observable.of(phone)
        }
        const formattedPhoneNumber = numberParts.join('')

        if (phone.v.startsWith('+')) {
            const data = parseNumber(phone.v, { extended: true });
            if (data.valid === true) {
                phone.vl = true
                phone.cc = data.country
                if (phone.cc == 'CN') {
                    phone.c = '中国';
                    return this.lookupChinaMobilePhoneLocation(connection,
                        formattedPhoneNumber, data.phone as string)
                        .map(result => {
                            if (result != null)
                                return { ...phone, ...result };
                            return phone
                        })
                }
            }
            return Observable.of(phone);
        } else {
            return this.lookupInternalPhoneLocation(connection, user, formattedPhoneNumber)
                .map(data => {
                    if (data != null)
                        return { ...phone, ...data };
                    return phone
                })
        }
    }

    private add(req: AuthRequest, res: Response) {
        console.log('ContactsController.add()')
        const contact = req.body.items as Contact[]
        GetConnectionObservable(this.db)
            .subscribe(
                connection => {
                    this.saveContact(connection, contact[0], req)
                        .subscribe(results => {
                            res.send({results})
                        })
                }
            )
    }

    private searchContact(v: any, contact: any) : any {
        return v.ps.map(a => a.v).filter(b => contact.ps.map(c => c.v).includes(b)).length
    }

    private saveContact(connection: Connection, contact: Contact, req: AuthRequest) {
        return Observable.from(contact.ps)
            .concatMap(phone => this.lookupPhoneLocation(connection, req.user, phone))
            .toArray()
            .map(phones => {
                const id = uuid.v4()
                const ts = microtime.now()
                const result: SyncOperationData = {
                    queries: [],
                    params: [],
                    result: {
                        operation: 'CREATE',
                        contact: {
                            ...contact,
                            id,
                            uts: ts,
                            ps: phones,
                        }
                    }
                }
                result.queries.push(
                    'INSERT INTO contacts VALUES (?, ?, ?, null, ?, ?, ?, ?, ?, ?)')
                result.params.push(id, ts, ts, req.user.contactId,
                    contact.lid, contact.n, contact.p, false, contact.uh)
                phones.forEach(phone => {
                    phone.id = uuid.v4()
                    result.queries.push('INSERT INTO contact_phones' +
                        ' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
                    result.params.push(phone.id, id, phone.l, phone.v,
                        phone.vl, phone.c, phone.cc, phone.lc,
                        toDecimal(phone.lt), toDecimal(phone.ln))
                })
                contact.es.forEach(e => {
                    result.queries.push('INSERT INTO contact_emails VALUES (?, ?, ?, ?)')
                    result.params.push(uuid.v4(), id, e.l, e.v)
                })
                return result;
            })
            .concatMap(q => QueryObservable(connection, q.queries.join(';'), q.params).mapTo(q.result))
    }

    private updateContact(connection: Connection, old: Contact, contact: Contact, req: AuthRequest) {
        return Observable.from(contact.ps)
            .concatMap(phone => this.lookupPhoneLocation(connection, req.user, phone))
            .toArray()
            .map(phones => {
                const ts = microtime.now()
                const data: SyncOperationData = {
                    queries: [
                        'DELETE FROM contact_phones WHERE contactId = ?',
                        'DELETE FROM contact_emails WHERE contactId = ?',
                    ],
                    params: [old.id, old.id],
                }
                data.queries.push('UPDATE contacts SET updateTs = ?, deleteTs = NULL, name = ?, unique_hash = ?' +
                    ' WHERE id = ?')
                data.params.push(ts, contact.n, contact.uh, old.id)
                phones.forEach(p => {
                    p.id = uuid.v4()
                    data.queries.push('INSERT INTO contact_phones' +
                        ' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
                    data.params.push(p.id, old.id, p.l, p.v,
                        p.vl, p.c, p.cc, p.lc,
                        toDecimal(p.lt), toDecimal(p.ln))
                })
                contact.es.forEach(e => {
                    data.queries.push('INSERT INTO contact_emails VALUES (?, ?, ?, ?)')
                    data.params.push(uuid.v4(), old.id, e.l, e.v)
                })
                return data;
            })
            .concatMap(q => QueryObservable(connection,
                q.queries.join(';'),
                q.params))
            .subscribe()
    }

    private deleteContact(connection: Connection, contact: string) {
        return Observable.of({
            queries: [
                'DELETE FROM contacts WHERE id = ?',
                'DELETE FROM contact_notes WHERE contactId = ?',
                'DELETE FROM contact_phones WHERE contactId = ?',
                'DELETE FROM contact_emails WHERE contactId = ?'
            ],
            params: [contact, contact, contact, contact],
        })
        .concatMap(q => QueryObservable(connection,
            q.queries.join(';'),
            q.params))
    }

    private formatNumber(number: string): string {
        return number
            .trim()
            .replace(/-|\(|\)|( )/g, '')
    }

    private formatEmail(email: string): string {
        return email
            .trim()
            .toLowerCase()
    }

    private createHash (contact: Contact) : string {
        const phones = contact.ps
            .map(phone => phone.v)
            .map(n => this.formatNumber(n))
            .sort()
            .join('');
        const emails = contact.es
            .map(email => email.v)
            .map(this.formatEmail)
            .sort()
            .join('');
        console.log(`${contact.n}${phones}${emails}`)
        return md5(`${contact.n}${phones}${emails}`)
    }

    private sync(req: AuthRequest, res: Response) {
        console.log('ContactsController.sync() ', req.body.items.length)
        const syncResponse : SyncOperationResponse[] = [];
        const items = req.body.items as Contact[];
        const processedContacts : string[] = [];
        GetConnectionObservable(this.db)
            .concatMap(connection => {
                return QueryObservable<Contact[]>(connection,
                'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
                '  cl.value as lk, c.public as p, unique_hash as uh' +
                ' FROM contacts c' +
                '  LEFT JOIN contact_likes cl ON c.id = cl.contactId AND cl.userId = ?' +
                ' WHERE holderId = ?' +
                ' AND deleteTs is null' +
                ' ORDER BY updateTs',
                [req.user.id, req.user.contactId])
                .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))
                .concatMap(dbContacts => {
                    dbContacts.forEach(contact => {
                        const isProcessed = processedContacts.includes(contact.uh)
                        if(isProcessed) return
                        processedContacts.push(contact.uh)
                        const existContactIndex = items.findIndex(localContact => (contact.uh === localContact.ouh) || (contact.uh === localContact.uh))
                        if(existContactIndex === -1){
                            syncResponse.push({ operation: 'CREATE', contact: contact } as SyncOperationResponse)
                        } else {
                            if(items[existContactIndex].ouh && items[existContactIndex].uh !== items[existContactIndex].ouh) {
                                this.updateContact(connection, contact, items[existContactIndex], req)
                                contact.lk ? items[existContactIndex].lk = contact.lk : null
                                contact.ns ? items[existContactIndex].ns = contact.ns : null
                                contact.p ? items[existContactIndex].p = contact.p : null
                                contact.cc ? items[existContactIndex].cc = contact.cc : null
                                contact.uts ? items[existContactIndex].uts = contact.uts : null
                                syncResponse.push({ operation: 'UPDATE', contact: items[existContactIndex] } as SyncOperationResponse)
                                processedContacts.push(items[existContactIndex].uh, items[existContactIndex].ouh)
                            } else {
                                contact.lid = items[existContactIndex].lid
                                // syncResponse.push({ operation: 'SKIP', contact: contact } as SyncOperationResponse)
                            }
                        }
                    })
                    items.filter(contact => !processedContacts.includes(contact.uh))
                        .forEach(contact => {
                            const isProcessed = processedContacts.includes(contact.uh)
                            if(!isProcessed){
                                this.saveContact(connection, contact, req)
                                    .subscribe(createdContact => console.log('createdContact=> ', createdContact))
                                // syncResponse.push({ operation: 'LOCAL', contact } as SyncOperationResponse)
                                processedContacts.push(contact.uh)
                            }
                        })
                        console.log('return')
                   return dbContacts;
                })
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                })
            })
            .toArray()
            .subscribe(
                _ => {
                    res.send({ results: syncResponse })
                },
                error => {
                    console.error(error)
                    res.status(500).send()
            })
    }

    private fillHash(req: AuthRequest, res: Response) {
        console.log('ContactsController.fillHash()')
        const params: any = [];
        const queries: any = [];
        GetConnectionObservable(this.db)
            .concatMap(connection => {
                return QueryObservable<Contact[]>(connection,
                'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
                '  cl.value as lk, c.public as p, unique_hash as uh' +
                ' FROM contacts c' +
                '  LEFT JOIN contact_likes cl ON c.id = cl.contactId' +
                ' AND deleteTs is null' +
                ' ORDER BY updateTs',
                [])
                .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))
                .concatMap(dbContacts => {
                    dbContacts.map(contact => {
                        queries.push('UPDATE contacts SET unique_hash = ? WHERE id = ?');
                        params.push(this.createHash(contact), contact.id)
                    })
                    console.log(queries.length)
                    Observable.of({
                        queries,
                        params
                    }).concatMap(q => QueryObservable(connection,
                        q.queries.join(';'),
                        q.params))
                    .subscribe()
                   return dbContacts;
                })
                .do(_ => connection.release())
                .catch(error => {
                    connection.release();
                    return Observable.throw(error);
                })
            })
            .toArray()
            .subscribe(
                _ => {
                    res.send({ result: 'OK' })
                },
                error => {
                    console.error(error)
                    res.status(500).send()
            })
    }
    private deleteDuplicates(req: AuthRequest, res: Response) {
        console.log('ContactsController.deleteDuplicates()')
        const params: any = [];
        const queries: any = [];
        GetConnectionObservable(this.db)
            .concatMap(connection => {
                return QueryObservable<User[]>(connection, 'SELECT * from users', [])
                    .concatMap(dbUsers => Observable.from(dbUsers)
                        .concatMap(user => QueryObservable<Contact[]>(connection,
                            'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
                            '  cl.value as lk, c.public as p, unique_hash as uh' +
                            ' FROM contacts c' +
                            '  LEFT JOIN contact_likes cl ON c.id = cl.contactId' +
                            ' AND deleteTs is null' +
                            ' WHERE holderId = ?' +
                            ' ORDER BY updateTs',
                            [user.contactId])
                            .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))
                            .concatMap(dbContacts => {
                                if(dbContacts.length) {
                                    dbContacts.forEach((contact, index) => {
                                        const duplicates = dbContacts.filter(val => val.uh === contact.uh)
                                        if(duplicates.length > 1) {
                                            if(contact.cc && !contact.cc.length) {
                                                queries.push(
                                                    'DELETE FROM contacts WHERE id = ?',
                                                    'DELETE FROM contact_notes WHERE contactId = ?',
                                                    'DELETE FROM contact_phones WHERE contactId = ?',
                                                    'DELETE FROM contact_emails WHERE contactId = ?'
                                                );
                                                params.push(contact.id, contact.id, contact.id, contact.id)
                                            }
                                        }
                                        dbContacts.splice(index, 1)
                                    })
                                }
                               return dbContacts;
                            })
                        )
                    )
                    .last()
                    .do(_ => {
                        if(queries.length && params.length){
                            Observable.of({ queries, params })
                            .concatMap(q => QueryObservable(connection, q.queries.join(';'), q.params))
                            .subscribe()
                        }
                        connection.release();
                    })
                    .catch(error => {
                        connection.release();
                        return Observable.throw(error);
                   })
            })
            .toArray()
            .subscribe(
                _ => {
                    res.send({ result: 'OK' })
                },
                error => {
                    console.error(error)
                    res.status(500).send()
            })
    }
}
type SyncOperationResponse = {
    operation: string,
    contact: Contact
}

type SyncOperationData = {
    queries: string[],
    params: any[],
    result?: any,
}

const toDecimal = (num: number | null) =>
    num == null ? null : Math.round(num * 1000000) / 1000000;

export const loadContactPhones = (connection: Connection, id: string, includeGsr: boolean) =>
    QueryObservable<ContactPhone[]>(connection,
        'SELECT id, label as l, value as v, valid as vl, country as c,' +
        ' countryCode as cc, locality as lc, latitude as lt, longitude as ln' +
        ' FROM contact_phones WHERE contactId = ?', [id])
        .concatMap(phones => Observable.from(phones)
            .map(phone => {
                phone.vl = phone.vl === true
                return phone
            })
            .concatMap(phone => !includeGsr
                ? Observable.of(phone)
                : QueryObservable<GSearchResult[]>(connection,
                    'SELECT rank_ as r, uri as u, title as t, description as d' +
                    ' FROM gsearch_results' +
                    ' WHERE phoneId = ?',
                    [phone.id])
                    .map(gsresults => {
                        phone.gsr = gsresults
                        return phone
                    }))
            .toArray())

export const loadContactEmails = (connection: Connection, id: string) =>
    QueryObservable<ContactEmail[]>(connection,
        'SELECT label as l, value as v FROM contact_emails WHERE contactId = ?', [id])

export const loadUserContacts = (connection: Connection, user: User, since: number, limit: number) =>
    QueryObservable<Contact[]>(connection,
        'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
        '  cl.value as lk, c.public as p' +
        ' FROM contacts c' +
        '  LEFT JOIN contact_likes cl ON c.id = cl.contactId AND cl.userId = ?' +
        ' WHERE holderId = ?' +
        '   AND updateTs > ?' +
        ' ORDER BY updateTs' +
        ' LIMIT ?',
        [user.id, user.contactId, since, limit || 1000])
        .concatMap(contacts => Observable.from(contacts)
            .map(contact => {
                contact.p = !!contact.p
                contact.d = contact.d != null ? true : undefined
                return contact
            })
            .toArray())
        .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))

const attachContactDetails = (connection: Connection, contacts: Contact[], authorId: string | null,
    includeGsr: boolean | 'if public', includeNodes: boolean) => Observable.from(contacts)
        .map(contact => {
            if (contact.lk != null)
                contact.lk = !!contact.lk
            return contact;
        })
        .concatMap(contact => loadContactPhones(connection, contact.id,
            includeGsr === 'if public' && contact.p === true || includeGsr as boolean)
            .map(phones => {
                contact.ps = phones
                return contact
            }))
        .concatMap(contact => loadContactEmails(connection, contact.id)
            .map(emails => {
                contact.es = emails
                return contact
            }))
        .concatMap(contact => !includeNodes
            ? Observable.of(contact)
            : loadContactNotes(connection, contact.id)
                .map(notes => {
                    contact.ns = notes
                    return contact
                }))
        .concatMap(contact => loadContactComments(connection, authorId == null ? '' : authorId, contact.id)
                .map(comment => {
                    contact.cc = comment
                    return contact
                }))
        .toArray()