<?php

namespace Opentalent\OtCore\Http;

use Opentalent\OtCore\Exception\ApiRequestException;
use PDO;
use TYPO3\CMS\Core\Http\JsonResponse;
use TYPO3\CMS\Core\Http\ServerRequest;

/**
 * Actions for Http API calls
 *
 * @package Opentalent\OtCore\Http
 */
class ApiController
{
    /**
     * Return a connection to the DB matching the current context
     *
     * @param ServerRequest $request
     * @return PDO
     */
    private function getCnn(ServerRequest $request) {
        if ($_SERVER['HTTP_HOST'] == 'typo3' | $_SERVER['HTTP_HOST'] == 'local.sub.opentalent.fr') {
            $db_host = 'db';
        } elseif ($_SERVER['HTTP_HOST'] == 'preprod.opentalent.fr') {
            $db_host = 'localhost';
        } else {
            $db_host = 'prod-back';
        }

        $cnn = new PDO(
            "mysql:host=" . $db_host . ";dbname=opentalent",
            'dbcloner',
            'wWZ4hYcrmHLW2mUK',
            array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8')
        );
        $cnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $cnn;
    }

    /**
     * -- Target of the route 'all' --
     *
     * Get all of the Opentalent structures directly
     * from the Opentalent DB (API Platform is too slow with so many records)
     *
     * @param ServerRequest $request
     * @return JsonResponse
     */
    public function getAllStructures(ServerRequest $request)
    {
        $cnn = $this->getCnn($request);

        $stmt = $cnn->prepare(
            "SELECT o.id, o.name, o.logo_id as logoId, o.principalType, p.otherWebsite as website, a.latitude, a.longitude,
                       TRIM(BOTH ' ' FROM CONCAT(a.streetAddress, ' ', a.streetAddressSecond, ' ', a.streetAddressThird)) AS streetAddress,
                       a.postalCode, a.addressCity, c.name AS country,
                        (SELECT CONCAT(GROUP_CONCAT(DISTINCT CONCAT(tp.name)))
                        FROM organization_type_of_practices AS otp
                        LEFT JOIN TypeOfPractice AS tp ON(tp.id = otp.typeofpractice_id)
                        WHERE otp.organization_id = o.id) AS practices,
                       n1.parent_id as n1Id, net1.name as n1Name, n2.parent_id as n2Id, n3.parent_id as n3Id, n4.parent_id as n4Id, n5.parent_id as n5Id,
                       CONCAT_WS(',', n1.parent_id, n2.parent_id, n3.parent_id, n4.parent_id, n5.parent_id) as parents
                    FROM opentalent.Organization o
                        INNER JOIN opentalent.Parameters p on o.parameters_id = p.id
                        INNER JOIN (SELECT * FROM opentalent.OrganizationAddressPostal WHERE `type`='ADDRESS_HEAD_OFFICE') oa on oa.organization_id = o.id
                        INNER JOIN opentalent.AddressPostal a on oa.addressPostal_id = a.id
                        LEFT JOIN opentalent.Country c ON (c.id = a.addressCountry_id)
                        INNER JOIN (SELECT * FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n1 on n1.organization_id = o.id
                        INNER JOIN Organization net1 ON net1.id = n1.parent_id
                        LEFT JOIN (SELECT * FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n2 on n2.organization_id = n1.parent_id
                        LEFT JOIN (SELECT * FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n3 on n3.organization_id = n2.parent_id
                        LEFT JOIN (SELECT * FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n4 on n4.organization_id = n3.parent_id
                        LEFT JOIN (SELECT * FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n5 on n5.organization_id = n4.parent_id
        ;");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return new JsonResponse($data);
    }

    /**
     * -- Target of the route 'get' --
     *
     * Get a structure directly with the specific data required in the structures template
     *
     * @param ServerRequest $request
     * @return JsonResponse
     * @throws ApiRequestException
     */
    public function getStructureById(ServerRequest $request)
    {
        $params = $request->getQueryParams();
        $organizationId = $params['organization-id'];
        if (!$organizationId) {
            throw new ApiRequestException('Missing argument: organization-id');
        }
        $cnn = $this->getCnn($request);

        $stmt = $cnn->prepare(
            "SELECT o.id, o.name, o.logo_id as logoId, p.otherWebsite as website, a.latitude, a.longitude,
                       TRIM(BOTH ' ' FROM CONCAT(a.streetAddress, ' ', a.streetAddressSecond, ' ', a.streetAddressThird)) AS streetAddress,
                       a.postalCode, a.addressCity, cp.telphone, cp.email, o.facebook, o.twitter, o.instagram,
                       (SELECT CONCAT(GROUP_CONCAT(DISTINCT CONCAT(tp.name)))
                        FROM organization_type_of_practices AS otp
                        LEFT JOIN TypeOfPractice AS tp ON(tp.id = otp.typeofpractice_id)
                        WHERE otp.organization_id = o.id) AS practices,
                       n1.parent_id as n1Id, net1.name as n1Name
                    FROM opentalent.Organization o
                        INNER JOIN opentalent.Parameters p on o.parameters_id = p.id
                        INNER JOIN (SELECT * FROM opentalent.OrganizationAddressPostal WHERE `type`='ADDRESS_HEAD_OFFICE') oa on oa.organization_id = o.id
                        INNER JOIN opentalent.AddressPostal a on oa.addressPostal_id = a.id
                        INNER JOIN (SELECT * FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n1 on n1.organization_id = o.id
                        INNER JOIN Organization net1 ON net1.id = n1.parent_id
                        LEFT JOIN opentalent.organization_contactpoint ocp ON ocp.organization_id = o.id
                        LEFT JOIN (SELECT * FROM opentalent.ContactPoint WHERE `contactType`='PRINCIPAL') cp ON cp.id = ocp.contactPoint_id
                    WHERE o.id=" . $organizationId . ";"
        );
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return new JsonResponse($data);
    }
}
