<?php

class Booking {

  const STATUS_ERROR = -1;
  const STATUS_INIT = 1;
  const STATUS_BOOKED = 2;
  const STATUS_CANCELED = 3;
  const STATUS_VENDOR_CANCELED = 4;
  const STATUS_VEHICLE_ARRIVED = 5;
  const STATUS_COMPLETED = 6;
  const STATUS_IN_PROGRESS = 7;
  const STATUS_DISPATCHED = 8;
  const STATUS_LOCATION_UPDATE = 9;

/*
CREATE TABLE booking (
 id serial not null constraint booking_pkey primary key,
 quote_id bigint not null,
 provider_booking_ref varchar(200) not null,
 details json,
 created timestamp default now(),
 updated timestamp,
 completed timestamp,
 status smallint default 0,
 message text,
 cost    numeric   default 0
);

alter table booking
 add constraint booking_quotes_id_fk
 foreign key (quote_id) references quotes;


CREATE TABLE booking_details (
 id serial not null constraint booking_details_pkey primary key,
 booking_id bigint not null,
 action varchar(64),
 details json,
 created timestamp default now(),
 message text,
 request json
);

alter table booking_details
    add constraint booking_details_booking_id_fk
        foreign key (booking_id) references booking;*/

	public static function getById($db, $id) {
		syslog( LOG_WARNING, "Booking::getById(\$db, $id)" );
		Logger::debug( "Booking::getById(\$db, $id)" );
		$result = [];
//		$q = "SELECT * FROM booking WHERE id=${id}";
    $q = "SELECT b.*, 
                 a_s.address as location_start, a_s.latitude as location_start_lat, a_s.longitude as location_start_lng, 
                 a_e.address as location_end, a_e.latitude as location_end_lat, a_e.longitude as location_end_lng,
                 a_s.country as location_country
          FROM booking b
          LEFT JOIN quotes q on b.quote_id = q.id
          LEFT JOIN address a_s on q.location_start_id = a_s.id 
          LEFT JOIN address a_e on q.location_end_id = a_e.id 
          WHERE b.id=${id}";
		$r      = pg_query( $db, $q );
		//syslog(LOG_WARNING,$q);
		if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
			$result = $f;
		}
		return $result;
	}

  public static function getAll($db, $params = []) {
    syslog( LOG_WARNING, "Booking::getAll(\$db, $params)" );
    Logger::debug( "Booking::getAll(\$db, $params)" );
    $result = [];
//    $q = "SELECT * FROM booking ";
    $q = "SELECT b.*, 
                 a.address as location_end, a.latitude as location_end_lat, a.longitude as location_end_lng,
                 a.country as location_country 
          FROM booking b
          LEFT JOIN quotes q on b.quote_id = q.id
          LEFT JOIN address a on q.location_end_id = a.id ";
    if (count($params)) {
      if (array_key_exists('active', $params) && (bool)$params['active'] == true) {
        $q .= " WHERE b.status = ". Booking::STATUS_BOOKED. " OR b.status = ". Booking::STATUS_IN_PROGRESS. " OR b.status = ". Booking::STATUS_DISPATCHED ;
      } else if (array_key_exists('status', $params) && isset($params['status'])) {
        //$statuses = explode(',', $params['status']);
        $q .= " WHERE b.status IN(". $params['status'] .")";
      }
      if (array_key_exists('limit', $params) && array_key_exists('offset', $params)) {
        $limit = $params['limit'];
        $offset = $params['offset'];
        $q .= " ORDER BY created DESC LIMIT ${limit} OFFSET ${offset} ";
      } else {
        $q .= " ORDER BY created DESC  LIMIT 10 OFFSET 0";
      }
    }
    $r = pg_query( $db, $q );
    $result = [];
    if ( $r && pg_num_rows( $r ) ) {
      while ($row = pg_fetch_assoc($r)) {
        array_push($result, $row);
      }
    }
    return $result;
  }

	public static function getByBookingRef($db, $bookingRef) {
		syslog( LOG_WARNING, "Booking::getByBookingRef(\$db, $bookingRef)" );
		Logger::debug( "Booking::getById(\$db, $bookingRef)" );
		$result = [];
		$q = "SELECT * FROM booking WHERE provider_booking_ref='${bookingRef}'";
		$r      = pg_query( $db, $q );
		//syslog(LOG_WARNING,$q);
		if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
			$result = $f;
		}
		return $result;
	}


  public static function getByMemberId($db, $id, $params = []) {
    syslog( LOG_WARNING, "Booking::getByMemberId(\$db, $id)" );
    Logger::debug( "Booking::getByMemberId(\$db, $id)" );
    $result = [];
//    $q = "SELECT * FROM booking WHERE member_id=${id} ";
    $q = "SELECT b.*, 
                 a.address as location_end, a.latitude as location_end_lat, a.longitude as location_end_lng,
                 a.country as location_country 
          FROM booking b
          LEFT JOIN quotes q on b.quote_id = q.id
          LEFT JOIN address a on q.location_end_id = a.id
          WHERE b.member_id=${id}";

    if (count($params)) {
      if (array_key_exists('active', $params) && (bool)$params['active'] == true) {
        $q .= " AND b.status = ". Booking::STATUS_BOOKED. " OR b.status = ". Booking::STATUS_IN_PROGRESS. " OR b.status = ". Booking::STATUS_DISPATCHED ;
      } else if (array_key_exists('status', $params) && isset($params['status'])) {
        //$statuses = explode(',', $params['status']);
        $q .= " AND b.status IN(". $params['status'] .")";
      }

      if (array_key_exists('limit', $params) && array_key_exists('offset', $params)) {
        $limit = $params['limit'];
        $offset = $params['offset'];
        $q .= " ORDER BY created DESC  LIMIT ${limit} OFFSET ${offset} ";
      } else {
        $q .= " ORDER BY created DESC  LIMIT 10 OFFSET 0";
      }
    }

    $r = pg_query( $db, $q );
    $result = [];
    if ( $r && pg_num_rows( $r ) ) {
      while ($row = pg_fetch_assoc($r)) {
        array_push($result, $row);
      }
    }
    return $result;
  }

  public static function getLastNotCompletedBookingForMember($db, $member_id) {
    syslog( LOG_WARNING, "Booking::getLastNotCompletedBookingForMember(\$db, $member_id)" );
    Logger::debug( "Booking::getLastNotCompletedBookingForMember(\$db, $member_id)" );
    $result = [];
    $q = "SELECT * FROM booking WHERE member_id=${member_id} AND completed IS NULL AND status = ". Booking::STATUS_BOOKED ." AND cost > 0";
    $r      = pg_query( $db, $q );
    //syslog(LOG_WARNING,$q);
    if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
      $result = $f;
    }
    return $result;
  }

	public static function create($db, $data) {
		syslog(LOG_WARNING,'Booking::create($db, $data)');

		$quote_id = $data["quote_id"];
		$provider_booking_ref = $data["provider_booking_ref"];
		$details = $data["details"];
		$status = (int)$data["status"];
		$memberId = (int)$data["member_id"];
		$cost = (int)$data["cost"];
		$q = "INSERT INTO booking(quote_id, provider_booking_ref, details, status, member_id, cost) VALUES (${quote_id}, '${provider_booking_ref}', '${details}', ${status}, ${memberId}, ${cost}) RETURNING id";
		$log = [
			'message' => 'Insert Booking',
			'data' =>$data,
			'query' =>$q
		];
		Logger::debug($log);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return [self::getById($db, $f["id"]),NULL];
		}
		syslog(LOG_WARNING,pg_last_error($db));
		return [NULL,pg_last_error($db)];
	}

	public static function update($db, $data) {
		syslog(LOG_WARNING,'Booking::update(): '.json_encode($data));
		$q = self::buildUpdateSQL($data);
		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);
		$log = [
			'affected_row' => pg_affected_rows($r),
			'message' => 'Update Booking',
			'data' =>$data,
			'query' =>$q
		];
		Logger::debug($log);
		if ($r && pg_affected_rows($r)) {
			return array(self::getById($db, $data["id"]),NULL);
		}
		return array(NULL, pg_last_error($db));
	}

	public static function buildUpdateSQL($data) {
		$q = "UPDATE booking SET";
		foreach ($data as $key => $value) {
			if ($key != 'id') {
				if (is_int($value) || "now()" == $value) {
					$q .= " ${key}=${value},";
				} else {
					$q .= " ${key}='".pg_escape_string($value)."',";
				}
			}
		}
		$q = substr($q, 0, -1);
		$q .= " WHERE id=".$data["id"];
		return $q;
	}

  public static function createDetails($db, $data) {
    syslog(LOG_WARNING,'Booking::createDetails($db, $data)');

    $booking_id = $data['booking_id'];
    $action = $data['action'];
    $details = $data['details'];
    $message = $data['message'];
    $request = $data['request'];

    $q = "INSERT INTO booking_details(booking_id, action, details, message, request) VALUES (${booking_id}, '${action}', '${details}', '${message}', '${request}') RETURNING id";
    $log = [
      'message' => 'Insert Booking Details',
      'data' =>$data,
      'query' =>$q
    ];
    Logger::debug($log);
    $r = pg_query($db, $q);
    if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
      return [self::getDetailsById($db, $f["id"]),NULL];
    }
    syslog(LOG_WARNING,pg_last_error($db));
    return [NULL,pg_last_error($db)];
  }

  public static function getDetailsById($db, $id) {
    syslog( LOG_WARNING, "Booking::getDetailsById(\$db, $id)" );
    Logger::debug( "Booking::getDetailsById(\$db, $id)" );
    $result = [];
    $q = "SELECT id, booking_id, action, details, message FROM booking_details WHERE id=${id}";
    $r      = pg_query( $db, $q );
    if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
      $result = $f;
    }
    return $result;
  }

  public static function getDetailsByBookingId($db, $booking_id) {
    syslog( LOG_WARNING, "Booking::getDetailsByBookingId(\$db, $booking_id)" );
    Logger::debug( "Booking::getDetailsByBookingId(\$db, $booking_id)" );
    $result = [];
    $q = "SELECT id, booking_id, action, details, message FROM booking_details WHERE booking_id=${booking_id} ORDER BY id";
    $r      = pg_query( $db, $q );
    $result = [];
    if ( $r && pg_num_rows( $r ) ) {
      while ($row = pg_fetch_assoc($r)) {
        array_push($result, $row);
      }
    }
    return $result;
  }

  public static function getMemberBySessionId($db, $sessionId) {
    syslog( LOG_WARNING, "Booking::getMemberBySessionId(\$db, $sessionId)" );
    Logger::debug( "Booking::getMemberBySessionId(\$db, $sessionId)" );
    $result = [];
    $q = "SELECT * FROM public.members_session WHERE session = ${sessionId} LIMIT 1 OFFSET 0";
    $r      = pg_query( $db, $q );
    if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r )) {
      return [$f, NULL];
    }
      syslog(LOG_WARNING,pg_last_error($db));
      return [NULL,pg_last_error($db)];
  }


}