<?php

namespace KupShop\OSSVatsBundle\Resources\upgrade;

class OSSVatsUpgrade extends \UpgradeNew
{
    public function check_productsCN(): bool
    {
        return $this->checkColumnExists('products', 'id_cn');
    }

    /** Add column products.id_cn */
    public function upgrade_productsCN(): void
    {
        sqlQuery('ALTER TABLE products ADD COLUMN id_cn bigint(12) unsigned null AFTER vat');
        sqlQuery('CREATE INDEX products_id_cn_index ON products (id_cn)');
        $this->upgradeOK();
    }

    public function check_vats_cns(): bool
    {
        return $this->checkTableExists('vats_cns');
    }

    /** Add table vats_cns */
    public function upgrade_vats_cns(): void
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS vats_cns (
                id_cn bigint(12) unsigned null,
                id_vat INT(11) UNSIGNED NOT NULL,
                FOREIGN KEY (id_vat)
                    REFERENCES vats(id)
                    ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
        sqlQuery('CREATE INDEX vats_cns_id_cn_index ON vats_cns (id_cn)');
        sqlQuery('CREATE INDEX vats_cns_id_vat_index ON vats_cns (id_vat)');
        sqlQuery('CREATE UNIQUE INDEX vats_cns_uindex ON vats_cns (id_cn, id_vat)');
        $this->upgradeOK();
    }

    public function check_vats_country(): bool
    {
        return $this->checkColumnExists('vats', 'id_country');
    }

    /** Add column vats.id_country */
    public function upgrade_vats_country(): void
    {
        sqlQuery('ALTER TABLE vats ADD COLUMN id_country VARCHAR(10) DEFAULT NULL AFTER id');
        sqlQuery('CREATE INDEX vats_id_country_index ON vats (id_country)');
        sqlQuery('ALTER TABLE vats
	ADD CONSTRAINT vats_countries_id_fk
		FOREIGN KEY (id_country) REFERENCES countries (id)
			ON UPDATE CASCADE ON DELETE CASCADE;');
        $this->upgradeOK();
    }

    public function check_vatsAutoManagedColumn()
    {
        return $this->checkColumnExists('vats', 'automanaged');
    }

    /** Add 'automanaged' column into table 'vats' */
    public function upgrade_vatsAutoManagedColumn()
    {
        sqlQuery('ALTER TABLE vats ADD COLUMN automanaged TINYINT(1) DEFAULT 0 NOT NULL AFTER is_default');

        $this->upgradeOK();
    }

    public function check_shared_oss_vats_categories(): bool
    {
        return $this->checkTableExists('oss_vats_categories', 'kupshop_shared');
    }

    /** Add oss_vats_categories table to kupshop_shared */
    public function upgrade_shared_oss_vats_categories(): void
    {
        sqlQuery(
            'create table kupshop_shared.oss_vats_categories
                        (
                            cnkey bigint(12) unsigned not null
                                primary key,
                            parent bigint(12) unsigned null,
                            cn varchar(10) null,
                            description text null,
                            constraint oss_vats_cn_uindex
                                unique (cn),
                            constraint oss_vats_categories_oss_vats_categories_cnkey_fk
                                foreign key (parent) references kupshop_shared.oss_vats_categories (cnkey)
                                    on update cascade on delete cascade
                        );
                        
                        create index oss_vats_cn_index
                            on kupshop_shared.oss_vats_categories (cn);
                        
                     '
        );
    }

    public function check_cn00()
    {
        return sqlNumRows(sqlQuery('SELECT cnkey FROM kupshop_shared.oss_vats_categories WHERE cn = "00";')) == 0;
    }

    /** Add cn category '00' for products that have to be without VAT */
    public function upgrade_cn00()
    {
        sqlQueryBuilder()->insert('kupshop_shared.oss_vats_categories')
            ->directValues(['cnkey' => 1, 'cn' => '00', 'description' => 'bez DPH'])
            ->execute();

        $this->upgradeOK();
    }

    public function check_VatsCnsAutomanaged()
    {
        return $this->checkColumnExists('vats_cns', 'automanaged');
    }

    /** Add 'automanaged' column into table 'vats_cns' */
    public function upgrade_VatsCnsAutomanaged()
    {
        sqlQuery('ALTER TABLE vats_cns ADD COLUMN automanaged TINYINT(1) DEFAULT 1 NOT NULL AFTER id_vat');

        $this->upgradeOK();
    }

    public function check_ExceptionsTable(): bool
    {
        return $this->checkTableExists('vats_oss_exceptions');
    }

    // TODO: jako cnkey dávát záporný číslo, předělat bigint unsigned na bigint signed

    /** OSSVatsBundle: Add exceptions table */
    public function upgrade_ExceptionsTable(): void
    {
        sqlQuery('create table vats_oss_exceptions(
                        id int auto_increment primary key,
                        id_cn BIGINT(12) NOT NULL,
                        description varchar(255) null)');

        $this->upgradeOK();
    }

    public function check_productsCNKeyType(): bool
    {
        return $this->checkColumnType('products', 'id_cn', 'bigint(12)');
    }

    public function upgrade_productsCNKeyType(): void
    {
        sqlQuery('ALTER TABLE products MODIFY id_cn BIGINT(12) NULL');
        $this->upgradeOK();
    }

    public function check_vatsCNKeyType(): bool
    {
        return $this->checkColumnType('vats_cns', 'id_cn', 'bigint(12)');
    }

    public function upgrade_vatsCNKeyType(): void
    {
        sqlQuery('ALTER TABLE vats_cns MODIFY id_cn BIGINT(12) NULL');
        $this->upgradeOK();
    }
}
