<?php

namespace Sibs;

class SibsModels
{
    // CREATE TABLES
    public static function createOrderRefTables()
    {
        $db_prefix = _DB_PREFIX_;

        $sql = "CREATE TABLE IF NOT EXISTS `sibs_order_ref`(
          `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
          `id_order` INT(10) UNSIGNED NULL,
          `id_transaction` VARCHAR(32) NOT NULL,
          `id_reference` VARCHAR(32) NOT NULL,
          `id_captured` VARCHAR(32) NULL,
          `id_refunded` VARCHAR(32) NULL,
          `payment_method` VARCHAR(50) NOT NULL,
          `payment_type` VARCHAR(50),
          `payment_status` VARCHAR(50) NOT NULL,
          `payment_date` DATE NOT NULL,
          `currency` VARCHAR(3) NOT NULL,
          `amount` decimal(17,2) NOT NULL,
          `capture_status` VARCHAR(50) NULL,
          `capture_information` LONGTEXT NULL,
          `refund_status` VARCHAR(50) NULL,
          `refund_information` LONGTEXT NULL,
          FOREIGN KEY (`id_order`) REFERENCES `{$db_prefix}orders`(`id_order`)
      )";

        return \Db::getInstance()->Execute($sql);
    }

    public static function createOrderRefLogTables()
    {
        $db_prefix = _DB_PREFIX_;

        $sql = "CREATE TABLE IF NOT EXISTS `sibs_order_ref_log`(
          `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
          `id_order` INT(10) UNSIGNED NULL,
          `id_transaction` VARCHAR(32) NOT NULL,
          `id_reference` VARCHAR(32) NOT NULL,
          `id_captured` VARCHAR(32) NULL,
          `id_refunded` VARCHAR(32) NULL,
          `payment_method` VARCHAR(50) NOT NULL,
          `payment_type` VARCHAR(50),
          `payment_status` VARCHAR(50) NOT NULL,
          `payment_date` DATE NOT NULL,
          `currency` VARCHAR(3) NOT NULL,
          `amount` decimal(17,2) NOT NULL,
          `capture_status` VARCHAR(50) NULL,
          `capture_information` LONGTEXT NULL,
          `refund_status` VARCHAR(50) NULL,
          `refund_information` LONGTEXT NULL,
          FOREIGN KEY (`id_order`) REFERENCES `{$db_prefix}orders`(`id_order`)
      )";

