# Generated by Django 5.2.13 on 2026-04-28 09:58

import django.contrib.postgres.indexes
import django.contrib.postgres.search
from django.conf import settings
from django.db import migrations


# Trigger that updates ``search_vector`` from the joined CustomUser names + the
# AlumniProfile text fields. Fires on any insert/update against
# alumni_profiles_alumniprofile so the FTS index stays in sync without a periodic
# rebuild task. The function is recreated idempotently with CREATE OR REPLACE.
TRIGGER_FORWARD_SQL = """
CREATE OR REPLACE FUNCTION alumni_profile_refresh_search_vector() RETURNS trigger AS $$
DECLARE
    full_name text := '';
BEGIN
    SELECT coalesce(first_name, '') || ' ' || coalesce(last_name, '') || ' ' || coalesce(email, '')
      INTO full_name
      FROM core_customuser WHERE id = NEW.user_id;
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(full_name, '')), 'A')
        || setweight(to_tsvector('english', coalesce(NEW.headline, '')), 'A')
        || setweight(to_tsvector('english', coalesce(NEW.current_employer, '')), 'B')
        || setweight(to_tsvector('english', coalesce(NEW.current_position, '')), 'B')
        || setweight(to_tsvector('english', coalesce(array_to_string(NEW.expertise_tags, ' '), '')), 'B')
        || setweight(to_tsvector('english', coalesce(array_to_string(NEW.mentor_topics, ' '), '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS alumni_profile_refresh_search_vector_trg ON alumni_profiles_alumniprofile;
CREATE TRIGGER alumni_profile_refresh_search_vector_trg
BEFORE INSERT OR UPDATE OF headline, current_employer, current_position, expertise_tags, mentor_topics, user_id
ON alumni_profiles_alumniprofile
FOR EACH ROW EXECUTE FUNCTION alumni_profile_refresh_search_vector();
"""

TRIGGER_REVERSE_SQL = """
DROP TRIGGER IF EXISTS alumni_profile_refresh_search_vector_trg ON alumni_profiles_alumniprofile;
DROP FUNCTION IF EXISTS alumni_profile_refresh_search_vector();
"""

# One-off backfill — touches every existing profile so the trigger fires.
BACKFILL_FORWARD_SQL = """
UPDATE alumni_profiles_alumniprofile SET updated_at = updated_at;
"""


class Migration(migrations.Migration):

    dependencies = [
        ("alumni_profiles", "0002_alumniprofile_mentor_accepting_and_more"),
        ("core", "0030_alter_notification_verb_and_more"),
        migrations.swappable_dependency(settings.AUTH_USER_MODEL),
    ]

    operations = [
        migrations.AddField(
            model_name="alumniprofile",
            name="search_vector",
            field=django.contrib.postgres.search.SearchVectorField(editable=False, null=True),
        ),
        migrations.AddField(
            model_name="historicalalumniprofile",
            name="search_vector",
            field=django.contrib.postgres.search.SearchVectorField(editable=False, null=True),
        ),
        migrations.AddIndex(
            model_name="alumniprofile",
            index=django.contrib.postgres.indexes.GinIndex(
                fields=["search_vector"], name="alumni_profile_search_gin"
            ),
        ),
        migrations.RunSQL(TRIGGER_FORWARD_SQL, reverse_sql=TRIGGER_REVERSE_SQL),
        migrations.RunSQL(BACKFILL_FORWARD_SQL, reverse_sql=migrations.RunSQL.noop),
    ]