<?php

namespace Mtc\Modules\DoctorSurgery\Classes;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Facades\DB;

class DoctorSurgery extends Model
{
    use SoftDeletes;

    protected $table = 'doctor_surgeries';

    protected $fillable = [
        "area_team_name",
        "ccg_name",
        "ods_code",
        "practice_name",
        "address_1",
        "address_2",
        "postcode",
        "latitude",
        "longitude",
        "status",
        "go_live",
        "patient_list_size",
        "percent_estimate",
        "epsr2_estimate"
    ];

    protected $dates = [
        'deleted_at'
    ];


    public static function importSurgeries()
    {
        $result = validate_csv_upload('import');
        $errors = $result['errors'];
        $handle = $result['handle'];
        $success = '';
        if (empty($errors)) {

            $first = true;
            $columns = [];

            while(($data = fgetcsv($handle, null, ',')) !== false) {
                if ($first) {
                    $columns = (array)$data;
                    $first = false;

                } else {
                    $row = [];
                    // Map column names with data
                    foreach ($columns as $k => $column_name) {
                        $row[$column_name] = $data[$k];
                    }

                    $doctor_surgery = new DoctorSurgery();

                    $doctor_surgery->ods_code = trim($row['ODS']);
                    $doctor_surgery->practice_name = trim($row['Practice Name']);
                    $doctor_surgery->address_1 = trim($row['Address Line 1']);
                    $doctor_surgery->address_2 = trim($row['Address Line 2']);
                    $doctor_surgery->address_3 = trim($row['Address Line 3']);
                    $doctor_surgery->city = trim($row['City']);
                    $doctor_surgery->postcode = trim($row['Postcode']);
                    $doctor_surgery->phone = trim($row['Telephone Number']);

                    // Sometimes the City is in the Address 3 or Address 2 field
                    if (empty($doctor_surgery->city)) {
                        if (!empty($doctor_surgery->address_3)) {
                            $doctor_surgery->city = $doctor_surgery->address_3;
                            $doctor_surgery->address_3 = '';
                        } elseif (!empty($doctor_surgery->address_2)) {
                            $doctor_surgery->city = $doctor_surgery->address_2;
                            $doctor_surgery->address_2 = '';
                        }

                    }

                    // For existing doctor surgeries we only update phone
                    if ($existing_surgery = self::query()->where('ods_code', '=', $doctor_surgery->ods_code)->first()) {
                        $existing_surgery->address_1 = $doctor_surgery->address_1;
                        $existing_surgery->address_2 = $doctor_surgery->address_2;
                        $existing_surgery->address_3 = $doctor_surgery->address_3;
                        $existing_surgery->city = $doctor_surgery->city;
                        $existing_surgery->postcode = $doctor_surgery->postcode;
                        $existing_surgery->phone = $doctor_surgery->phone;
                        $existing_surgery->save();
                        continue;
                    }

                    $doctor_surgery->save();

                }
            }

            $success = 'Doctor surgeries imported successfully!';
        }

        return [
            'errors' => $errors,
            'success' => $success,
        ];
    }


    public static function getByDistanceFromLatLong($lat, $long, $distance_miles = 20, $limit = 10)
    {
        // Haversine algorithm from: [http://mysql.rjweb.org/doc.php/find_nearest_in_mysql#gcdistdeg]
        // 'Degrees in, Degrees out.  For conversion: 69.172 mi/deg or 111.325 km/deg'
        $sql_drop_function = '
            DROP FUNCTION IF EXISTS `GCDistDeg`
        ';

        $sql_create_function = '
            CREATE FUNCTION `GCDistDeg` (
                    _lat1 DOUBLE,
                    _lon1 DOUBLE,
                    _lat2 DOUBLE,
                    _lon2 DOUBLE
                ) RETURNS double
                DETERMINISTIC
                SQL SECURITY INVOKER

            BEGIN

                DECLARE _deg2rad DOUBLE DEFAULT PI()/180;
                DECLARE _rlat1 DOUBLE DEFAULT _deg2rad * _lat1;
                DECLARE _rlat2 DOUBLE DEFAULT _deg2rad * _lat2;

                DECLARE _rlond DOUBLE DEFAULT _deg2rad * (_lon1 - _lon2);
                DECLARE _m     DOUBLE DEFAULT COS(_rlat2);
                DECLARE _x     DOUBLE DEFAULT COS(_rlat1) - _m * COS(_rlond);
                DECLARE _y     DOUBLE DEFAULT               _m * SIN(_rlond);
                DECLARE _z     DOUBLE DEFAULT SIN(_rlat1) - SIN(_rlat2);
                DECLARE _n     DOUBLE DEFAULT SQRT(
                                    _x * _x +
                                    _y * _y +
                                    _z * _z    );
                RETURN  2 * ASIN(_n / 2) / _deg2rad;
            END
        ';

        DB::unprepared($sql_drop_function);
        DB::unprepared($sql_create_function);

        $distance_deg = $distance_miles / 69.172;

        $surgeries = DB::table('doctor_surgeries')
            ->addSelect('*')
            ->selectRaw("GCDistDeg(latitude, longitude, ?, ?) * 69.172 AS distance", [ $lat, $long ])
            ->whereRaw("GCDistDeg(latitude, longitude, ?, ?) < ?", [ $lat, $long, $distance_deg ])
            ->whereRaw("LENGTH(latitude) > 0")
            ->whereRaw("LENGTH(longitude) > 0")
            ->orderByRaw("GCDistDeg(latitude, longitude, ?, ?)", [ $lat, $long ])
            ->limit($limit)
            ->get()
        ;

        return $surgeries;
    }

}
