<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatePatientsByDoctorsView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement($this->dropView());
        DB::statement($this->createView());
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement($this->dropView());
    }

    private function createView()
    {
        return <<<SQL
            CREATE VIEW patients_by_doctors AS 
            SELECT 
                appointments.id AS appointment_id,
                patients.id AS patient_id,
                patients.email AS patientEmail,
                users.email AS usersEmail,
                users.id AS user_id,
                patients.identification AS identification,
                patients.firstname AS firstname,
                patients.lastname AS lastname,
                patients.age AS age,
                patients.gender AS gender,
                patients.phone AS phone,
                patients.birthday AS birthday,
                patients.blood_type AS blood_type,
                patients.address AS address,
                patients.created_at AS patientCreatedAt,
                patients.updated_at AS patientUpdatedAt 
            FROM patients 
            LEFT JOIN appointments ON appointments.patients_id = patients.id 
            LEFT JOIN users ON appointments.users_id = users.id
        SQL;
    }

    private function dropView()
    {
        return <<<SQL
            DROP VIEW IF EXISTS patients_by_doctors
        SQL;
    }
}
