-- Migration 022: Link technicians to users, remove redundant personal info columns
-- (full_name, phone, email are now owned by the linked users row)
-- Idempotent: safe to run multiple times

BEGIN;

-- 1. Add user_id FK column if not exists
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name   = 'technicians'
          AND column_name  = 'user_id'
    ) THEN
        ALTER TABLE public.technicians
            ADD COLUMN user_id UUID
            REFERENCES public.users(id) ON DELETE SET NULL;
    END IF;
END $$;

-- 2. Add index on user_id if not exists
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes
        WHERE schemaname = 'public'
          AND tablename  = 'technicians'
          AND indexname  = 'idx_technicians_user_id'
    ) THEN
        CREATE INDEX idx_technicians_user_id ON public.technicians(user_id);
    END IF;
END $$;

-- 3. Drop unique indexes that lived on the removed columns (idempotent)
DROP INDEX IF EXISTS public.idx_technicians_email;
DROP INDEX IF EXISTS public.idx_technicians_phone;

-- 4. Drop full_name column if it still exists
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name   = 'technicians'
          AND column_name  = 'full_name'
    ) THEN
        ALTER TABLE public.technicians DROP COLUMN full_name;
    END IF;
END $$;

-- 5. Drop phone column if it still exists
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name   = 'technicians'
          AND column_name  = 'phone'
    ) THEN
        ALTER TABLE public.technicians DROP COLUMN phone;
    END IF;
END $$;

-- 6. Drop email column if it still exists
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name   = 'technicians'
          AND column_name  = 'email'
    ) THEN
        ALTER TABLE public.technicians DROP COLUMN email;
    END IF;
END $$;

COMMIT;
