<?php

/**
 * Copyright(C) 2015 Markus Bröker<broeker.markus@googlemail.com>
 *
 */

namespace bfw\database;

use bfw\Configuration;
use Logger;
use PDO;
use PDOException;

class Database implements DBInterface {
    private static $handle = null;
    private $logger;
    /**
     * @var PDO
     */
    private $pdo;

    /**
     * Database constructor.
     * @param null|array $cfg
     */
    private function __construct($cfg = null) {
        $this->logger = Logger::getLogger(__CLASS__);

        if ($cfg == null) {
            $cfg = Configuration::getDataSourceProperties();
        }

        $dsn = $cfg['dsn'];
        $config = $cfg['config'];
        $username = $cfg['username'];
        $password = $cfg['password'];

        $this->pdo = new PDO($dsn, $username, $password, $config);
    }

    /**
     * <b>Liefert das Singleton-Pattern der Datenbank-Schicht</b>
     *
     * Es existiert in einem Lauf, einem Scope, immer nur ein DB-Handle zur gleichen Zeit.
     *
     * Damit das ganze vernünftig flutscht, muss man natürlich berücksichtigen, dass ein SP state-lastig ist!
     *
     * Definition des States: Ein Abfrageergebnis stellt solange den State des SP da, bis eine neue Abfrage
     * einen neuen State erzeugt.
     *
     * @param null|array $cfg
     * @return Database
     */
    public static function getInstance($cfg = null) {
        if (self::$handle == null) {
            self::$handle = new Database($cfg);
        }

        return self::$handle;
    }

    /**
     * <b>Diese Methode löscht alle Tickets, History und Benutzer weg</b>
     *
     * Diese Methode sollte dann aufgerufen werden, wenn die Anwendung deployed wird
     *
     * Auf Deutsch: "Vor der Präsi alles weglöschen."
     *
     * @return bool
     */
    public function cleanup() {
        try {
            $this->pdo->exec("DELETE FROM `t_user` WHERE `id` > 2;");
            $this->pdo->exec("DELETE FROM `t_group` WHERE `id` > 5;");

            $this->pdo->exec("ALTER TABLE `t_user` AUTO_INCREMENT = 2;");
            $this->pdo->exec("ALTER TABLE `t_group` AUTO_INCREMENT = 5;");
        } catch (PDOException $pdoe) {
            return false;
        }

        return true;
    }

    /**
     *
     * @param string $table
     * @param array $fields
     * @param string $filename
     * @param string $seperator
     *
     * @throws InvalidArgumentException
     */
    public function csvExport($table, $fields, $filename, $seperator = ';') {
        $handle = fopen($filename, "w+");

        if ($handle == FALSE) {
            throw new InvalidArgumentException("Datei nicht gefunden: " . $filename);
        }

        $rows = $this->fetchAll(sprintf("SELECT %s FROM `%s` ORDER BY `id`", implode(", ", $fields), $table));

        fwrite($handle, sprintf("%s\n", implode($seperator, $fields)));
        foreach ($rows as $row) {
            fwrite($handle, sprintf("%s\n", implode($seperator, $row)));
        }

        fclose($handle);
    }

