const mysql = require('mysql2');
const {queryDb} = require("../helpers/db");

const searchController = async (req, res) => {
    let {type, zone} = req.params
    let searchTerm = req.query.q;
    let q;
    const category = 1;

    q = `SELECT * FROM properties 
            WHERE category = ${category} 
            AND (rental = 0 OR rental IS NULL)
            AND (hidden = 0 OR hidden IS NULL)
            ORDER BY searchOrd ASC`

    let typeLike
    if (type) {
        typeLike = type === 'all' ? mysql.escape('%') : mysql.escape(type)
        q = `SELECT * FROM properties 
            WHERE type LIKE ${typeLike} 
            AND category = ${category} 
            ORDER BY searchOrd ASC`
    }
    if (zone) {
        const city = mysql.escape(zone)
        const location = mysql.escape(zone)
        q = `SELECT * FROM properties 
            WHERE type LIKE ${typeLike} 
            AND (
                city = ${city} 
                OR location = ${location}
            )
            AND category = ${category} 
            AND (rental = 0 OR rental IS NULL)
            AND (hidden = 0 OR hidden IS NULL)
            ORDER BY searchOrd ASC`
    }
    if (searchTerm) {
        const eSearchTerm = mysql.escape(`%${searchTerm}%`)
        q = `SELECT * FROM properties 
            WHERE category = ${category} 
            AND (rental = 0 OR rental IS NULL)
            AND (hidden = 0 OR hidden IS NULL)
            AND ( 
                city LIKE ${eSearchTerm} 
                OR location LIKE ${eSearchTerm}
                OR name LIKE ${eSearchTerm}
                OR type LIKE ${eSearchTerm}
                OR caption LIKE ${eSearchTerm}
                OR ensamble LIKE ${eSearchTerm}
                OR postal_code LIKE ${eSearchTerm}
                OR ensamble LIKE ${eSearchTerm}
            )
            ORDER BY searchOrd ASC`
        zone = searchTerm
    }
    console.log(q);
    try {
        const rows = await queryDb(q);
        const obj = {property: rows, location: zone};
        res.render('property/categoryView', obj);
    } catch (e) {
        console.log('searchController error', e);
        res.end('error at categ');
    }
}

const searchEnsambleController = async (req, res) => {
    let {type, zone} = req.params
    let searchTerm = req.query.q;
    let q;
    const category = 2;

    q = `SELECT * FROM properties 
            WHERE category = ${category} 
            AND (rental = 0 OR rental IS NULL)
            AND (hidden = 0 OR hidden IS NULL)
            ORDER BY searchOrd ASC`

    let typeLike
    if (type) {
        typeLike = type === 'all' ? mysql.escape('%') : mysql.escape(type)
        q = `SELECT * FROM properties 
            WHERE type LIKE ${typeLike} 
            AND category = ${category} 
            ORDER BY searchOrd ASC`
    }
    if (zone) {
        const city = mysql.escape(zone)
        const location = mysql.escape(zone)
        q = `SELECT * FROM properties 
            WHERE type LIKE ${typeLike} 
            AND (
                city = ${city} 
                OR location = ${location}
            )
            AND category = ${category} 
            AND (rental = 0 OR rental IS NULL)
            AND (hidden = 0 OR hidden IS NULL)
            ORDER BY searchOrd ASC`
    }
    if (searchTerm) {
        const eSearchTerm = mysql.escape(`%${searchTerm}%`)
        q = `SELECT * FROM properties 
            WHERE category = ${category} 
            AND (rental = 0 OR rental IS NULL)
            AND (hidden = 0 OR hidden IS NULL)
            AND ( 
                city LIKE ${eSearchTerm} 
                OR location LIKE ${eSearchTerm}
                OR name LIKE ${eSearchTerm}
                OR type LIKE ${eSearchTerm}
                OR caption LIKE ${eSearchTerm}
                OR ensamble LIKE ${eSearchTerm}
                OR postal_code LIKE ${eSearchTerm}
                OR ensamble LIKE ${eSearchTerm}
            )
            ORDER BY searchOrd ASC`
        zone = searchTerm
    }
    console.log(q);
    try {
        const rows = await queryDb(q);
        const obj = {property: rows, location: zone, ensamble: true};
        res.render('property/categoryView', obj);
    } catch (e) {
        console.log('searchController error', e);
        res.end('error at ensamble');
    }
}

const searchRentController = async (req, res) => {
    let {type, zone} = req.params
    let searchTerm = req.query.q;
    let q;
    const category = 1;

    q = `SELECT * FROM properties 
            WHERE category = ${category} 
            AND (rental = 1)
            AND (hidden = 0 OR hidden IS NULL)
            ORDER BY searchOrd ASC`

    let typeLike
    if (type) {
        typeLike = type === 'all' ? mysql.escape('%') : mysql.escape(type)
        q = `SELECT * FROM properties 
            WHERE type LIKE ${typeLike} 
            AND category = ${category} 
            AND (rental = 1)
            ORDER BY searchOrd ASC`
    }
    if (zone) {
        const city = mysql.escape(zone)
        const location = mysql.escape(zone)
        q = `SELECT * FROM properties 
            WHERE type LIKE ${typeLike} 
            AND (
                city = ${city} 
                OR location = ${location}
            )
            AND category = ${category} 
            AND (rental = 1)
            AND (hidden = 0 OR hidden IS NULL)
            ORDER BY searchOrd ASC`
    }
    if (searchTerm) {
        const eSearchTerm = mysql.escape(`%${searchTerm}%`)
        q = `SELECT * FROM properties 
            WHERE category = ${category} 
            AND (rental = 1)
            AND (hidden = 0 OR hidden IS NULL)
            AND ( 
                city LIKE ${eSearchTerm} 
                OR location LIKE ${eSearchTerm}
                OR name LIKE ${eSearchTerm}
                OR type LIKE ${eSearchTerm}
                OR caption LIKE ${eSearchTerm}
                OR ensamble LIKE ${eSearchTerm}
                OR postal_code LIKE ${eSearchTerm}
                OR ensamble LIKE ${eSearchTerm}
            )
            ORDER BY searchOrd ASC`
        zone = searchTerm
    }
    console.log(q);
    try {
        const rows = await queryDb(q);
        const obj = {property: rows, location: zone, ensamble: true};
        res.render('property/categoryView', obj);
    } catch (e) {
        console.log('searchController error', e);
        res.end('error at rent');
    }
}

module.exports = {
    searchController, searchEnsambleController, searchRentController
}