<?php

namespace KupShop\POSBundle\Resources\upgrade;

class UpgradePOSBundle extends \UpgradeNew
{
    public function check_createPointOfSaleTable()
    {
        return $this->checkTableExists('pos');
    }

    /** points_of_sale: create table */
    public function upgrade_createPointOfSaleTable()
    {
        sqlQuery('CREATE TABLE pos (
          id                    INT AUTO_INCREMENT NOT NULL,
          name                  VARCHAR(60)        NOT NULL,
          date_created          DATETIME           NULL,
          cash_delivery_type    INT UNSIGNED       NULL ,
          card_delivery_type    INT UNSIGNED       NULL,
          invoice_delivery_type INT UNSIGNED       NULL,
          data                  MEDIUMTEXT         NULL,
          CONSTRAINT `FK_TypeCashDelivery` FOREIGN KEY (`cash_delivery_type`) REFERENCES `delivery_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          CONSTRAINT `FK_TypeCardDelivery` FOREIGN KEY (`card_delivery_type`) REFERENCES `delivery_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          CONSTRAINT `FK_TypeInvoiceDelivery` FOREIGN KEY (`invoice_delivery_type`) REFERENCES `delivery_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          PRIMARY KEY (id)
        ) ENGINE = InnoDB');

        $this->upgradeOK();
    }

    public function check_createPosPaymentRelationTable()
    {
        return $this->checkTableExists('pos_payments_relation');
    }

    /** pos_payments: create relation table between pos and payment */
    public function upgrade_createPosPaymentRelationTable()
    {
        sqlQuery('CREATE TABLE pos_payments_relation (
          id           INT AUTO_INCREMENT NOT NULL,
          id_pos       INT  NOT NULL,
          id_payment   INT  NOT NULL,
          KEY `id_pos` (`id_pos`),
          KEY `id_payment` (`id_payment`),
          CONSTRAINT `FK_IDPos` FOREIGN KEY (`id_pos`) REFERENCES `pos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          CONSTRAINT `pos_payments_fk_2` FOREIGN KEY (`id_payment`) REFERENCES `order_payments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
          PRIMARY KEY (id)
        ) ENGINE = InnoDB');
        $this->upgradeOK();
    }

    public function check_constraintForPosOnOrderTable()
    {
        return $this->checkForeignKeyExists('orders', 'pos');
    }

    public function upgrade_constraintForPosOnOrderTable()
    {
        sqlQuery('ALTER TABLE orders MODIFY pos INTEGER NULL');
        sqlQuery('UPDATE orders o
                            LEFT JOIN order_payments op ON o.id = op.id_order
                            LEFT JOIN pos_payments_relation ppr on op.id = ppr.id_payment
                        SET pos = NULL
                            WHERE o.pos = 0 OR (o.pos = 1 AND ppr.id_pos IS NULL)');
        sqlQuery('UPDATE orders o LEFT JOIN pos p ON o.pos = p.id SET pos = NULL WHERE o.pos IS NOT NULL AND p.id IS NULL');
        sqlQuery('ALTER TABLE orders ADD CONSTRAINT pos_orders_ibfk1 FOREIGN KEY (pos) REFERENCES pos(id) ON DELETE SET NULL ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_columnCustomDeliveryType(): bool
    {
        return $this->checkColumnExists('pos', 'custom_delivery_type');
    }

    /** pos: add custom_delivery_type column and constraint to delivery_type */
    public function upgrade_columnCustomDeliveryType(): void
    {
        sqlQuery('ALTER TABLE pos ADD COLUMN custom_delivery_type INT UNSIGNED NULL AFTER invoice_delivery_type');
        sqlQuery('ALTER TABLE pos ADD CONSTRAINT FK_TypeCustomDelivery FOREIGN KEY (custom_delivery_type) REFERENCES delivery_type(id) ON DELETE SET NULL ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_deliveryTypeConstraintCardCashInvoice(): bool
    {
        return $this->checkConstraintRule('pos', 'FK_TypeCashDelivery', 'SET NULL', 'CASCADE')
            && $this->checkConstraintRule('pos', 'FK_TypeCardDelivery', 'SET NULL', 'CASCADE')
            && $this->checkConstraintRule('pos', 'FK_TypeInvoiceDelivery', 'SET NULL', 'CASCADE');
    }

    /** pos: change delivery_type constraint rule from ON DELETE CASCADE to SET NULL (cash, card, invoice) */
    public function upgrade_deliveryTypeConstraintCardCashInvoice(): void
    {
        try {
            sqlQuery('ALTER TABLE pos DROP FOREIGN KEY FK_TypeCashDelivery');
            sqlQuery('ALTER TABLE pos DROP FOREIGN KEY FK_TypeCardDelivery');
            sqlQuery('ALTER TABLE pos DROP FOREIGN KEY FK_TypeInvoiceDelivery');
        } catch (\Exception $e) {
        }

        sqlQuery('ALTER TABLE pos ADD CONSTRAINT FK_TypeCashDelivery FOREIGN KEY (cash_delivery_type) REFERENCES delivery_type(id) ON DELETE SET NULL ON UPDATE CASCADE');
        sqlQuery('ALTER TABLE pos ADD CONSTRAINT FK_TypeCardDelivery FOREIGN KEY (card_delivery_type) REFERENCES delivery_type(id) ON DELETE SET NULL ON UPDATE CASCADE');
        sqlQuery('ALTER TABLE pos ADD CONSTRAINT FK_TypeInvoiceDelivery FOREIGN KEY (invoice_delivery_type) REFERENCES delivery_type(id) ON DELETE SET NULL ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_ordersConstraintPos(): bool
    {
        return $this->checkConstraintRule('orders', 'pos_orders_ibfk1', 'SET NULL', 'CASCADE');
    }

    /** orders: change pos constraint rule from ON DELETE CASCADE to SET NULL */
    public function upgrade_ordersConstraintPos(): void
    {
        try {
            sqlQuery('ALTER TABLE orders DROP FOREIGN KEY pos_orders_ibfk1');
        } catch (\Exception $e) {
        }
        sqlQuery('ALTER TABLE orders ADD CONSTRAINT pos_orders_ibfk1 FOREIGN KEY (pos) REFERENCES pos(id) ON DELETE SET NULL ON UPDATE CASCADE');
        $this->upgradeOK();
    }
}