    /**
     * @param $table
     * @param $cond
     * @return array|null
     */
    public function fetchAll($table, $cond = 'id > 0') {
        $this->logger->info(sprintf('%s(%s, %s) ', __METHOD__, $table, $cond));

        $stmt = $this->pdo->prepare(sprintf('
            SELECT
                *
            FROM
              `%s`
            WHERE
              %s
            ', $table, $cond
        ));

        if ($stmt->execute()) {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return null;
    }

    /**
     * @param $table
     * @param $cond
     * @return array|null
     */
    public function fetch($table, $cond = 'id > 0') {
        $this->logger->info(sprintf('%s(%s, %s) ', __METHOD__, $table, $cond));

        $stmt = $this->pdo->prepare(sprintf('
            SELECT
                *
            FROM
              `%s`
            WHERE
              %s
            LIMIT 1
            ', $table, $cond
        ));

        if ($stmt->execute()) {
            return $stmt->fetch(PDO::FETCH_ASSOC);
        }

        return null;
    }

    /**
     *
     * @param string $table
     * @param string $filename
     * @param string $seperator
     *
     * @throws InvalidArgumentException
     */
    public function csvImport($table, $filename, $seperator = ';') {
        $handle = fopen($filename, "r");

        if ($handle == FALSE) {
            throw new InvalidArgumentException("Datei nicht gefunden: " . $filename);
        }

        $firstLine = trim(fgets($handle, 1024));
        $firstExplodedLine = explode($seperator, $firstLine);
        $firstImplodedLine = implode(", ", $firstExplodedLine);

        while (($line = fgets($handle, 1024)) != NULL) {
            $explodedLine = explode($seperator, trim($line));
            $row = array();
            foreach ($explodedLine as $explodedField) {
                $row[] = "'$explodedField'";
            }

            $implodedLine = implode(", ", $row);
            $this->pdo->exec(sprintf("INSERT INTO %s(id, %s) VALUES(NULL, %s);\n", $table, $firstImplodedLine, $implodedLine));
        }

        fclose($handle);
    }

    /**
     * @param $table
     * @param $id
     * @return mixed|null
     */
    public function find($table, $id) {
        $this->logger->info(sprintf('%s(%s, %d) ', __METHOD__, $table, $id));

        $stmt = $this->pdo->prepare(
            sprintf('
              SELECT
                *
              FROM
                `%s`
              WHERE
                `id` = :id
              LIMIT 1
            ', $table
            ));

        $stmt->bindParam(':id', $id);

        $status = $stmt->execute();
        if ($status) {
            return $stmt->fetch(PDO::FETCH_ASSOC);
        }

        return null;
    }

    /**
     * @param $table
     * @param bool $sys
     * @return array|null
     */
    public function findAll($table, $sys) {
        $this->logger->info(sprintf('%s(%s, %d) ', __METHOD__, $table, $sys));

        $stmt = $this->pdo->prepare(
            sprintf('
              SELECT
                *
              FROM
                `%s`
              WHERE
                `id` > :id
            ', $table
            ));

        $id = ($sys ? 0 : 1);
        $stmt->bindParam(':id', $id);

        if ($stmt->execute()) {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return null;
    }

    /**
     * @param $table
     * @param $field
     * @param $value
     * @return mixed|null
     */
    public function findByField($table, $field, $value) {
        $this->logger->info(sprintf('%s(%s, %s, %s) ', __METHOD__, $table, $field, $value));

        $stmt = $this->pdo->prepare(
            sprintf('
              SELECT
                *
              FROM
                `%s`
              WHERE
                `%s` = :value
              LIMIT 1
            ', $table, $field
            ));

        $stmt->bindParam(':value', $value);

        if ($stmt->execute()) {
            return $stmt->fetch(PDO::FETCH_ASSOC);
        }

        return null;
    }

    /**
     * @param $table
     * @param $field
     * @param $value
     * @return array|null
     */
    function findAllByField($table, $field, $value) {
        $this->logger->info(sprintf('%s(%s, %s, %s) ', __METHOD__, $table, $field, $value));

        $stmt = $this->pdo->prepare(
            sprintf('
              SELECT
                *
              FROM
                `%s`
              WHERE
                `%s` = :value
            ', $table, $field
            ));

        $stmt->bindParam(':value', $value);

        if ($stmt->execute()) {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return null;
    }

    /**
     * @param $sql
     * @return array|null
     */
    public function query($sql) {
        $this->logger->info(sprintf('%s(%s, %s) ', __METHOD__, $sql));

        $stmt = $this->pdo->query($sql);

        if ($stmt) {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return null;
    }

    /**
     * @param $sql
     * @return int
     */
    public function execute($sql) {
        return $this->pdo->exec($sql);
    }

    /**
     * @param $table
     * @param $array
     * @return int
     */
    public function persist($table, $array) {
        $this->logger->info(sprintf('%s(%s, %s) ', __METHOD__, $table, print_r($array, true)));

        $keys = array();
        foreach (array_keys($array) as $key) {
            if ($key != 'id') {
                $keys[] = sprintf("`%s`", $key);
            }
        }

        $fieldList = implode(", ", $keys);

        $values = array();
        foreach ($array as $key => $value) {
            if ($key != 'id') {
                $values[] = sprintf("'%s'", $value);
            }
        }

        $fields = implode(",", $values);

        $sql = sprintf("
            INSERT INTO `%s`
            (`id`, %s) VALUES (NULL, %s)
        ", $table, $fieldList, $fields);

        return $this->pdo->exec($sql);
    }

    /**
     * @param $table
     * @param $id
     * @param $array
     * @return int
     */
    public function store($table, $id, $array) {
        $this->logger->info(sprintf('%s(%s, %d, %s) ', __METHOD__, $table, $id, print_r($array, true)));

        $list = array();
        foreach ($array as $key => $value) {
            if ($key != 'id') {
                $list[] = sprintf("`%s` = '%s'", $key, $value);
            }
        }

        $listItems = implode(", ", $list);

        $sql = sprintf("
            UPDATE `%s`
            SET %s
            WHERE `id` = %d
        ", $table, $listItems, $id);

        return $this->pdo->exec($sql);
    }

    /**
     * @param $table
     * @param $id
     * @return int
     */
    public function delete($table, $id) {
        $this->logger->info(sprintf('%s(%s, %s) ', __METHOD__, $table, $id));

        $sql = sprintf("
            DELETE FROM `%s`
            WHERE `id` = %d;
        ", $table, $id);

        return $this->pdo->exec($sql);
    }

    public function getLastInsertedId() {
        return $this->pdo->lastInsertId();
    }
}