<?php

namespace Dealerdirect\Agreements\Models;

abstract class CarModel extends AbstractModel
{
    /**
     * {@inheritDoc}
     */
    protected function getPurchaseQuery(): string
    {
        return <<<SQL
            SELECT
                vb.id AS lot_id,
                vb.reference AS site_reference,
                vb.aanhef AS consumer_salutation,
                vb.voorletters AS consumer_first_letters,
                vb.naam AS consumer_name,
                vb.straatnaam AS consumer_street_name,
                vb.huisnummer AS consumer_house_number,
                CONCAT(vb.postcodec, ' ', vb.postcodel) AS consumer_zip_code,
                vb.woonplaats AS consumer_city,
                vb.land AS consumer_country,
                vb.telefoonnummer AS consumer_phone,
                vb.gsmnummer AS consumer_mobile,
                vb.email AS consumer_email,
                vb.kenteken AS license_plate,
                SB_MERKEN.merkNaam AS make,
                SB_MODELLEN.modelNaam AS model,
                vb.uitvoering AS execution,
                vl.gehaald AS pick_up,
                vb.carrosserie AS bodywork,
                vb.bouwjaar AS construction_year,
                vb.kmstand AS mileage,
                vb.motorinhoud AS engine_capacity,
                vb.brandstof AS fuel,
                vb.vermogen AS power,
                vb.vermogen2 AS power_2,
                vb.versnellingsbak AS transmission,
                vb.nieuwoudmodel AS old_new_model,
                vb.vervalapk AS inspection_valid_until,
                vb.kleur AS color,
                vb.laksoort AS paint_type,
                vb.bekleding AS upholstery,
                vb.con_rijdbaar AS drivable,
                vb.con_carrosserie AS condition_exterior,
                vb.con_interieur AS condition_interior,
                vb.con_technisch AS condition_technical,
                vb.con_bandenprofiel AS condition_tire_profile,
                vb.con_schadevrij AS damage_free,
                vb.con_opmerkingen AS internal_comments,
                vb.opmerkingklant AS consumer_comments,
                vb.optielijst AS vehicle_options,
                vb.onderhboekje AS maintenance_history,
                vb.marge,
                vbr.wachtenopkeuren AS wok_notification,
                vbr.extra_data->>"$.tenaamstellen_mogelijk" AS registration_possible,
                vb.extra_data->>"$.checklist_id" AS checklist_id,
                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.pri_lang AS company_language,
                bedr_email.akkoord_email AS company_agree_email,
                bedr_email.akkoord_email2 AS company_agree_email_2,
                IF(bl.user_id IS NULL, 'Dealerdirect', bl.voornaam) AS sales_agent_name,
                vl.verkoper AS sales_agent,
                vl.extra_data->>"$.company_name_seller" AS company_name_seller,
                COALESCE(bb.bod, vl.verkoop_bedrag) AS sales_price,
                COALESCE(bb.comm, vl.bem_bedrag) AS sales_fee,
                vl.verkoop_datum AS sales_date,
                (
                    SELECT media_id
                    FROM dealer01_ddmain.media
                    WHERE media.lot_id = vb.id
                        AND mime_type LIKE 'image%'
                        AND deleted_at IS NULL
                    ORDER BY is_primary DESC, sort_order ASC
                    LIMIT 1
                ) AS image
            FROM {$this->getTable()} vl
                INNER JOIN dealer01_ddmain.verzamel_base vb
                    ON vl.kavelref = vb.id
                INNER JOIN dealer01_ddmain.dealerdirect_bedr bedr
                    ON vl.bedrijvenID = bedr.bedrijvenID
                LEFT JOIN dealer01_ddmain.biedingen_base AS bb
                    ON bb.id = vl.bid_id
                LEFT JOIN dealer01_ddmain.verzamel_base_rdwinfo AS vbr
                    ON vb.id = vbr.kavelref
                LEFT JOIN dealer01_webservices.SB_MERKEN
                    ON vb.automerk = SB_MERKEN.merkID
                LEFT JOIN dealer01_webservices.SB_MODELLEN
                    ON vb.autotype = SB_MODELLEN.modelID
                LEFT JOIN dealer01_ddmain.dealerdirect_bedr_email bedr_email
                    ON bedr_email.bedrijvenID = bedr.bedrijvenID
                LEFT JOIN dealer01_ddmain.backoffice_login bl
                    ON bl.user_id = vl.verkoper
            WHERE
                vl.id = :deliverId
            SQL;
    }

