136 lines
4.1 KiB
PL/PgSQL
136 lines
4.1 KiB
PL/PgSQL
-- Ensure ak_users has a primary key and chat tables reference it consistently
|
|
-- This script can be executed on Supabase/PostgreSQL to harden chat user relations
|
|
--
|
|
-- Steps:
|
|
-- 1. Add a primary key on public.ak_users(id) if it is missing.
|
|
-- 2. Backfill missing ak_users rows from auth.users so foreign keys can validate.
|
|
-- 3. Recreate chat_* foreign keys that point to ak_users.
|
|
|
|
begin;
|
|
|
|
-- 1) Guard against duplicate or null ids before creating the primary key.
|
|
DO
|
|
$$
|
|
DECLARE
|
|
duplicate_ids integer;
|
|
null_ids integer;
|
|
BEGIN
|
|
SELECT count(*) INTO duplicate_ids
|
|
FROM (
|
|
SELECT id
|
|
FROM public.ak_users
|
|
GROUP BY id
|
|
HAVING count(*) > 1
|
|
) dup;
|
|
|
|
SELECT count(*) INTO null_ids
|
|
FROM public.ak_users
|
|
WHERE id IS NULL;
|
|
|
|
IF duplicate_ids > 0 THEN
|
|
RAISE EXCEPTION 'Cannot add primary key on public.ak_users(id): % duplicate id(s) detected', duplicate_ids;
|
|
ELSIF null_ids > 0 THEN
|
|
RAISE EXCEPTION 'Cannot add primary key on public.ak_users(id): % row(s) have NULL id', null_ids;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'ak_users_pkey'
|
|
AND conrelid = 'public.ak_users'::regclass
|
|
) THEN
|
|
ALTER TABLE public.ak_users
|
|
ADD CONSTRAINT ak_users_pkey PRIMARY KEY (id);
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
-- 2) Backfill basic ak_users profiles from auth.users for any missing ids.
|
|
INSERT INTO public.ak_users (
|
|
id,
|
|
username,
|
|
email,
|
|
password_hash,
|
|
gender,
|
|
birthday,
|
|
height_cm,
|
|
weight_kg,
|
|
bio,
|
|
phone,
|
|
avatar_url,
|
|
region_id,
|
|
school_id,
|
|
grade_id,
|
|
class_id,
|
|
role,
|
|
created_at,
|
|
updated_at,
|
|
auth_id,
|
|
preferred_language
|
|
)
|
|
SELECT
|
|
au.id,
|
|
COALESCE(NULLIF(trim(au.raw_user_meta_data ->> 'nickname'), ''),
|
|
NULLIF(trim(au.raw_user_meta_data ->> 'username'), ''),
|
|
split_part(au.email, '@', 1)),
|
|
COALESCE(au.email, 'user_' || substr(au.id::text, 1, 8) || '@akmon.local'),
|
|
'auth-managed:' || au.id::text,
|
|
COALESCE(NULLIF(trim(au.raw_user_meta_data ->> 'gender'), ''), 'other'),
|
|
NULL,
|
|
NULL,
|
|
NULL,
|
|
NULL,
|
|
NULL,
|
|
NULLIF(trim(au.raw_user_meta_data ->> 'avatar_url'), ''),
|
|
NULL,
|
|
NULL,
|
|
NULL,
|
|
NULL,
|
|
COALESCE(NULLIF(trim(au.raw_user_meta_data ->> 'role'), ''), 'student'),
|
|
COALESCE(au.created_at, now()),
|
|
COALESCE(au.updated_at, now()),
|
|
au.id,
|
|
NULLIF(trim(au.raw_user_meta_data ->> 'preferred_language'), '')
|
|
FROM auth.users au
|
|
LEFT JOIN public.ak_users ak ON ak.id = au.id
|
|
WHERE ak.id IS NULL;
|
|
|
|
-- 3) Recreate chat foreign keys to reference ak_users.
|
|
ALTER TABLE public.chat_conversations
|
|
DROP CONSTRAINT IF EXISTS chat_conversations_owner_id_fkey;
|
|
ALTER TABLE public.chat_conversations
|
|
ADD CONSTRAINT chat_conversations_owner_id_fkey
|
|
FOREIGN KEY (owner_id) REFERENCES public.ak_users(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE public.chat_participants
|
|
DROP CONSTRAINT IF EXISTS chat_participants_user_id_fkey;
|
|
ALTER TABLE public.chat_participants
|
|
ADD CONSTRAINT chat_participants_user_id_fkey
|
|
FOREIGN KEY (user_id) REFERENCES public.ak_users(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE public.chat_messages
|
|
DROP CONSTRAINT IF EXISTS chat_messages_sender_id_fkey;
|
|
ALTER TABLE public.chat_messages
|
|
ADD CONSTRAINT chat_messages_sender_id_fkey
|
|
FOREIGN KEY (sender_id) REFERENCES public.ak_users(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE public.chat_notifications
|
|
DROP CONSTRAINT IF EXISTS chat_notifications_user_id_fkey;
|
|
ALTER TABLE public.chat_notifications
|
|
ADD CONSTRAINT chat_notifications_user_id_fkey
|
|
FOREIGN KEY (user_id) REFERENCES public.ak_users(id) ON DELETE CASCADE;
|
|
|
|
ALTER TABLE public.chat_mqtt_downlinks
|
|
DROP CONSTRAINT IF EXISTS chat_mqtt_downlinks_target_user_id_fkey;
|
|
ALTER TABLE public.chat_mqtt_downlinks
|
|
ADD CONSTRAINT chat_mqtt_downlinks_target_user_id_fkey
|
|
FOREIGN KEY (target_user_id) REFERENCES public.ak_users(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE public.chat_mqtt_downlinks
|
|
DROP CONSTRAINT IF EXISTS chat_mqtt_downlinks_created_by_fkey;
|
|
ALTER TABLE public.chat_mqtt_downlinks
|
|
ADD CONSTRAINT chat_mqtt_downlinks_created_by_fkey
|
|
FOREIGN KEY (created_by) REFERENCES public.ak_users(id) ON DELETE CASCADE;
|
|
|
|
commit;
|