defmodule Mv.Repo.Migrations.RemovePhoneNumberAndMakeFieldsOptional do
  @moduledoc """
  Removes phone_number field from members table and makes first_name/last_name optional.

  This migration:
  1. Removes phone_number column from members table
  2. Makes first_name and last_name columns nullable
  3. Updates members_search_vector_trigger() function to remove phone_number
  4. Updates update_member_search_vector_from_custom_field_value() function to remove phone_number
  5. Updates existing search_vector values for all members
  """

  use Ecto.Migration

  def up do
    # Update the main trigger function to remove phone_number
    execute("""
    CREATE OR REPLACE FUNCTION members_search_vector_trigger() RETURNS trigger AS $$
    DECLARE
      custom_values_text text;
    BEGIN
      -- Aggregate all custom field values for this member
      -- Support both formats: _union_type/_union_value (Ash format) and type/value (legacy)
      -- ->> operator always returns TEXT directly (no need for -> + ::text fallback)
      SELECT string_agg(
        CASE
          WHEN value ? '_union_value' THEN value->>'_union_value'
          WHEN value ? 'value' THEN value->>'value'
          ELSE ''
        END,
        ' '
      )
      INTO custom_values_text
      FROM custom_field_values
      WHERE member_id = NEW.id AND value IS NOT NULL;

      -- Build search_vector with member fields and custom field values
      NEW.search_vector :=
        setweight(to_tsvector('simple', coalesce(NEW.first_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.last_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.email, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(NEW.join_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(NEW.exit_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(NEW.notes, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(NEW.city, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(NEW.street, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(NEW.house_number::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(NEW.postal_code::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C');
      RETURN NEW;
    END
    $$ LANGUAGE plpgsql;
    """)

    # Update trigger function to remove phone_number
    execute("""
    CREATE OR REPLACE FUNCTION update_member_search_vector_from_custom_field_value() RETURNS trigger AS $$
    DECLARE
      member_id_val uuid;
      member_first_name text;
      member_last_name text;
      member_email text;
      member_join_date date;
      member_exit_date date;
      member_notes text;
      member_city text;
      member_street text;
      member_house_number text;
      member_postal_code text;
      custom_values_text text;
      old_value_text text;
      new_value_text text;
    BEGIN
      -- Get member ID from trigger context
      member_id_val := COALESCE(NEW.member_id, OLD.member_id);

      -- Optimization: For UPDATE operations, check if value actually changed
      -- If value hasn't changed, we can skip the expensive re-aggregation
      IF TG_OP = 'UPDATE' THEN
        -- Extract OLD value for comparison (handle both JSONB formats)
        -- ->> operator always returns TEXT directly
        old_value_text := COALESCE(
          NULLIF(OLD.value->>'_union_value', ''),
          NULLIF(OLD.value->>'value', ''),
          ''
        );

        -- Extract NEW value for comparison (handle both JSONB formats)
        new_value_text := COALESCE(
          NULLIF(NEW.value->>'_union_value', ''),
          NULLIF(NEW.value->>'value', ''),
          ''
        );

        -- Check if value, member_id, or custom_field_id actually changed
        -- If nothing changed, skip expensive re-aggregation
        IF (old_value_text IS NOT DISTINCT FROM new_value_text) AND
           (OLD.member_id IS NOT DISTINCT FROM NEW.member_id) AND
           (OLD.custom_field_id IS NOT DISTINCT FROM NEW.custom_field_id) THEN
          RETURN COALESCE(NEW, OLD);
        END IF;
      END IF;

      -- Fetch only required fields instead of full record (performance optimization)
      SELECT
        first_name,
        last_name,
        email,
        join_date,
        exit_date,
        notes,
        city,
        street,
        house_number,
        postal_code
      INTO
        member_first_name,
        member_last_name,
        member_email,
        member_join_date,
        member_exit_date,
        member_notes,
        member_city,
        member_street,
        member_house_number,
        member_postal_code
      FROM members
      WHERE id = member_id_val;

      -- Aggregate all custom field values for this member
      -- Support both formats: _union_type/_union_value (Ash format) and type/value (legacy)
      -- ->> operator always returns TEXT directly
      SELECT string_agg(
        CASE
          WHEN value ? '_union_value' THEN value->>'_union_value'
          WHEN value ? 'value' THEN value->>'value'
          ELSE ''
        END,
        ' '
      )
      INTO custom_values_text
      FROM custom_field_values
      WHERE member_id = member_id_val AND value IS NOT NULL;

      -- Update the search_vector for the affected member
      UPDATE members
      SET search_vector =
        setweight(to_tsvector('simple', coalesce(member_first_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(member_last_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(member_email, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(member_join_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(member_exit_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(member_notes, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(member_city, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(member_street, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(member_house_number::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(member_postal_code::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C')
      WHERE id = member_id_val;

      RETURN COALESCE(NEW, OLD);
    END
    $$ LANGUAGE plpgsql;
    """)

    # Update existing search_vector values for all members
    execute("""
    UPDATE members m
    SET search_vector =
      setweight(to_tsvector('simple', coalesce(m.first_name, '')), 'A') ||
      setweight(to_tsvector('simple', coalesce(m.last_name, '')), 'A') ||
      setweight(to_tsvector('simple', coalesce(m.email, '')), 'B') ||
      setweight(to_tsvector('simple', coalesce(m.join_date::text, '')), 'D') ||
      setweight(to_tsvector('simple', coalesce(m.exit_date::text, '')), 'D') ||
      setweight(to_tsvector('simple', coalesce(m.notes, '')), 'B') ||
      setweight(to_tsvector('simple', coalesce(m.city, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(m.street, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(m.house_number::text, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(m.postal_code::text, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(
        (SELECT string_agg(
          CASE
            WHEN value ? '_union_value' THEN value->>'_union_value'
            WHEN value ? 'value' THEN value->>'value'
            ELSE ''
          END,
          ' '
        )
        FROM custom_field_values
        WHERE member_id = m.id AND value IS NOT NULL),
        ''
      )), 'C')
    """)

    # Make first_name and last_name nullable
    execute("ALTER TABLE members ALTER COLUMN first_name DROP NOT NULL")
    execute("ALTER TABLE members ALTER COLUMN last_name DROP NOT NULL")

    # Remove phone_number column
    alter table(:members) do
      remove :phone_number
    end
  end

  def down do
    # Set default values for NULL fields before restoring NOT NULL constraint
    # This prevents the migration from failing if NULL values exist
    execute("UPDATE members SET first_name = '' WHERE first_name IS NULL")
    execute("UPDATE members SET last_name = '' WHERE last_name IS NULL")

    # Restore first_name and last_name as NOT NULL
    execute("ALTER TABLE members ALTER COLUMN first_name SET NOT NULL")
    execute("ALTER TABLE members ALTER COLUMN last_name SET NOT NULL")

    # Add phone_number column back
    alter table(:members) do
      add :phone_number, :text
    end

    # Restore trigger functions with phone_number
    execute("""
    CREATE OR REPLACE FUNCTION members_search_vector_trigger() RETURNS trigger AS $$
    DECLARE
      custom_values_text text;
    BEGIN
      -- Aggregate all custom field values for this member
      -- Support both formats: _union_type/_union_value (Ash format) and type/value (legacy)
      -- ->> operator always returns TEXT directly (no need for -> + ::text fallback)
      SELECT string_agg(
        CASE
          WHEN value ? '_union_value' THEN value->>'_union_value'
          WHEN value ? 'value' THEN value->>'value'
          ELSE ''
        END,
        ' '
      )
      INTO custom_values_text
      FROM custom_field_values
      WHERE member_id = NEW.id AND value IS NOT NULL;

      -- Build search_vector with member fields and custom field values
      NEW.search_vector :=
        setweight(to_tsvector('simple', coalesce(NEW.first_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.last_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.email, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(NEW.phone_number, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(NEW.join_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(NEW.exit_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(NEW.notes, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(NEW.city, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(NEW.street, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(NEW.house_number::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(NEW.postal_code::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C');
      RETURN NEW;
    END
    $$ LANGUAGE plpgsql;
    """)

    execute("""
    CREATE OR REPLACE FUNCTION update_member_search_vector_from_custom_field_value() RETURNS trigger AS $$
    DECLARE
      member_id_val uuid;
      member_first_name text;
      member_last_name text;
      member_email text;
      member_phone_number text;
      member_join_date date;
      member_exit_date date;
      member_notes text;
      member_city text;
      member_street text;
      member_house_number text;
      member_postal_code text;
      custom_values_text text;
      old_value_text text;
      new_value_text text;
    BEGIN
      -- Get member ID from trigger context
      member_id_val := COALESCE(NEW.member_id, OLD.member_id);

      -- Optimization: For UPDATE operations, check if value actually changed
      -- If value hasn't changed, we can skip the expensive re-aggregation
      IF TG_OP = 'UPDATE' THEN
        -- Extract OLD value for comparison (handle both JSONB formats)
        -- ->> operator always returns TEXT directly
        old_value_text := COALESCE(
          NULLIF(OLD.value->>'_union_value', ''),
          NULLIF(OLD.value->>'value', ''),
          ''
        );

        -- Extract NEW value for comparison (handle both JSONB formats)
        new_value_text := COALESCE(
          NULLIF(NEW.value->>'_union_value', ''),
          NULLIF(NEW.value->>'value', ''),
          ''
        );

        -- Check if value, member_id, or custom_field_id actually changed
        -- If nothing changed, skip expensive re-aggregation
        IF (old_value_text IS NOT DISTINCT FROM new_value_text) AND
           (OLD.member_id IS NOT DISTINCT FROM NEW.member_id) AND
           (OLD.custom_field_id IS NOT DISTINCT FROM NEW.custom_field_id) THEN
          RETURN COALESCE(NEW, OLD);
        END IF;
      END IF;

      -- Fetch only required fields instead of full record (performance optimization)
      SELECT
        first_name,
        last_name,
        email,
        phone_number,
        join_date,
        exit_date,
        notes,
        city,
        street,
        house_number,
        postal_code
      INTO
        member_first_name,
        member_last_name,
        member_email,
        member_phone_number,
        member_join_date,
        member_exit_date,
        member_notes,
        member_city,
        member_street,
        member_house_number,
        member_postal_code
      FROM members
      WHERE id = member_id_val;

      -- Aggregate all custom field values for this member
      -- Support both formats: _union_type/_union_value (Ash format) and type/value (legacy)
      -- ->> operator always returns TEXT directly
      SELECT string_agg(
        CASE
          WHEN value ? '_union_value' THEN value->>'_union_value'
          WHEN value ? 'value' THEN value->>'value'
          ELSE ''
        END,
        ' '
      )
      INTO custom_values_text
      FROM custom_field_values
      WHERE member_id = member_id_val AND value IS NOT NULL;

      -- Update the search_vector for the affected member
      UPDATE members
      SET search_vector =
        setweight(to_tsvector('simple', coalesce(member_first_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(member_last_name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(member_email, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(member_phone_number, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(member_join_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(member_exit_date::text, '')), 'D') ||
        setweight(to_tsvector('simple', coalesce(member_notes, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(member_city, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(member_street, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(member_house_number::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(member_postal_code::text, '')), 'C') ||
        setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C')
      WHERE id = member_id_val;

      RETURN COALESCE(NEW, OLD);
    END
    $$ LANGUAGE plpgsql;
    """)

    # Update existing search_vector values to include phone_number
    execute("""
    UPDATE members m
    SET search_vector =
      setweight(to_tsvector('simple', coalesce(m.first_name, '')), 'A') ||
      setweight(to_tsvector('simple', coalesce(m.last_name, '')), 'A') ||
      setweight(to_tsvector('simple', coalesce(m.email, '')), 'B') ||
      setweight(to_tsvector('simple', coalesce(m.phone_number, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(m.join_date::text, '')), 'D') ||
      setweight(to_tsvector('simple', coalesce(m.exit_date::text, '')), 'D') ||
      setweight(to_tsvector('simple', coalesce(m.notes, '')), 'B') ||
      setweight(to_tsvector('simple', coalesce(m.city, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(m.street, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(m.house_number::text, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(m.postal_code::text, '')), 'C') ||
      setweight(to_tsvector('simple', coalesce(
        (SELECT string_agg(
          CASE
            WHEN value ? '_union_value' THEN value->>'_union_value'
            WHEN value ? 'value' THEN value->>'value'
            ELSE ''
          END,
          ' '
        )
        FROM custom_field_values
        WHERE member_id = m.id AND value IS NOT NULL),
        ''
      )), 'C')
    """)
  end
end