    /**
     * {@inheritDoc}
     */
    protected function getSaleQuery(): string
    {
        return <<<SQL
            SELECT
                vb.id AS lot_id,
                vb.reference AS site_reference,
                vb.voorletters AS consumer_first_letters,
                vb.naam AS consumer_name,
                vb.aanhef AS consumer_salutation,
                vb.straatnaam AS consumer_street_name,
                vb.huisnummer AS consumer_house_number,
                CONCAT(vb.postcodec, ' ', vb.postcodel) AS consumer_zip_code,
                vb.woonplaats AS consumer_city,
                vb.land AS consumer_country,
                vb.telefoonnummer AS consumer_phone,
                vb.gsmnummer AS consumer_mobile,
                vb.email AS consumer_email,
                SB_MERKEN.merkNaam AS make,
                SB_MODELLEN.modelNaam AS model,
                vb.kenteken AS license_plate,
                vb.carrosserie AS bodywork,
                vb.uitvoering AS execution,
                vb.motorinhoud AS engine_capacity,
                vb.brandstof AS fuel,
                vb.vermogen AS power,
                vb.vermogen2 AS power_2,
                vb.versnellingsbak AS transmission,
                vb.nieuwoudmodel AS old_new_model,
                vb.kmstand AS mileage,
                vb.bouwjaar AS construction_year,
                vb.vervalapk AS inspection_valid_until,
                vb.kleur AS color,
                vb.laksoort AS paint_type,
                vb.bekleding AS upholstery,
                vb.con_rijdbaar AS drivable,
                vb.con_carrosserie AS condition_exterior,
                vb.con_interieur AS condition_interior,
                vb.con_technisch AS condition_technical,
                vb.con_bandenprofiel AS condition_tire_profile,
                vb.con_schadevrij AS damage_free,
                vb.con_opmerkingen AS internal_comments,
                vb.opmerkingklant AS consumer_comments,
                vb.optielijst AS vehicle_options,
                vb.onderhboekje AS maintenance_history,
                vl.gehaald AS pick_up,
                vb.marge,
                vb.datum1 AS first_ascription, #eersteToelating
                vb.extra_data->>"$.checklist_id" AS checklist_id,
                vb.extra_data->>"$.VIN" AS vin,
                vbr.wachtenopkeuren AS wok_notification,
                vbr.extra_data->>"$.tenaamstellen_mogelijk" AS registration_possible,
                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,
                vl.verkoop_datum AS sales_date,
                vl.verkoper AS sales_agent,
                bol.voornaam AS sales_agent_name,
                vl.verkoop_bedrag AS sales_price,
                vl.bem_bedrag AS sales_fee,
                vl.bedrijvenID AS company_id,
                vl.extra_data->>"$.company_name_seller" AS company_name_seller,
                bedr_email.akkoord_email AS company_agree_email,
                bedr_email.akkoord_email2 AS company_agree_email_2
            FROM {$this->getTable()} AS vl
                LEFT JOIN dealer01_ddmain.verzamel_base AS vb
                    ON vl.kavelref = vb.id
                LEFT JOIN dealer01_ddmain.verzamel_base_rdwinfo AS vbr
                    ON vb.id = vbr.kavelref
                LEFT JOIN dealer01_ddmain.dealerdirect_bedr AS bedr
                    ON vl.bedrijvenID = bedr.bedrijvenID
                LEFT JOIN dealer01_ddmain.dealerdirect_bedr_email AS bedr_email
                    ON bedr_email.bedrijvenID = bedr.bedrijvenID
                LEFT JOIN dealer01_webservices.SB_MERKEN
                    ON vb.automerk = SB_MERKEN.merkID
                LEFT JOIN dealer01_webservices.SB_MODELLEN
                    ON vb.autotype = SB_MODELLEN.modelID
                LEFT JOIN dealer01_ddmain.backoffice_login AS bol
                    ON bol.user_id = vl.verkoper
            WHERE vl.id = :deliverId
            LIMIT 1
            SQL;
    }

    abstract protected function getTable(): string;
}
