-- Drop foreign keys to legacy meeting_attendance.users
ALTER TABLE "platform_meeting_attendance"."accounts" DROP CONSTRAINT IF EXISTS "accounts_user_id_fkey";
ALTER TABLE "platform_meeting_attendance"."attendances" DROP CONSTRAINT IF EXISTS "attendances_user_id_fkey";
ALTER TABLE "platform_meeting_attendance"."audit_logs" DROP CONSTRAINT IF EXISTS "audit_logs_user_id_fkey";
ALTER TABLE "platform_meeting_attendance"."leave_records" DROP CONSTRAINT IF EXISTS "leave_records_user_id_fkey";
ALTER TABLE "platform_meeting_attendance"."meeting_required_attendees" DROP CONSTRAINT IF EXISTS "meeting_required_attendees_user_id_fkey";
ALTER TABLE "platform_meeting_attendance"."meeting_series" DROP CONSTRAINT IF EXISTS "meeting_series_creator_id_fkey";
ALTER TABLE "platform_meeting_attendance"."meeting_template_attendees" DROP CONSTRAINT IF EXISTS "meeting_template_attendees_user_id_fkey";
ALTER TABLE "platform_meeting_attendance"."meeting_templates" DROP CONSTRAINT IF EXISTS "meeting_templates_creator_id_fkey";
ALTER TABLE "platform_meeting_attendance"."meetings" DROP CONSTRAINT IF EXISTS "meetings_creator_id_fkey";
ALTER TABLE "platform_meeting_attendance"."sessions" DROP CONSTRAINT IF EXISTS "sessions_user_id_fkey";

-- Add new UUID columns for platform users
ALTER TABLE "platform_meeting_attendance"."meetings" ADD COLUMN "creator_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."meeting_series" ADD COLUMN "creator_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."meeting_templates" ADD COLUMN "creator_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."attendances" ADD COLUMN "user_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."meeting_required_attendees" ADD COLUMN "user_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."meeting_template_attendees" ADD COLUMN "user_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."leave_records" ADD COLUMN "user_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."audit_logs" ADD COLUMN "user_id_uuid" UUID;
ALTER TABLE "platform_meeting_attendance"."data_access_logs" ADD COLUMN "actor_id_uuid" UUID;

-- Backfill creator/user references from legacy users
UPDATE "platform_meeting_attendance"."meetings" m
SET "creator_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE m."creator_id" = u."id";

UPDATE "platform_meeting_attendance"."meeting_series" s
SET "creator_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE s."creator_id" = u."id";

UPDATE "platform_meeting_attendance"."meeting_templates" t
SET "creator_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE t."creator_id" = u."id";

UPDATE "platform_meeting_attendance"."attendances" a
SET "user_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE a."user_id" = u."id";

UPDATE "platform_meeting_attendance"."meeting_required_attendees" r
SET "user_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE r."user_id" = u."id";

UPDATE "platform_meeting_attendance"."meeting_required_attendees" r
SET "user_id_uuid" = p."id"
FROM "platform_iam"."users" p
WHERE r."user_id_uuid" IS NULL
  AND r."email" IS NOT NULL
  AND LOWER(p."email") = LOWER(r."email");

UPDATE "platform_meeting_attendance"."meeting_template_attendees" t
SET "user_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE t."user_id" = u."id";

UPDATE "platform_meeting_attendance"."leave_records" l
SET "user_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE l."user_id" = u."id";

UPDATE "platform_meeting_attendance"."audit_logs" a
SET "user_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE a."user_id" = u."id";

UPDATE "platform_meeting_attendance"."audit_logs" a
SET "user_id_uuid" = p."id"
FROM "platform_iam"."users" p
WHERE a."user_id_uuid" IS NULL
  AND a."user_email" IS NOT NULL
  AND LOWER(p."email") = LOWER(a."user_email");

UPDATE "platform_meeting_attendance"."data_access_logs" d
SET "actor_id_uuid" = COALESCE(u."platform_user_id", p."id")
FROM "platform_meeting_attendance"."users" u
LEFT JOIN "platform_iam"."users" p ON LOWER(p."email") = LOWER(u."email")
WHERE d."actor_id" = u."id";

UPDATE "platform_meeting_attendance"."data_access_logs" d
SET "actor_id_uuid" = p."id"
FROM "platform_iam"."users" p
WHERE d."actor_id_uuid" IS NULL
  AND d."actor_email" IS NOT NULL
  AND LOWER(p."email") = LOWER(d."actor_email");

-- Validate required mappings
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."meetings" WHERE "creator_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: meetings.creator_id cannot be mapped to platform users';
  END IF;
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."meeting_series" WHERE "creator_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: meeting_series.creator_id cannot be mapped to platform users';
  END IF;
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."meeting_templates" WHERE "creator_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: meeting_templates.creator_id cannot be mapped to platform users';
  END IF;
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."attendances" WHERE "user_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: attendances.user_id cannot be mapped to platform users';
  END IF;
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."meeting_required_attendees" WHERE "user_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: meeting_required_attendees.user_id cannot be mapped to platform users';
  END IF;
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."meeting_template_attendees" WHERE "user_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: meeting_template_attendees.user_id cannot be mapped to platform users';
  END IF;
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."leave_records" WHERE "user_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: leave_records.user_id cannot be mapped to platform users';
  END IF;
  IF EXISTS (SELECT 1 FROM "platform_meeting_attendance"."audit_logs" WHERE "user_id_uuid" IS NULL) THEN
    RAISE EXCEPTION 'meeting_attendance migration: audit_logs.user_id cannot be mapped to platform users';
  END IF;
