import { MigrationInterface, QueryRunner } from "typeorm";

export class UpdateUsersFollowerCount1743230339456 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`
            CREATE OR REPLACE FUNCTION update_user_follower_count()
            RETURNS TRIGGER AS $$
            BEGIN
                UPDATE users 
                SET total_followers = (
                    SELECT COALESCE(SUM(follower_count), 0)
                    FROM social_medias
                    WHERE users = NEW.users AND deleted_at IS NULL
                ),
                total_following = (
                    SELECT COALESCE(SUM(following_count), 0)
                    FROM social_medias
                    WHERE users = NEW.users AND deleted_at IS NULL
                ),
                engagement_ratio = (
                    SELECT COALESCE(AVG(engagement_rate), 0)
                    FROM social_medias
                    WHERE users = NEW.users AND deleted_at IS NULL
                )
                WHERE id = NEW.users;
                
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        `);

        await queryRunner.query(`
            CREATE TRIGGER update_user_follower_count
            AFTER INSERT OR UPDATE OR DELETE ON social_medias
            FOR EACH ROW
            EXECUTE FUNCTION update_user_follower_count();
        `);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TRIGGER IF EXISTS update_user_follower_count ON social_medias;`);
        await queryRunner.query(`DROP FUNCTION IF EXISTS update_user_follower_count;`);
    }

}