<?php

namespace KupShop\ContentBundle\Resources\upgrade;

use KupShop\ContentBundle\Util\SliderUtil;
use KupShop\I18nBundle\Translations\PhotosTranslation;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\System\PathFinder;
use Query\Operator;

class SlidersUpgrade extends \UpgradeNew
{
    protected $priority = 1000;

    protected function isAllowed()
    {
        return findModule(\Modules::SLIDERS);
    }

    public function check_dataColumn()
    {
        return $this->checkColumnExists('sliders', 'data');
    }

    /** sliders: add data column */
    public function upgrade_dataColumn()
    {
        sqlQuery('ALTER TABLE sliders ADD COLUMN data MEDIUMTEXT DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_SlidersImagesDataColumn()
    {
        return $this->checkColumnExists('sliders_images', 'data');
    }

    /** sliders_images: add data column */
    public function upgrade_SlidersImagesDataColumn()
    {
        sqlQuery('ALTER TABLE sliders_images ADD COLUMN data MEDIUMTEXT DEFAULT NULL');

        foreach (sqlQuery('SELECT * FROM sliders') as $slider) {
            $sliderData = [];
            $data = json_decode($slider['data'], true) ?: [];
            foreach ($data as $key => $value) {
                if (is_numeric($key)) {
                    $slideData = json_encode($value);
                    sqlQuery('UPDATE sliders_images SET data = :data WHERE id = :id_slide AND id_slider = :id_slider', [
                        'data' => $slideData,
                        'id_slide' => $key,
                        'id_slider' => $slider['id'],
                    ]);
                } else {
                    $sliderData[$key] = $value;
                }
            }

            sqlQuery('UPDATE sliders SET data = :data WHERE id = :id', [
                'id' => $slider['id'],
                'data' => empty($sliderData) ? null : json_encode($sliderData),
            ]);
        }

        $this->upgradeOK();
    }

    public function check_trigger_sliders_images()
    {
        return !$this->checkIfTriggerExists('trigger_sliders_images');
    }

    /** Delete old trigger trigger_sliders_images */
    public function upgrade_trigger_sliders_images()
    {
        sqlQuery('DROP TRIGGER trigger_sliders_images');
        $this->upgradeOK();
    }

    public function check_trigger_sliders_images3()
    {
        return $this->checkIfTriggerExists('trigger_sliders_images3', 'sliders_images');
    }

    /** Add trigger (incrementing position) to sliders_images 3  */
    public function upgrade_trigger_sliders_images3()
    {
        sqlQuery('ALTER TABLE sliders_images ALTER COLUMN position DROP DEFAULT');
        sqlQuery('DROP TRIGGER IF EXISTS trigger_sliders_images2');
        sqlQuery('CREATE TRIGGER trigger_sliders_images3 BEFORE INSERT ON sliders_images
                    FOR EACH ROW
                    BEGIN
                    IF NEW.position IS NULL THEN
                        SET NEW.position = ((SELECT IFNULL(MAX(position), 0) FROM sliders_images WHERE id_slider = NEW.id_slider) + 1);
                    END IF;
                END;');
        $this->upgradeOK();
    }

    public function check_labelColumn()
    {
        return $this->checkColumnExists('sliders', 'label');
    }

    /** sliders: add label column */
    public function upgrade_labelColumn()
    {
        sqlQuery('ALTER TABLE sliders ADD COLUMN label VARCHAR(50) DEFAULT NULL UNIQUE');

        $this->upgradeOK();
    }

    public function check_SizeColumns(): bool
    {
        return $this->checkColumnExists('sliders', 'size_tablet');
    }

    /** Add size_* columns into sliders table */
    public function upgrade_SizeColumns(): void
    {
        sqlQuery('ALTER TABLE sliders ADD COLUMN size_tablet VARCHAR(100) DEFAULT NULL AFTER size');
        sqlQuery('ALTER TABLE sliders ADD COLUMN size_mobile VARCHAR(100) DEFAULT NULL AFTER size_tablet');

        $this->upgradeOK();
    }

    public function check_PhotoIdColumn(): bool
    {
        return $this->checkColumnExists('sliders_images', 'id_photo');
    }

    /** Migrate sliders_images to photos */
    public function upgrade_PhotoIdColumn(): void
    {
        sqlQuery('ALTER TABLE sliders_images ADD COLUMN id_photo INT(11) UNSIGNED AFTER image');
        sqlQuery('ALTER TABLE sliders_images ADD FOREIGN KEY (id_photo) 
              REFERENCES photos(id) ON UPDATE CASCADE ON DELETE CASCADE;');

        $this->upgradeOK();
    }

    public function check_OldImageColumn(): bool
    {
        return !$this->checkColumnExists('sliders_images', 'image');
    }

    /** Remove old image column */
    public function upgrade_OldImageColumn(): void
    {
        $pathFinder = PathFinder::getService();

        $qb = sqlQueryBuilder()
            ->select('id, image')
            ->from('sliders_images')
            ->where('id_photo IS NULL');

        // convert slider images to photos table
        foreach ($qb->execute() as $item) {
            $path = $pathFinder->dataPath('photos/sliders/'.$item['image']);

            $photo = [
                'name' => $item['image'],
                'tmp_name' => $path,
            ];

            $img = \Photos::get();
            // use copy to keep original files in sliders dir
            if (file_exists($path)) {
                if ($img->uploadImage($photo, false, true)) {
                    $img->insertImageIntoDB();
                }
            }

            sqlQueryBuilder()
                ->update('sliders_images')
                ->directValues(
                    [
                        'id_photo' => $img->getID(),
                    ]
                )
                ->where(Operator::equals(['id' => $item['id']]))
                ->execute();
        }

        sqlQuery('ALTER TABLE sliders_images DROP COLUMN image');

        // migrovat sliders_images_translations do photos_translations
        if (findModule(\Modules::TRANSLATIONS)) {
            $qb = sqlQueryBuilder()
                ->select('si.id, si.id_photo, sit.id_language, sit.image')
                ->from('sliders_images', 'si')
                ->join('si', 'sliders_images_translations', 'sit', 'si.id = sit.id_sliders_image');

            $photosTranslation = ServiceContainer::getService(PhotosTranslation::class);
            foreach ($qb->execute() as $item) {
                if (empty($item['image'])) {
                    continue;
                }

                $path = $pathFinder->dataPath('photos/sliders/'.$item['image']);
                if (!file_exists($path)) {
                    continue;
                }

                $photo = [
                    'name' => $item['image'],
                    'tmp_name' => $path,
                ];

                $img = \Photos::get((int) $item['id_photo']);
                if (file_exists($path)) {
                    $img->uploadImage($photo, false, true, $item['id_language']);
                }

                $photosTranslation->saveSingleObject($item['id_language'], $item['id_photo'], ['image_2' => $img->image['filename']]);
            }

            sqlQuery('ALTER TABLE sliders_images_translations DROP COLUMN image');
        }

        $this->upgradeOK();
    }

    public function check_FigureColumn()
    {
        return $this->checkColumnExists('sliders_images', 'figure');
    }

    /** Add `sliders_images.figure` column  */
    public function upgrade_FigureColumn()
    {
        sqlQuery("ALTER TABLE sliders_images ADD figure ENUM('Y', 'N') NULL DEFAULT 'Y'");
        $this->upgradeOK();
    }

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

    /** Create table sliders_in_sections */
    public function upgrade_multipleSlidersInSection(): void
    {
        $positions = array_keys(SliderUtil::getPositions());
        $positions = "'".implode("', '", $positions)."'";

        sqlQuery("CREATE TABLE sliders_in_sections (
            id_section  INT NOT NULL,
            position    ENUM({$positions}) NOT NULL DEFAULT 'main',
            id_slider   INT NOT NULL,
            
            PRIMARY KEY (id_section, position),
            INDEX (id_slider),
            FOREIGN KEY `slider_to_section_fk` (id_slider) REFERENCES sliders(id) ON DELETE CASCADE,
            FOREIGN KEY `section_to_slider_fk` (id_section) REFERENCES sections(id) ON DELETE CASCADE
        )");

        $this->upgradeOK();
    }

    public function check_migrateSectionSliders(): bool
    {
        return !$this->checkColumnExists('sections', 'id_slider');
    }

    /** Migrate id_slider column (and additional slider) into sliders_in_sections table */
    public function upgrade_migrateSectionSliders(): void
    {
        sqlQuery("
            INSERT IGNORE INTO sliders_in_sections (id_slider, id_section, position)
            SELECT * FROM (
                (SELECT s.id_slider, s.id AS id_section, 'main' AS position 
                    FROM sections s
                    WHERE s.id_slider IS NOT NULL
                )
                UNION 
                (SELECT CAST(JSON_UNQUOTE(JSON_EXTRACT(s.data, '$.additional_slider')) AS INTEGER) AS id_slider, s.id AS id_section, 'additional' AS position
                    FROM sections s
                    WHERE s.data IS NOT NULL
                        AND JSON_EXTRACT(s.data, '$.additional_slider') IS NOT NULL
                        AND EXISTS ( 
                            SELECT * FROM sliders WHERE id = JSON_UNQUOTE(JSON_EXTRACT(s.data, '$.additional_slider')) 
                        )
                )
            ) t;
            
            ALTER TABLE sections DROP FOREIGN KEY IF EXISTS sections_ibfk_1;
            ALTER TABLE sections DROP COLUMN id_slider;
        ");

        $this->upgradeOK();
    }

    public function check_sliderPositionInSection(): bool
    {
        $positions = array_keys(SliderUtil::getPositions());

        return $this->checkEnumOptions('sliders_in_sections', 'position', $positions);
    }

    /** Update sliders_in_sections positions ENUM from module config */
    public function upgrade_sliderPositionInSection(): void
    {
        $positions = array_keys(SliderUtil::getPositions());

        $this->updateEnumOptions('sliders_in_sections', 'position', $positions);

        $this->upgradeOK();
    }
}