<?php

namespace Dealerdirect\Agreements\Models;

final class MotorbikeModel extends AbstractModel
{
    protected function getLogTable(): string
    {
        return 'dealer01_ddmoto.BO_Verkopen_NL_loggg';
    }

    /**
     * {@inheritDoc}
     */
    protected function getPurchaseQuery(): string
    {
        return <<<SQL
            SELECT
                bom.id AS lot_id,
                bom.reference AS site_reference,
                YEAR(bom.bouwjaar) AS construction_year,
                SB_MERKEN.merkNaam AS make,
                SB_MODELLEN.modelNaam AS model,
                bom.uitvoering AS execution,
                bom.brandstof AS fuel,
                bom.soortmotorfiets AS bodywork,
                bom.cylinder AS cylinder_amount,
                bom.vermogen AS power,
                bom.kenteken AS license_plate,
                CONCAT(bom.kmstand, IF(bom.kms = 2, ' mi', ' km')) AS mileage,
                bom.kleur AS color,
                bom.kleur2 AS color_2,
                bom.kleur3 AS color_3,
                bom.transmissie AS transmission,
                bom.onderhoudshistorie AS maintenance_history,
                bom.apk AS inspection_valid_until,
                bom.eigendom AS marge,
                vl.gehaald AS pick_up,
                bom.con_opmerkingen AS internal_comments,
                bom.opmerkingenklant AS consumer_comments,
                bop.aanhef AS consumer_salutation,
                bop.voornaam AS consumer_first_letters,
                bop.achternaam AS consumer_name,
                bop.straatnaam AS consumer_street_name,
                bop.huisnummer AS consumer_house_number,
                UPPER(bop.postcode) AS consumer_zip_code,
                bop.woonplaats AS consumer_city,
                bop.country_id AS consumer_country,
                bop.email AS consumer_email,
                bop.telefoon1 AS consumer_phone,
                bop.telefoon2 AS consumer_mobile,
                bedr.bedrijvenID AS company_id,
                bedr.bedrijfsnaam AS company_name,
                bedr.pri_lang AS company_language,
                bedr.bedrijvenID AS company_id,
                bedr.bedrijfsnaam AS company_name,
                bedr.adres AS company_street_name,
                bedr.huisnummer AS company_house_number,
                bedr.postcode AS company_zip_code,
                bedr.land AS company_country,
                bedr.plaats AS company_city,
                bedr.bedrijfstel AS company_phone,
                bedr_email.akkoord_email AS company_agree_email,
                bedr_email.akkoord_email2 AS company_agree_email_2,
                vl.verkoop_bedrag AS sales_price,
                vl.bem_bedrag AS sales_fee,
                vl.verkoop_datum AS sales_date,
                IF(vl.verkoper = 0, 'Dealerdirect', bl.voornaam) AS sales_agent_name,
                vl.verkoper AS sales_agent,
                GROUP_CONCAT(opties.optie SEPARATOR ';') vehicle_options,
                bom.con_uiterlijk AS condition_exterior,
                bom.con_technisch AS condition_technical,
                bom.con_bandenvoor AS condition_front_tire,
                bom.con_bandenprofiel AS condition_rear_tire,
                bom.con_aandrijving AS condition_propulsion,
                bom.con_schadevrij AS damage_free,
                bom.extra_data->>"$.bicycle_type" AS bicycle_type,
                bom.extra_data->>"$.gears" AS gears,
                bom.extra_data->>"$.engine_position" AS engine_position,
                bom.extra_data->>"$.battery_wattage" AS battery_wattage,
                bom.extra_data->>"$.condition_battery" AS condition_battery,
                bom.extra_data->>"$.condition" AS `condition_general`,
                bom.extra_data->>"$.checklist_id" AS checklist_id,
                bom.extra_data->>"$.VIN" AS vin,
                bom.extra_data->>"$.wacht_op_keuring" AS wok_notification,
                bom.extra_data->>"$.tenaamstellen_mogelijk" AS registration_possible,
                vl.extra_data->>"$.company_name_seller" AS company_name_seller,
                (
                    SELECT media_id
                    FROM dealer01_ddmoto.media
                    WHERE media.lot_id = bom.id
                        AND mime_type LIKE 'image%'
                        AND deleted_at IS NULL
                    ORDER BY is_primary DESC, sort_order ASC
                    LIMIT 1
                ) AS image
            FROM dealer01_ddmoto.BO_Verkopen_NL AS vl
                LEFT JOIN dealer01_ddmoto.BO_Motorfiets AS bom
                    ON bom.id = vl.kavelref
                LEFT JOIN dealer01_ddmoto.BO_Persoon AS bop
                    ON bom.persoonID = bop.persoonID
                LEFT JOIN dealer01_ddmain.dealerdirect_bedr AS bedr
                    ON bedr.bedrijvenID = vl.bedrijvenID
                LEFT JOIN dealer01_ddmain.dealerdirect_bedr_email AS bedr_email
                    ON bedr_email.bedrijvenID = bedr.bedrijvenID
                LEFT JOIN dealer01_webservices.SB_MERKEN
                    ON SB_MERKEN.merkID = bom.merk
                LEFT JOIN dealer01_webservices.SB_MODELLEN
                    ON SB_MODELLEN.modelID = bom.model
                LEFT JOIN dealer01_ddmoto.BO_Motorfiets_Opties AS opties
                    ON opties.voertuigID = bom.id
                LEFT JOIN dealer01_ddmain.backoffice_login AS bl
                    ON vl.verkoper = bl.user_id
            WHERE vl.id = :deliverId
            GROUP BY vl.id
            SQL;
    }