        return \Db::getInstance()->Execute($sql);
    }

    public static function createTokenizationTable()
    {
        $db_prefix = _DB_PREFIX_;

        $sql = "CREATE TABLE IF NOT EXISTS `sibs_card_tokens`(
          `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
          `id_customer` INT(10) UNSIGNED NOT NULL,
          `token_name` VARCHAR(100) NOT NULL,
          `token_type` VARCHAR(32) NOT NULL,
          `value` VARCHAR(100) NOT NULL,
          `masked_pan` VARCHAR(32) NOT NULL,
          `expire_date` VARCHAR(32) NOT NULL,
          `expire_date_example` VARCHAR(6) NOT NULL,
          FOREIGN KEY (`id_customer`) REFERENCES `{$db_prefix}customer`(`id_customer`),
          UNIQUE KEY `id_token_unique_key` (`id_customer`, `token_name`, `token_type`, `masked_pan`, `expire_date`)
      )";

        return \Db::getInstance()->Execute($sql);
    }

    // TABLES FUNCTIONS

    public static function getOrderSibs($id)
    {
        $query = "SELECT *
          FROM `sibs_order_ref`
          WHERE `id_order` = '$id' ";

        return \Db::getInstance()->getRow($query);
    }

    public static function getOrderSibsLog($id)
    {
        $query = "SELECT *
          FROM `sibs_order_ref_log`
          WHERE `id_order` = '$id' ORDER BY id DESC";

        return \Db::getInstance()->ExecuteS($query);
    }

    public static function isPluginVersionUpdated($actual_version)
    {
        $db_prefix = _DB_PREFIX_;

        $sqlString = "SELECT `version`
          FROM `{$db_prefix}module`
          WHERE `name` = 'sibs' ";
        $version = \Db::getInstance()->getValue($sqlString);

        if (! empty($version) && $version != $actual_version) {
            return false;
        }

        return true;
    }

    public static function updatePluginVersion($actual_version, $module_name)
    {
        $db_prefix = _DB_PREFIX_;

        $sql = "UPDATE `{$db_prefix}module`
          SET `version` = '{$actual_version}'
          WHERE `name` = '{$module_name}'
      ";

        return \Db::getInstance()->execute($sql);
    }

    public static function getConfigurationId($params)
    {
        $db_prefix = _DB_PREFIX_;

        $statusSql = "SELECT `id_configuration`
          FROM `{$db_prefix}configuration`
          WHERE `name` = '{$params['config_key']}'
      ";

        return \Db::getInstance()->getValue($statusSql);
    }

    public static function updateOrderRefCaptureID($orderID, $captureID)
    {
        $sql = "UPDATE `sibs_order_ref`
        SET `id_captured` = '$captureID'
        WHERE `id_order` = $orderID ";
        \Db::getInstance()->execute($sql);
    }

    public static function updateOrderRefCaptureMessage($orderID, $captureMessage)
    {
        $sql = "UPDATE `sibs_order_ref`
        SET `capture_information` = '$captureMessage'
        WHERE `id_order` = $orderID ";
        \Db::getInstance()->execute($sql);
    }

    public static function updateOrderRefCaptureStatus($orderID, $captureStatus)
    {
        $sql = "UPDATE `sibs_order_ref`
        SET `capture_status` = '$captureStatus'
        WHERE `id_order` = $orderID ";
        \Db::getInstance()->execute($sql);
    }

    public static function updateOrderRefRefundID($orderID, $refundID)
    {
        $sql = "UPDATE `sibs_order_ref`
        SET `id_refunded` = '$refundID'
        WHERE `id_order` = $orderID ";
        \Db::getInstance()->execute($sql);
    }

    public static function updateOrderRefRefundMessage($orderID, $refundMessage)
    {
        $sql = "UPDATE `sibs_order_ref`
        SET `refund_information` = '$refundMessage'
        WHERE `id_order` = $orderID ";
        \Db::getInstance()->execute($sql);
    }

    public static function updateOrderRefRefundStatus($orderID, $refundStatus)
    {
        $sql = "UPDATE `sibs_order_ref`
        SET `refund_status` = '$refundStatus'
        WHERE `id_order` = $orderID ";
        \Db::getInstance()->execute($sql);
    }

    public static function updateTransLogId($orderID, $transactionID)
    {
        $sql = "UPDATE `sibs_order_ref`
      SET `id_order` = '$orderID'
      WHERE `id_transaction` = '$transactionID' ";

        \Db::getInstance()->execute($sql);
    }

    public static function saveTransactionLog($cartID, $paymentMethod, $resultJson)
    {
        $date = date('ymd');

        $id_cart        = $date . $cartID;
        $id_transaction = $resultJson['transactionID'];
        $paymentType    = $resultJson['paymentType'];
        $paymentStatus  = $resultJson['paymentStatus'];
        $currency       = $resultJson['amount']['currency'];
        $value          = $resultJson['amount']['value'];

        $sql = "INSERT INTO `sibs_order_ref` (
        `id_transaction`, `id_reference`, `payment_method`,
        `payment_type`, `payment_status`, `currency`, `amount`,
        `payment_date`
      ) VALUES (
        '$id_transaction', '$id_cart', '$paymentMethod',
        '$paymentType', '$paymentStatus', '$currency', '$value',
        '$date'
      ) ";

        \Db::getInstance()->execute($sql);
    }

    public static function saveTransactionRefundLog($cartID, $paymentMethod, $resultJson)
    {
        $date = date('ymd');

        $orderID        = $cartID;
        $id_cart        = $date . $cartID;
        $id_transaction = $resultJson['transactionID'];
        $paymentType    = $resultJson['paymentType'];
        $paymentStatus  = $resultJson['paymentStatus'];
        $currency       = $resultJson['amount']['currency'];
        $value          = $resultJson['amount']['value'];

        $sql = "INSERT INTO `sibs_order_ref_log` (
        `id_transaction`, `id_reference`, `payment_method`,
        `payment_type`, `payment_status`, `currency`, `amount`,
        `payment_date`,`id_order`
      ) VALUES (
        '$id_transaction', '$id_cart', '$paymentMethod',
        '$paymentType', '$paymentStatus', '$currency', '$value',
        '$date', '$orderID'
      ) ";

        \Db::getInstance()->execute($sql);
    }

    public static function getRefundStatus($originalTransactionID, $attributeIdField = 'id_transaction')
    {
        $sqlString = "SELECT `refund_status`
        FROM `sibs_order_ref`
        WHERE `$attributeIdField` = '$originalTransactionID' ";

        return \Db::getInstance()->getValue($sqlString);
    }

    public static function getOrderIdByCustomAttribute($originalTransactionId, $attributeIdField = 'id_transaction')
    {
        $sqlString = "SELECT `id_order`
      FROM `sibs_order_ref`
      WHERE `$attributeIdField` = '$originalTransactionId' ";

        return (int) \Db::getInstance()->getValue($sqlString);
    }

    public static function getCardTokensByCustomer($customerId)
    {
        $sqlString = "SELECT `token_type`, `value`
      FROM `sibs_card_tokens`
      WHERE `id_customer` = '$customerId' ";

        return \Db::getInstance()->ExecuteS($sqlString);
    }

    public static function saveToken($customerID, $token)
    {
        SibsLog::info('start saveToken()');

        $id_customer       = $customerID;
        $tokenName         = isset($token['tokenName']) ? $token['tokenName'] : '';
        $tokenType         = isset($token['tokenType']) ? $token['tokenType'] : '';
        $value             = isset($token['value']) ? $token['value'] : '';
        $maskedPAN         = isset($token['maskedPAN']) ? $token['maskedPAN'] : '';
        $expireDate        = isset($token['expireDate']) ? $token['expireDate'] : '';
        $expireDateExample = 'PL';

        $sql = "INSERT INTO `sibs_card_tokens` (
        `id_customer`, `token_name`, `token_type`,
        `value`, `masked_pan`, `expire_date`,
        `expire_date_example`
      ) VALUES (
        '$id_customer', '$tokenName', '$tokenType',
        '$value', '$maskedPAN', '$expireDate',
        '$expireDateExample'
      ) ON DUPLICATE KEY UPDATE
      `token_name` = IF('$tokenName' <> '', '$tokenName', `token_name`),
      `token_type` = IF('$tokenType' <> '', '$tokenType', `token_type`),
      `value` = IF('$value' <> '', '$value', `value`),
      `masked_pan` = IF('$maskedPAN' <> '', '$maskedPAN', `masked_pan`),
      `expire_date` = IF('$expireDate' <> '', '$expireDate', `expire_date`),
      `expire_date_example` = IF('$expireDateExample' <> '', '$expireDateExample', `expire_date_example`) ";

        if (! \Db::getInstance()->execute($sql)) {
            throw new SibsException('ERROR_SAVE_TOKEN');
        }

        SibsLog::info('end saveToken()');
    }

    public static function getActiveEmployees()
    {
        $db_prefix = _DB_PREFIX_;

        $sql = "SELECT *
        FROM `{$db_prefix}employee`
        WHERE `id_profile` = 1
            AND `active` = 1 ";

        return \Db::getInstance()->ExecuteS($sql);
    }

    public static function getPhoneNumber($customerID)
    {
        $db_prefix = _DB_PREFIX_;

        $sqlQuery = "SELECT `phone`
      FROM `{$db_prefix}address`
      WHERE `id_customer` = '$customerID'
          AND `deleted` = 0 ";
        $phoneNumber = \Db::getInstance()->getValue($sqlQuery);

        return $phoneNumber;
    }
}
