import { MigrationInterface, QueryRunner } from "typeorm";

export class MigrateCategoriesToTranslations1766989572319 implements MigrationInterface {
    name = 'MigrateCategoriesToTranslations1766989572319'

    public async up(queryRunner: QueryRunner): Promise<void> {
        // Insert English translations for all categories that don't have a translation yet
        // This will copy title, slug, description, and tags from categories table to category_translations table

        // First, let's check how many categories need migration
        const countResult = await queryRunner.query(`
            SELECT COUNT(*) as count
            FROM categories c
            WHERE c.deleted_at IS NULL
            AND NOT EXISTS (
                SELECT 1 
                FROM category_translations ct 
                WHERE ct.category_id = c.id 
                AND ct.language_code = 'en'
                AND ct.deleted_at IS NULL
            )
        `);

        const categoriesToMigrate = parseInt(countResult[0]?.count || '0');
        console.log(`📊 Found ${categoriesToMigrate} categories to migrate to translations...`);

        if (categoriesToMigrate > 0) {
            await queryRunner.query(`
                INSERT INTO category_translations (id, category_id, language_code, title, slug, description, tags, created_at, updated_at)
                SELECT 
                    uuid_generate_v4() as id,
                    c.id as category_id,
                    'en' as language_code,
                    COALESCE(NULLIF(TRIM(c.title), ''), 'Untitled Category') as title,
                    COALESCE(
                        NULLIF(TRIM(c.slug), ''),
                        LOWER(REGEXP_REPLACE(REGEXP_REPLACE(COALESCE(NULLIF(TRIM(c.title), ''), 'untitled-category'), '[^a-zA-Z0-9\\s-]', '', 'g'), '\\s+', '-', 'g'))
                    ) as slug,
                    c.description,
                    c.tags,
                    COALESCE(c.created_at, NOW()) as created_at,
                    COALESCE(c.updated_at, NOW()) as updated_at
                FROM categories c
                WHERE c.deleted_at IS NULL
                AND NOT EXISTS (
                    SELECT 1 
                    FROM category_translations ct 
                    WHERE ct.category_id = c.id 
                    AND ct.language_code = 'en'
                    AND ct.deleted_at IS NULL
                )
            `);

            // Verify the migration
            const result = await queryRunner.query(`
                SELECT COUNT(*) as count 
                FROM category_translations 
                WHERE language_code = 'en'
            `);

            console.log(`✅ Migration completed successfully!`);
            console.log(`   - Categories migrated: ${categoriesToMigrate}`);
            console.log(`   - Total English translations: ${result[0]?.count || 0}`);
        } else {
            console.log(`ℹ️  No categories need migration. All categories already have English translations.`);
        }
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        // Remove all English translations that were created by this migration
        // Note: This will only remove translations if they match the original category data
        await queryRunner.query(`
            DELETE FROM category_translations 
            WHERE language_code = 'en'
            AND EXISTS (
                SELECT 1 
                FROM categories c 
                WHERE c.id = category_translations.category_id
                AND c.title = category_translations.title
            )
        `);

        console.log(`✅ Rollback completed: English category translations removed.`);
    }
}