<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20250905042717 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Migrate Customer data to User and update foreign key references';
    }

    public function up(Schema $schema): void
    {
        // First, add new columns to user table
        $this->addSql('ALTER TABLE `user` ADD phone VARCHAR(20) DEFAULT NULL, ADD is_active TINYINT(1) NOT NULL, ADD email_verified_at DATETIME DEFAULT NULL, ADD created_at DATETIME NOT NULL, ADD updated_at DATETIME DEFAULT NULL, ADD last_login_at DATETIME DEFAULT NULL, ADD subscription_type VARCHAR(50) DEFAULT NULL, ADD subscription_expires_at DATETIME DEFAULT NULL');
        
        // Migrate customer data to user table (only if not already exists)
        $this->addSql('INSERT IGNORE INTO `user` (email, roles, password, name, phone, is_active, email_verified_at, created_at, updated_at, last_login_at, subscription_type, subscription_expires_at)
                       SELECT email, roles, password, name, phone, is_active, 
                              CASE WHEN email_verified_at = "0000-00-00 00:00:00" OR email_verified_at IS NULL THEN NULL ELSE email_verified_at END,
                              CASE WHEN created_at = "0000-00-00 00:00:00" OR created_at IS NULL THEN NOW() ELSE created_at END,
                              CASE WHEN updated_at = "0000-00-00 00:00:00" OR updated_at IS NULL THEN NULL ELSE updated_at END,
                              CASE WHEN last_login_at = "0000-00-00 00:00:00" OR last_login_at IS NULL THEN NULL ELSE last_login_at END,
                              subscription_type,
                              CASE WHEN subscription_expires_at = "0000-00-00 00:00:00" OR subscription_expires_at IS NULL THEN NULL ELSE subscription_expires_at END
                       FROM customer');

        // Update question table to reference user instead of customer
        $this->addSql('UPDATE question q 
                       INNER JOIN customer c ON q.author_id = c.id 
                       INNER JOIN `user` u ON c.email = u.email 
                       SET q.author_id = u.id');

        // Update answer table to reference user instead of customer
        $this->addSql('UPDATE answer a 
                       INNER JOIN customer c ON a.author_id = c.id 
                       INNER JOIN `user` u ON c.email = u.email 
                       SET a.author_id = u.id');

        // Update question_vote table to reference user instead of customer
        $this->addSql('UPDATE question_vote qv 
                       INNER JOIN customer c ON qv.user_id = c.id 
                       INNER JOIN `user` u ON c.email = u.email 
                       SET qv.user_id = u.id');

        // Update answer_vote table to reference user instead of customer
        $this->addSql('UPDATE answer_vote av 
                       INNER JOIN customer c ON av.user_id = c.id 
                       INNER JOIN `user` u ON c.email = u.email 
                       SET av.user_id = u.id');

        // Update password_reset_token table to reference user instead of customer
        $this->addSql('UPDATE password_reset_token prt 
                       INNER JOIN customer c ON prt.customer_id = c.id 
                       INNER JOIN `user` u ON c.email = u.email 
                       SET prt.customer_id = u.id');

        // Now update foreign key constraints
        $this->addSql('ALTER TABLE answer DROP FOREIGN KEY FK_DADD4A25F675F31B');
        $this->addSql('ALTER TABLE answer ADD CONSTRAINT FK_DADD4A25F675F31B FOREIGN KEY (author_id) REFERENCES `user` (id)');
        
        $this->addSql('ALTER TABLE answer_vote DROP FOREIGN KEY FK_43B66A4A76ED395');
        $this->addSql('ALTER TABLE answer_vote ADD CONSTRAINT FK_43B66A4A76ED395 FOREIGN KEY (user_id) REFERENCES `user` (id)');
        
        $this->addSql('ALTER TABLE password_reset_token DROP FOREIGN KEY FK_6B7BA4B69395C3F3');
        $this->addSql('DROP INDEX IDX_6B7BA4B69395C3F3 ON password_reset_token');
        $this->addSql('ALTER TABLE password_reset_token CHANGE customer_id user_id INT NOT NULL');
        $this->addSql('ALTER TABLE password_reset_token ADD CONSTRAINT FK_6B7BA4B6A76ED395 FOREIGN KEY (user_id) REFERENCES `user` (id)');
        $this->addSql('CREATE INDEX IDX_6B7BA4B6A76ED395 ON password_reset_token (user_id)');
        
        $this->addSql('ALTER TABLE question DROP FOREIGN KEY FK_B6F7494EF675F31B');
        $this->addSql('ALTER TABLE question ADD CONSTRAINT FK_B6F7494EF675F31B FOREIGN KEY (author_id) REFERENCES `user` (id)');
        
        $this->addSql('ALTER TABLE question_vote DROP FOREIGN KEY FK_4FE688BA76ED395');
        $this->addSql('ALTER TABLE question_vote ADD CONSTRAINT FK_4FE688BA76ED395 FOREIGN KEY (user_id) REFERENCES `user` (id)');
    }

    public function down(Schema $schema): void
    {
        // Revert foreign key constraints
        $this->addSql('ALTER TABLE answer DROP FOREIGN KEY FK_DADD4A25F675F31B');
        $this->addSql('ALTER TABLE answer ADD CONSTRAINT FK_DADD4A25F675F31B FOREIGN KEY (author_id) REFERENCES customer (id) ON UPDATE NO ACTION ON DELETE NO ACTION');
        
        $this->addSql('ALTER TABLE answer_vote DROP FOREIGN KEY FK_43B66A4A76ED395');
        $this->addSql('ALTER TABLE answer_vote ADD CONSTRAINT FK_43B66A4A76ED395 FOREIGN KEY (user_id) REFERENCES customer (id) ON UPDATE NO ACTION ON DELETE NO ACTION');
        
        $this->addSql('ALTER TABLE password_reset_token DROP FOREIGN KEY FK_6B7BA4B6A76ED395');
        $this->addSql('DROP INDEX IDX_6B7BA4B6A76ED395 ON password_reset_token');
        $this->addSql('ALTER TABLE password_reset_token CHANGE user_id customer_id INT NOT NULL');
        $this->addSql('ALTER TABLE password_reset_token ADD CONSTRAINT FK_6B7BA4B69395C3F3 FOREIGN KEY (customer_id) REFERENCES customer (id) ON UPDATE NO ACTION ON DELETE NO ACTION');
        $this->addSql('CREATE INDEX IDX_6B7BA4B69395C3F3 ON password_reset_token (customer_id)');
        
        $this->addSql('ALTER TABLE question DROP FOREIGN KEY FK_B6F7494EF675F31B');
        $this->addSql('ALTER TABLE question ADD CONSTRAINT FK_B6F7494EF675F31B FOREIGN KEY (author_id) REFERENCES customer (id) ON UPDATE NO ACTION ON DELETE NO ACTION');
        
        $this->addSql('ALTER TABLE question_vote DROP FOREIGN KEY FK_4FE688BA76ED395');
        $this->addSql('ALTER TABLE question_vote ADD CONSTRAINT FK_4FE688BA76ED395 FOREIGN KEY (user_id) REFERENCES customer (id) ON UPDATE NO ACTION ON DELETE NO ACTION');
        
        // Remove new columns from user table
        $this->addSql('ALTER TABLE `user` DROP phone, DROP is_active, DROP email_verified_at, DROP created_at, DROP updated_at, DROP last_login_at, DROP subscription_type, DROP subscription_expires_at');
    }
}