    /**
     * {@inheritDoc}
     */
    protected function getSaleQuery(): string
    {
        return <<<SQL
            SELECT
                bom.id AS lot_id,
                bom.site,
                bom.reference AS site_reference,
                bom.kenteken AS license_plate,
                bom.apk AS inspection_valid_until,
                SB_MERKEN.merkNaam AS make,
                SB_MODELLEN.modelNaam AS model,
                bom.uitvoering As execution,
                bom.brandstof AS fuel,
                bom.datum1 AS first_ascription,
                YEAR(bom.bouwjaar) AS construction_year,
                bom.kleur AS color,
                bom.kleur2 AS color_2,
                bom.kleur3 AS color_3,
                bom.vermogen AS power,
                bom.kmstand AS mileage,
                bom.kms AS mileage_type,
                bom.transmissie AS transmission,
                bom.onderhoudshistorie AS maintenance_history,
                bom.eigendom AS marge,
                bom.con_uiterlijk AS condition_exterior,
                bom.con_technisch AS condition_technical,
                bom.con_bandenvoor AS condition_front_tire,
                bom.con_bandenprofiel AS condition_rear_tire,
                bom.con_aandrijving AS condition_propulsion,
                bom.con_schadevrij AS damage_free,
                bom.con_opmerkingen AS internal_comments,
                bom.opmerkingenklant AS consumer_comments,
                bom.soortmotorfiets AS bodywork,
                pers.aanhef AS consumer_salutation,
                pers.achternaam AS consumer_name,
                pers.voornaam AS consumer_first_letters,
                pers.straatnaam AS consumer_street_name,
                pers.huisnummer AS consumer_house_number,
                pers.postcode AS consumer_zip_code,
                pers.woonplaats AS consumer_city,
                pers.country_id AS consumer_country,
                pers.telefoon1 AS consumer_phone,
                pers.telefoon2 AS consumer_mobile,
                pers.email AS consumer_email,
                dealerdirect_bedr.bedrijfsnaam AS company_name,
                dealerdirect_bedr.adres AS company_street_name,
                dealerdirect_bedr.huisnummer AS company_house_number,
                dealerdirect_bedr.postcode AS company_zip_code,
                dealerdirect_bedr.land AS company_country,
                dealerdirect_bedr.plaats AS company_city,
                dealerdirect_bedr.bedrijfstel AS company_phone,
                bedr_email.akkoord_email AS company_agree_email,
                bedr_email.akkoord_email2 AS company_agree_email_2,
                vl.verkoop_datum AS sales_date,
                vl.verkoop_bedrag AS sales_price,
                vl.bem_bedrag AS sales_fee,
                vl.verkoper AS sales_agent,
                vl.bedrijvenID AS company_id,
                bol.voornaam AS sales_agent_name,
                bom.ip AS ip_address,
                vl.gehaald AS pick_up,
                GROUP_CONCAT(opties.optie SEPARATOR ';') vehicle_options,
                bom.extra_data->>"$.bicycle_type" AS bicycle_type,
                bom.extra_data->>"$.gears" AS gears,
                bom.extra_data->>"$.engine_position" AS engine_position,
                bom.extra_data->>"$.battery_wattage" AS battery_wattage,
                bom.extra_data->>"$.condition_battery" AS condition_battery,
                bom.extra_data->>"$.condition" AS `condition_general`,
                bom.extra_data->>"$.checklist_id" AS checklist_id,
                bom.extra_data->>"$.VIN" AS vin,
                bom.extra_data->>"$.wacht_op_keuring" AS wok_notification,
                bom.extra_data->>"$.tenaamstellen_mogelijk" AS registration_possible,
                vl.extra_data->>"$.company_name_seller" AS company_name_seller
            FROM dealer01_ddmoto.BO_Verkopen_NL as vl
                LEFT JOIN dealer01_ddmoto.BO_Motorfiets AS bom
                    ON vl.kavelref = bom.id
                LEFT JOIN dealer01_ddmoto.BO_Persoon AS pers
                    ON bom.persoonID = pers.persoonID
                LEFT JOIN dealer01_webservices.SB_MERKEN
                    ON bom.merk = SB_MERKEN.merkID
                LEFT JOIN dealer01_webservices.SB_MODELLEN
                    ON bom.model = SB_MODELLEN.modelID
                LEFT JOIN dealer01_ddmain.dealerdirect_bedr
                    ON vl.bedrijvenID = dealerdirect_bedr.bedrijvenID
                LEFT JOIN dealer01_ddmoto.BO_Motorfiets_Opties AS opties
                    ON opties.voertuigID = bom.id
                LEFT JOIN dealer01_ddmain.backoffice_login AS bol
                    ON vl.verkoper = bol.user_id
                LEFT JOIN dealer01_ddmain.dealerdirect_bedr_email AS bedr_email
                    ON bedr_email.bedrijvenID = dealerdirect_bedr.bedrijvenID
            WHERE vl.id = :deliverId
            GROUP BY vl.id
            SQL;
    }
}