END $$;

-- Swap columns to new UUID references
ALTER TABLE "platform_meeting_attendance"."meetings" DROP COLUMN "creator_id";
ALTER TABLE "platform_meeting_attendance"."meetings" RENAME COLUMN "creator_id_uuid" TO "creator_id";

ALTER TABLE "platform_meeting_attendance"."meeting_series" DROP COLUMN "creator_id";
ALTER TABLE "platform_meeting_attendance"."meeting_series" RENAME COLUMN "creator_id_uuid" TO "creator_id";

ALTER TABLE "platform_meeting_attendance"."meeting_templates" DROP COLUMN "creator_id";
ALTER TABLE "platform_meeting_attendance"."meeting_templates" RENAME COLUMN "creator_id_uuid" TO "creator_id";

ALTER TABLE "platform_meeting_attendance"."attendances" DROP COLUMN "user_id";
ALTER TABLE "platform_meeting_attendance"."attendances" RENAME COLUMN "user_id_uuid" TO "user_id";

ALTER TABLE "platform_meeting_attendance"."meeting_required_attendees" DROP COLUMN "user_id";
ALTER TABLE "platform_meeting_attendance"."meeting_required_attendees" RENAME COLUMN "user_id_uuid" TO "user_id";

ALTER TABLE "platform_meeting_attendance"."meeting_template_attendees" DROP COLUMN "user_id";
ALTER TABLE "platform_meeting_attendance"."meeting_template_attendees" RENAME COLUMN "user_id_uuid" TO "user_id";

ALTER TABLE "platform_meeting_attendance"."leave_records" DROP COLUMN "user_id";
ALTER TABLE "platform_meeting_attendance"."leave_records" RENAME COLUMN "user_id_uuid" TO "user_id";

ALTER TABLE "platform_meeting_attendance"."audit_logs" DROP COLUMN "user_id";
ALTER TABLE "platform_meeting_attendance"."audit_logs" RENAME COLUMN "user_id_uuid" TO "user_id";

ALTER TABLE "platform_meeting_attendance"."data_access_logs" DROP COLUMN "actor_id";
ALTER TABLE "platform_meeting_attendance"."data_access_logs" RENAME COLUMN "actor_id_uuid" TO "actor_id";

-- Remove legacy attendee columns
ALTER TABLE "platform_meeting_attendance"."meeting_required_attendees"
  DROP COLUMN "company",
  DROP COLUMN "department",
  DROP COLUMN "email",
  DROP COLUMN "name";

-- Enforce NOT NULL for required columns
ALTER TABLE "platform_meeting_attendance"."meetings" ALTER COLUMN "creator_id" SET NOT NULL;
ALTER TABLE "platform_meeting_attendance"."meeting_series" ALTER COLUMN "creator_id" SET NOT NULL;
ALTER TABLE "platform_meeting_attendance"."meeting_templates" ALTER COLUMN "creator_id" SET NOT NULL;
ALTER TABLE "platform_meeting_attendance"."attendances" ALTER COLUMN "user_id" SET NOT NULL;
ALTER TABLE "platform_meeting_attendance"."meeting_required_attendees" ALTER COLUMN "user_id" SET NOT NULL;
ALTER TABLE "platform_meeting_attendance"."meeting_template_attendees" ALTER COLUMN "user_id" SET NOT NULL;
ALTER TABLE "platform_meeting_attendance"."leave_records" ALTER COLUMN "user_id" SET NOT NULL;
ALTER TABLE "platform_meeting_attendance"."audit_logs" ALTER COLUMN "user_id" SET NOT NULL;

-- Drop legacy auth tables and users
DROP TABLE "platform_meeting_attendance"."accounts";
DROP TABLE "platform_meeting_attendance"."sessions";
DROP TABLE "platform_meeting_attendance"."verification_tokens";
DROP TABLE "platform_meeting_attendance"."users";

-- Drop legacy role enum
DROP TYPE "platform_meeting_attendance"."MeetingAttendanceUserRole";

-- Recreate indexes
CREATE UNIQUE INDEX "attendances_user_id_meeting_id_key" ON "platform_meeting_attendance"."attendances"("user_id", "meeting_id");
CREATE INDEX "audit_logs_user_id_idx" ON "platform_meeting_attendance"."audit_logs"("user_id");
CREATE INDEX "data_access_logs_actor_id_idx" ON "platform_meeting_attendance"."data_access_logs"("actor_id");
CREATE UNIQUE INDEX "meeting_required_attendees_meeting_id_user_id_key" ON "platform_meeting_attendance"."meeting_required_attendees"("meeting_id", "user_id");
CREATE UNIQUE INDEX "meeting_template_attendees_template_id_user_id_key" ON "platform_meeting_attendance"."meeting_template_attendees"("template_id", "user_id");

-- Add new foreign keys to platform_iam.users
ALTER TABLE "platform_meeting_attendance"."meetings" ADD CONSTRAINT "meetings_creator_id_fkey" FOREIGN KEY ("creator_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_series" ADD CONSTRAINT "meeting_series_creator_id_fkey" FOREIGN KEY ("creator_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."attendances" ADD CONSTRAINT "attendances_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_required_attendees" ADD CONSTRAINT "meeting_required_attendees_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_templates" ADD CONSTRAINT "meeting_templates_creator_id_fkey" FOREIGN KEY ("creator_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_template_attendees" ADD CONSTRAINT "meeting_template_attendees_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."leave_records" ADD CONSTRAINT "leave_records_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."audit_logs" ADD CONSTRAINT "audit_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
