<?php
  class FCCDatabaseRepository extends ModelRepository
  {
    private static $_singletonInstance = null;

    public function __construct()
    {
      parent::__construct('FCCDatabase');
    }

    /**
     * Returnes an initiated PacketOgnRepository
     *
     * @return FCCDatabaseRepository
     */
    public static function getInstance()
    {
      if (self::$_singletonInstance === null) {
          self::$_singletonInstance = new FCCDatabaseRepository();
      }

      return self::$_singletonInstance;
    }


    /**
     * Get object by id
     *
     * @param  string $callsign (Callsign of the station)
     * @return FCCDatabase
     */
    public function getObjectByCallsign($callsign = null)
    {
        if (is_null($callsign) || empty($callsign))
        {
            return new FCCDatabase(null);
        }

        $sql =
          'SELECT en.*, en.unique_system_identifier AS id, am.operator_class, am.group_code, am.region_code, am.previous_callsign, am.previous_operator_class, hd.license_status, hd.radio_service_code, hd.grant_date, hd.expired_date, hd.cancellation_date
            FROM fcc_en en
            LEFT JOIN fcc_am am ON (en.unique_system_identifier = am.unique_system_identifier)
            LEFT JOIN fcc_hd hd ON (en.unique_system_identifier = hd.unique_system_identifier)
            WHERE en.call_sign = ?
            ORDER BY en.unique_system_identifier DESC
            LIMIT 1';

        return $this->getObjectFromSql($sql, [$callsign]);
    }


    /**
     * Get object list by query string
     *
     * @param  int   $callsign
     * @param  int   $limit
     * @param  int   $offset
     * @return array
     */
    public function getObjectListByQueryString($callsign, $limit=50, $offset=0)
    {
      $sql =
        'SELECT en.*, en.unique_system_identifier AS id, am.operator_class, am.group_code, am.region_code, am.previous_callsign, am.previous_operator_class, hd.license_status, hd.radio_service_code, hd.grant_date, hd.expired_date, hd.cancellation_date
          FROM fcc_en en
          LEFT JOIN fcc_am am ON (en.unique_system_identifier = am.unique_system_identifier)
          LEFT JOIN fcc_hd hd ON (en.unique_system_identifier = hd.unique_system_identifier)
          WHERE en.call_sign ILIKE ?
          ORDER BY en.unique_system_identifier DESC
          LIMIT ? OFFSET ?';

      $pdo = PDOConnection::getInstance();
      $stmt = $pdo->prepare($sql);
    	$stmt->bindValue(1, "$callsign%");
      $stmt->bindValue(2, $limit);
      $stmt->bindValue(3, $offset);
      $stmt->execute();

      $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
      if (is_array($records) && !empty($records)) {
          return $this->_getObjectListFromRecords($records);
      }

      // No object found, return empty array
      return [];
    }

    /**
     * Get number of objects in the object list by query string
     *
     * @param  int   $callsign
     * @return array
     */
    public function getNumberOfStationsByQueryString($callsign)
    {
      $sql =
        'SELECT COUNT(*)
          FROM fcc_en
          WHERE call_sign ILIKE ?';

      $pdo = PDOConnection::getInstance();
      $stmt = $pdo->prepare($sql);
    	$stmt->bindValue(1, "$callsign%");
      $stmt->execute();

      return $stmt->fetchColumn();
    }
  }
?>
