<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version20260610103401 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Replace polymorphic categories table with domain-specific tables: provinces, cities, specialties, doctor_services, insurances, tags';
    }

    public function up(Schema $schema): void
    {
        // ── 1. Create new tables ──────────────────────────────────────────────
        $this->addSql('CREATE TABLE provinces (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, status SMALLINT NOT NULL, weight INT NOT NULL, UNIQUE INDEX UNIQ_8C96CC57D17F50A6 (uuid), INDEX idx_provinces_status (status), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');

        $this->addSql('CREATE TABLE cities (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, status SMALLINT NOT NULL, weight INT NOT NULL, representation_id INT DEFAULT NULL, contact_phone VARCHAR(255) DEFAULT NULL, email VARCHAR(255) DEFAULT NULL, description LONGTEXT DEFAULT NULL, slogan VARCHAR(255) DEFAULT NULL, domain VARCHAR(255) DEFAULT NULL, keywords VARCHAR(255) DEFAULT NULL, footer_description LONGTEXT DEFAULT NULL, social_media JSON DEFAULT NULL, logo_url VARCHAR(500) DEFAULT NULL, province_id INT DEFAULT NULL, UNIQUE INDEX UNIQ_D95DB16BD17F50A6 (uuid), INDEX idx_cities_province (province_id), INDEX idx_cities_status (status), INDEX idx_cities_representation (representation_id), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');

        $this->addSql('CREATE TABLE specialties (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL, status SMALLINT NOT NULL, weight INT NOT NULL, parent_id INT DEFAULT NULL, UNIQUE INDEX UNIQ_410754B0D17F50A6 (uuid), INDEX idx_specialties_status (status), INDEX idx_specialties_parent (parent_id), UNIQUE INDEX uq_specialties_slug (slug), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');

        $this->addSql('CREATE TABLE doctor_services (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL, status SMALLINT NOT NULL, weight INT NOT NULL, specialty_id INT DEFAULT NULL, UNIQUE INDEX UNIQ_DBC6935FD17F50A6 (uuid), INDEX idx_doctor_services_status (status), INDEX idx_doctor_services_specialty (specialty_id), UNIQUE INDEX uq_doctor_services_slug (slug), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');

        $this->addSql('CREATE TABLE insurances (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, type VARCHAR(20) NOT NULL, logo_url VARCHAR(500) DEFAULT NULL, status SMALLINT NOT NULL, UNIQUE INDEX UNIQ_6400CC1FD17F50A6 (uuid), INDEX idx_insurances_type (type), INDEX idx_insurances_status (status), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');

        $this->addSql('CREATE TABLE tags (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL, status SMALLINT NOT NULL, UNIQUE INDEX UNIQ_6FBC9426D17F50A6 (uuid), INDEX idx_tags_status (status), UNIQUE INDEX uq_tags_slug (slug), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');

        $this->addSql('CREATE TABLE doctor_provinces (doctor_id INT NOT NULL, province_id INT NOT NULL, INDEX IDX_4384ADBC87F4FB17 (doctor_id), INDEX IDX_4384ADBCE946114A (province_id), PRIMARY KEY (doctor_id, province_id)) DEFAULT CHARACTER SET utf8mb4');

        // ── 2. FK constraints on new tables ──────────────────────────────────
        $this->addSql('ALTER TABLE cities ADD CONSTRAINT FK_D95DB16BE946114A FOREIGN KEY (province_id) REFERENCES provinces (id)');
        $this->addSql('ALTER TABLE doctor_services ADD CONSTRAINT FK_DBC6935F9A353316 FOREIGN KEY (specialty_id) REFERENCES specialties (id) ON DELETE SET NULL');
        $this->addSql('ALTER TABLE doctor_provinces ADD CONSTRAINT FK_4384ADBC87F4FB17 FOREIGN KEY (doctor_id) REFERENCES doctors (id)');
        $this->addSql('ALTER TABLE doctor_provinces ADD CONSTRAINT FK_4384ADBCE946114A FOREIGN KEY (province_id) REFERENCES provinces (id)');
        $this->addSql('ALTER TABLE specialties ADD CONSTRAINT FK_410754B0727ACA70 FOREIGN KEY (parent_id) REFERENCES specialties (id) ON DELETE SET NULL');

        // ── 3. Migrate data: provinces (bundle=state, IDs 1–31) ───────────────
        $this->addSql("
            INSERT INTO provinces (id, uuid, name, status, weight)
            SELECT id, UUID(), label, status, weight
            FROM categories
            WHERE bundle = 'state'
        ");

        // ── 4. Migrate data: cities (bundle=city, parent_id → province_id) ───
        $this->addSql("
            INSERT INTO cities (id, uuid, name, status, weight, province_id,
                                representation_id, contact_phone, email,
                                description, slogan, domain, keywords,
                                footer_description, logo_url)
            SELECT id, UUID(), label, status, weight, parent_id,
                   representation_id, contact_phone, email,
                   description, slogan, domain, keywords,
                   footer_description, logo_url
            FROM categories
            WHERE bundle = 'city'
        ");

        // ── 5. Migrate data: specialties (bundle=specially_doctor) ────────────
        $this->addSql("
            INSERT INTO specialties (id, uuid, name, slug, status, weight, parent_id)
            SELECT id, UUID(), label, CONCAT('specialty-', id), status, weight, NULL
            FROM categories
            WHERE bundle = 'specially_doctor'
        ");

        // ── 6. Migrate data: doctor_services (bundle=doctor_services) ─────────
        $this->addSql("
            INSERT INTO doctor_services (id, uuid, name, slug, status, weight, specialty_id)
            SELECT id, UUID(), label, CONCAT('service-', id), status, weight, NULL
            FROM categories
            WHERE bundle = 'doctor_services'
        ");

        // ── 7. Migrate data: insurances (basic + supplementary) ───────────────
        $this->addSql("
            INSERT INTO insurances (id, uuid, name, type, logo_url, status)
            SELECT id, UUID(), label, 'basic', logo_url, status
            FROM categories
            WHERE bundle = 'insurance_type'
        ");
        $this->addSql("
            INSERT INTO insurances (id, uuid, name, type, logo_url, status)
            SELECT id, UUID(), label, 'supplementary', logo_url, status
            FROM categories
            WHERE bundle = 'supplementary_insurance'
        ");

        // ── 8. Migrate data: tags (bundle=tag) ────────────────────────────────
        $this->addSql("
            INSERT INTO tags (id, uuid, name, slug, status)
            SELECT id, UUID(), label, CONCAT('tag-', id), status
            FROM categories
            WHERE bundle = 'tag'
        ");

        // ── 9. Migrate doctor_states → doctor_provinces ───────────────────────
        $this->addSql("
            INSERT INTO doctor_provinces (doctor_id, province_id)
            SELECT doctor_id, category_id
            FROM doctor_states
        ");

        // ── 10. Drop old doctor_states table ─────────────────────────────────
        $this->addSql('ALTER TABLE doctor_states DROP FOREIGN KEY `FK_955E47C012469DE2`');
        $this->addSql('ALTER TABLE doctor_states DROP FOREIGN KEY `FK_955E47C087F4FB17`');
        $this->addSql('DROP TABLE doctor_states');

        // ── 11. clinics: state_id → province_id ──────────────────────────────
        $this->addSql('DROP INDEX idx_clinics_state ON clinics');
        $this->addSql('ALTER TABLE clinics CHANGE state_id province_id INT DEFAULT NULL');
        $this->addSql('CREATE INDEX idx_clinics_province ON clinics (province_id)');

        // ── 12. clinic_specialties: category_id → specialty_id ───────────────
        $this->addSql('ALTER TABLE clinic_specialties DROP FOREIGN KEY `FK_3201DD3712469DE2`');
        $this->addSql('DROP INDEX IDX_3201DD3712469DE2 ON clinic_specialties');
        $this->addSql('ALTER TABLE clinic_specialties CHANGE category_id specialty_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (clinic_id, specialty_id)');
        $this->addSql('ALTER TABLE clinic_specialties ADD CONSTRAINT FK_3201DD379A353316 FOREIGN KEY (specialty_id) REFERENCES specialties (id)');
        $this->addSql('CREATE INDEX IDX_3201DD379A353316 ON clinic_specialties (specialty_id)');

        // ── 13. clinic_services: category_id → service_id ────────────────────
        $this->addSql('ALTER TABLE clinic_services DROP FOREIGN KEY `FK_C00FFAB012469DE2`');
        $this->addSql('DROP INDEX IDX_C00FFAB012469DE2 ON clinic_services');
        $this->addSql('ALTER TABLE clinic_services CHANGE category_id service_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (clinic_id, service_id)');
        $this->addSql('ALTER TABLE clinic_services ADD CONSTRAINT FK_C00FFAB0ED5CA9E6 FOREIGN KEY (service_id) REFERENCES doctor_services (id)');
        $this->addSql('CREATE INDEX IDX_C00FFAB0ED5CA9E6 ON clinic_services (service_id)');

        // ── 14. clinic_insurances: category_id → insurance_id ────────────────
        $this->addSql('ALTER TABLE clinic_insurances DROP FOREIGN KEY `FK_BE9FC89812469DE2`');
        $this->addSql('DROP INDEX IDX_BE9FC89812469DE2 ON clinic_insurances');
        $this->addSql('ALTER TABLE clinic_insurances CHANGE category_id insurance_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (clinic_id, insurance_id)');
        $this->addSql('ALTER TABLE clinic_insurances ADD CONSTRAINT FK_BE9FC898D1E63CD1 FOREIGN KEY (insurance_id) REFERENCES insurances (id)');
        $this->addSql('CREATE INDEX IDX_BE9FC898D1E63CD1 ON clinic_insurances (insurance_id)');

        // ── 15. doctor_insurances: category_id → insurance_id ────────────────
        $this->addSql('ALTER TABLE doctor_insurances DROP FOREIGN KEY `FK_5317E58E12469DE2`');
        $this->addSql('DROP INDEX idx_doctor_insurance_cat ON doctor_insurances');
        $this->addSql('DROP INDEX idx_doctor_insurance ON doctor_insurances');
        $this->addSql('ALTER TABLE doctor_insurances CHANGE category_id insurance_id INT NOT NULL');
        $this->addSql('ALTER TABLE doctor_insurances ADD CONSTRAINT FK_5317E58ED1E63CD1 FOREIGN KEY (insurance_id) REFERENCES insurances (id)');
        $this->addSql('CREATE INDEX idx_doctor_insurance_ins ON doctor_insurances (insurance_id)');
        $this->addSql('CREATE UNIQUE INDEX idx_doctor_insurance ON doctor_insurances (doctor_id, insurance_id)');

        // ── 16. doctor_specialties: category_id → specialty_id ───────────────
        $this->addSql('ALTER TABLE doctor_specialties DROP FOREIGN KEY `FK_C638E04B12469DE2`');
        $this->addSql('DROP INDEX IDX_C638E04B12469DE2 ON doctor_specialties');
        $this->addSql('ALTER TABLE doctor_specialties CHANGE category_id specialty_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (doctor_id, specialty_id)');
        $this->addSql('ALTER TABLE doctor_specialties ADD CONSTRAINT FK_C638E04B9A353316 FOREIGN KEY (specialty_id) REFERENCES specialties (id)');
        $this->addSql('CREATE INDEX IDX_C638E04B9A353316 ON doctor_specialties (specialty_id)');

        // ── 17. doctor_expertise: category_id → service_id ───────────────────
        $this->addSql('ALTER TABLE doctor_expertise DROP FOREIGN KEY `FK_ED88BE6012469DE2`');
        $this->addSql('DROP INDEX IDX_ED88BE6012469DE2 ON doctor_expertise');
        $this->addSql('ALTER TABLE doctor_expertise CHANGE category_id service_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (doctor_id, service_id)');
        $this->addSql('ALTER TABLE doctor_expertise ADD CONSTRAINT FK_ED88BE60ED5CA9E6 FOREIGN KEY (service_id) REFERENCES doctor_services (id)');
        $this->addSql('CREATE INDEX IDX_ED88BE60ED5CA9E6 ON doctor_expertise (service_id)');

        // ── 18. doctor_cities: category_id → city_id ─────────────────────────
        $this->addSql('ALTER TABLE doctor_cities DROP FOREIGN KEY `FK_7DC181E612469DE2`');
        $this->addSql('DROP INDEX IDX_7DC181E612469DE2 ON doctor_cities');
        $this->addSql('ALTER TABLE doctor_cities CHANGE category_id city_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (doctor_id, city_id)');
        $this->addSql('ALTER TABLE doctor_cities ADD CONSTRAINT FK_7DC181E68BAC62AF FOREIGN KEY (city_id) REFERENCES cities (id)');
        $this->addSql('CREATE INDEX IDX_7DC181E68BAC62AF ON doctor_cities (city_id)');

        // ── 19. Drop the categories table ─────────────────────────────────────
        $this->addSql('DROP TABLE categories');
    }

    public function down(Schema $schema): void
    {
        // Recreate categories table
        $this->addSql("
            CREATE TABLE categories (
                id INT AUTO_INCREMENT NOT NULL,
                uuid VARCHAR(36) NOT NULL,
                bundle VARCHAR(32) NOT NULL,
                label VARCHAR(255) DEFAULT NULL,
                status SMALLINT NOT NULL,
                parent_id INT DEFAULT NULL,
                weight INT NOT NULL,
                logo_id INT DEFAULT NULL,
                title VARCHAR(255) DEFAULT NULL,
                representation_id INT DEFAULT NULL,
                contact_phone VARCHAR(255) DEFAULT NULL,
                email VARCHAR(255) DEFAULT NULL,
                description LONGTEXT DEFAULT NULL,
                slogan VARCHAR(255) DEFAULT NULL,
                domain VARCHAR(255) DEFAULT NULL,
                keywords VARCHAR(255) DEFAULT NULL,
                footer_description LONGTEXT DEFAULT NULL,
                footer_disclaimer LONGTEXT DEFAULT NULL,
                social_media LONGTEXT DEFAULT NULL,
                logo_url VARCHAR(500) DEFAULT NULL,
                UNIQUE INDEX UNIQ_D95DB16BD17F50A6 (uuid),
                INDEX idx_categories_bundle (bundle),
                INDEX idx_categories_status (status),
                INDEX idx_categories_parent (parent_id),
                PRIMARY KEY (id)
            ) DEFAULT CHARACTER SET utf8mb4
        ");

        // Recreate doctor_states
        $this->addSql('CREATE TABLE doctor_states (doctor_id INT NOT NULL, category_id INT NOT NULL, INDEX IDX_955E47C012469DE2 (category_id), INDEX IDX_955E47C087F4FB17 (doctor_id), PRIMARY KEY (doctor_id, category_id)) DEFAULT CHARACTER SET utf8mb4');

        // Restore pivot table FKs to categories
        $this->addSql('ALTER TABLE clinic_insurances DROP FOREIGN KEY FK_BE9FC898D1E63CD1');
        $this->addSql('DROP INDEX IDX_BE9FC898D1E63CD1 ON clinic_insurances');
        $this->addSql('ALTER TABLE clinic_insurances CHANGE insurance_id category_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (clinic_id, category_id)');
        $this->addSql('ALTER TABLE clinic_insurances ADD CONSTRAINT `FK_BE9FC89812469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('CREATE INDEX IDX_BE9FC89812469DE2 ON clinic_insurances (category_id)');

        $this->addSql('ALTER TABLE clinic_services DROP FOREIGN KEY FK_C00FFAB0ED5CA9E6');
        $this->addSql('DROP INDEX IDX_C00FFAB0ED5CA9E6 ON clinic_services');
        $this->addSql('ALTER TABLE clinic_services CHANGE service_id category_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (clinic_id, category_id)');
        $this->addSql('ALTER TABLE clinic_services ADD CONSTRAINT `FK_C00FFAB012469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('CREATE INDEX IDX_C00FFAB012469DE2 ON clinic_services (category_id)');

        $this->addSql('ALTER TABLE clinic_specialties DROP FOREIGN KEY FK_3201DD379A353316');
        $this->addSql('DROP INDEX IDX_3201DD379A353316 ON clinic_specialties');
        $this->addSql('ALTER TABLE clinic_specialties CHANGE specialty_id category_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (clinic_id, category_id)');
        $this->addSql('ALTER TABLE clinic_specialties ADD CONSTRAINT `FK_3201DD3712469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('CREATE INDEX IDX_3201DD3712469DE2 ON clinic_specialties (category_id)');

        $this->addSql('ALTER TABLE doctor_cities DROP FOREIGN KEY FK_7DC181E68BAC62AF');
        $this->addSql('DROP INDEX IDX_7DC181E68BAC62AF ON doctor_cities');
        $this->addSql('ALTER TABLE doctor_cities CHANGE city_id category_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (doctor_id, category_id)');
        $this->addSql('ALTER TABLE doctor_cities ADD CONSTRAINT `FK_7DC181E612469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('CREATE INDEX IDX_7DC181E612469DE2 ON doctor_cities (category_id)');

        $this->addSql('ALTER TABLE doctor_expertise DROP FOREIGN KEY FK_ED88BE60ED5CA9E6');
        $this->addSql('DROP INDEX IDX_ED88BE60ED5CA9E6 ON doctor_expertise');
        $this->addSql('ALTER TABLE doctor_expertise CHANGE service_id category_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (doctor_id, category_id)');
        $this->addSql('ALTER TABLE doctor_expertise ADD CONSTRAINT `FK_ED88BE6012469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('CREATE INDEX IDX_ED88BE6012469DE2 ON doctor_expertise (category_id)');

        $this->addSql('ALTER TABLE doctor_insurances DROP FOREIGN KEY FK_5317E58ED1E63CD1');
        $this->addSql('DROP INDEX idx_doctor_insurance_ins ON doctor_insurances');
        $this->addSql('DROP INDEX idx_doctor_insurance ON doctor_insurances');
        $this->addSql('ALTER TABLE doctor_insurances CHANGE insurance_id category_id INT NOT NULL');
        $this->addSql('ALTER TABLE doctor_insurances ADD CONSTRAINT `FK_5317E58E12469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('CREATE INDEX idx_doctor_insurance_cat ON doctor_insurances (category_id)');
        $this->addSql('CREATE UNIQUE INDEX idx_doctor_insurance ON doctor_insurances (doctor_id, category_id)');

        $this->addSql('ALTER TABLE doctor_specialties DROP FOREIGN KEY FK_C638E04B9A353316');
        $this->addSql('DROP INDEX IDX_C638E04B9A353316 ON doctor_specialties');
        $this->addSql('ALTER TABLE doctor_specialties CHANGE specialty_id category_id INT NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (doctor_id, category_id)');
        $this->addSql('ALTER TABLE doctor_specialties ADD CONSTRAINT `FK_C638E04B12469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('CREATE INDEX IDX_C638E04B12469DE2 ON doctor_specialties (category_id)');

        $this->addSql('DROP INDEX idx_clinics_province ON clinics');
        $this->addSql('ALTER TABLE clinics CHANGE province_id state_id INT DEFAULT NULL');
        $this->addSql('CREATE INDEX idx_clinics_state ON clinics (state_id)');

        $this->addSql('ALTER TABLE doctor_states ADD CONSTRAINT `FK_955E47C012469DE2` FOREIGN KEY (category_id) REFERENCES categories (id)');
        $this->addSql('ALTER TABLE doctor_states ADD CONSTRAINT `FK_955E47C087F4FB17` FOREIGN KEY (doctor_id) REFERENCES doctors (id)');

        $this->addSql('ALTER TABLE cities DROP FOREIGN KEY FK_D95DB16BE946114A');
        $this->addSql('ALTER TABLE doctor_services DROP FOREIGN KEY FK_DBC6935F9A353316');
        $this->addSql('ALTER TABLE doctor_provinces DROP FOREIGN KEY FK_4384ADBC87F4FB17');
        $this->addSql('ALTER TABLE doctor_provinces DROP FOREIGN KEY FK_4384ADBCE946114A');
        $this->addSql('ALTER TABLE specialties DROP FOREIGN KEY FK_410754B0727ACA70');
        $this->addSql('DROP TABLE cities');
        $this->addSql('DROP TABLE doctor_services');
        $this->addSql('DROP TABLE doctor_provinces');
        $this->addSql('DROP TABLE insurances');
        $this->addSql('DROP TABLE provinces');
        $this->addSql('DROP TABLE specialties');
        $this->addSql('DROP TABLE tags');
    }
}