-- ============================================================================= -- MIGRATION 007: Change partner_id / tutor_id / actor_id columns INT → BIGINT -- PURPOSE: Zinzino partner IDs are 10-digit numbers (up to 9,999,999,999), -- exceeding PostgreSQL INT max (2,147,483,647). -- AFFECTED TABLES: -- partners, tutor_profiles, professor_profiles, superadmin_profiles, -- student_profiles, registrations_true, guests, student_lifecycle_log -- AFFECTED FUNCTIONS (redeploy separately after this migration): -- fn_validate_partner_id, fn_enroll_partner_system -- NOTE: Both views that reference partner_id columns must be dropped before -- altering column types and recreated at the end. -- CONSTRAINT NAMES verified against information_schema on 2026-05-26. -- ============================================================================= -- ── Step 1: Drop views ──────────────────────────────────────────────────────── DROP VIEW IF EXISTS public.vw_student_management_master; DROP VIEW IF EXISTS public.vw_user_identity_roles; -- ── Step 2: Drop FK constraints (exact names from information_schema) ───────── -- tutor_profiles → partners ALTER TABLE public.tutor_profiles DROP CONSTRAINT IF EXISTS tutor_profiles_partner_id_fkey; -- professor_profiles → partners ALTER TABLE public.professor_profiles DROP CONSTRAINT IF EXISTS fk_professor_profiles_partners; -- superadmin_profiles → partners ALTER TABLE public.superadmin_profiles DROP CONSTRAINT IF EXISTS fk_superadmin_profiles_partners; -- student_profiles → partners (partner_id FK) ALTER TABLE public.student_profiles DROP CONSTRAINT IF EXISTS student_profiles_partner_id_fkey; -- student_profiles → tutor_profiles (tutor_id FK) ALTER TABLE public.student_profiles DROP CONSTRAINT IF EXISTS student_profiles_tutor_id_fkey; -- registrations_true → tutor_profiles (tutor_id FK) ALTER TABLE public.registrations_true DROP CONSTRAINT IF EXISTS registrations_true_tutor_id_fkey; -- registrations_true → partners (student_id FK, nullable for guests) ALTER TABLE public.registrations_true DROP CONSTRAINT IF EXISTS registrations_true_student_id_fkey; -- guests → partners (tutor_id FK) ALTER TABLE public.guests DROP CONSTRAINT IF EXISTS guests_tutor_id_fkey; -- student_lifecycle_log: no FK constraints in DB (plain integer columns) -- DROP CONSTRAINT IF EXISTS is a safe no-op if constraints don't exist ALTER TABLE public.student_lifecycle_log DROP CONSTRAINT IF EXISTS student_lifecycle_log_student_id_fkey; ALTER TABLE public.student_lifecycle_log DROP CONSTRAINT IF EXISTS student_lifecycle_log_actor_id_fkey; ALTER TABLE public.student_lifecycle_log DROP CONSTRAINT IF EXISTS student_lifecycle_log_previous_tutor_id_fkey; ALTER TABLE public.student_lifecycle_log DROP CONSTRAINT IF EXISTS student_lifecycle_log_new_tutor_id_fkey; -- ── Step 3: Drop student_profiles composite PK (partner_id is part of it) ──── ALTER TABLE public.student_profiles DROP CONSTRAINT IF EXISTS student_profiles_pkey; -- ── Step 4: Alter all partner-referencing columns to BIGINT ─────────────────── -- Root identity table (referenced by all profile tables) ALTER TABLE public.partners ALTER COLUMN partner_id TYPE BIGINT; -- Profile tables ALTER TABLE public.tutor_profiles ALTER COLUMN partner_id TYPE BIGINT; ALTER TABLE public.professor_profiles ALTER COLUMN partner_id TYPE BIGINT; ALTER TABLE public.superadmin_profiles ALTER COLUMN partner_id TYPE BIGINT; -- student_profiles: composite PK column + tutor_id FK column ALTER TABLE public.student_profiles ALTER COLUMN partner_id TYPE BIGINT, ALTER COLUMN tutor_id TYPE BIGINT; -- Audit log: tutor_id + nullable student_id (NULL for guests via NULLIF) ALTER TABLE public.registrations_true ALTER COLUMN tutor_id TYPE BIGINT, ALTER COLUMN student_id TYPE BIGINT; -- Guest leads: tutor_id ALTER TABLE public.guests ALTER COLUMN tutor_id TYPE BIGINT; -- Lifecycle audit log: all partner-referencing columns ALTER TABLE public.student_lifecycle_log ALTER COLUMN student_id TYPE BIGINT, ALTER COLUMN previous_tutor_id TYPE BIGINT, ALTER COLUMN new_tutor_id TYPE BIGINT, ALTER COLUMN actor_id TYPE BIGINT; -- ── Step 5: Re-add student_profiles composite PK ────────────────────────────── ALTER TABLE public.student_profiles ADD CONSTRAINT student_profiles_pkey PRIMARY KEY (partner_id, course_id); -- ── Step 6: Re-add FK constraints (exact names and exact targets restored) ──── -- tutor_profiles → partners ALTER TABLE public.tutor_profiles ADD CONSTRAINT tutor_profiles_partner_id_fkey FOREIGN KEY (partner_id) REFERENCES public.partners(partner_id); -- professor_profiles → partners ALTER TABLE public.professor_profiles ADD CONSTRAINT fk_professor_profiles_partners FOREIGN KEY (partner_id) REFERENCES public.partners(partner_id); -- superadmin_profiles → partners ALTER TABLE public.superadmin_profiles ADD CONSTRAINT fk_superadmin_profiles_partners FOREIGN KEY (partner_id) REFERENCES public.partners(partner_id); -- student_profiles → partners (partner_id FK, separate from PK) ALTER TABLE public.student_profiles ADD CONSTRAINT student_profiles_partner_id_fkey FOREIGN KEY (partner_id) REFERENCES public.partners(partner_id); -- student_profiles → tutor_profiles (tutor must be a registered tutor) ALTER TABLE public.student_profiles ADD CONSTRAINT student_profiles_tutor_id_fkey FOREIGN KEY (tutor_id) REFERENCES public.tutor_profiles(partner_id); -- registrations_true → tutor_profiles (enrollment requires a valid tutor) ALTER TABLE public.registrations_true ADD CONSTRAINT registrations_true_tutor_id_fkey FOREIGN KEY (tutor_id) REFERENCES public.tutor_profiles(partner_id); -- registrations_true → partners (student_id NULL for guests via NULLIF) ALTER TABLE public.registrations_true ADD CONSTRAINT registrations_true_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.partners(partner_id); -- guests → partners (tutor who brought the guest) ALTER TABLE public.guests ADD CONSTRAINT guests_tutor_id_fkey FOREIGN KEY (tutor_id) REFERENCES public.partners(partner_id); -- ── Step 7: Recreate views ──────────────────────────────────────────────────── CREATE OR REPLACE VIEW public.vw_user_identity_roles AS SELECT p.partner_id, p.name, p.surname, p.email, p.phone, p.country, p.zinzino_range, CASE WHEN sa.partner_id IS NOT NULL AND sa.status = 'ACTIVE'::text THEN true ELSE false END AS is_superadmin, CASE WHEN pp.partner_id IS NOT NULL AND pp.status = 'ACTIVE'::text THEN true ELSE false END AS is_professor, CASE WHEN tp.partner_id IS NOT NULL AND tp.status::text = 'ACTIVE'::text THEN true ELSE false END AS is_tutor, CASE WHEN sp.partner_id IS NOT NULL AND sp.status = 'ACTIVE'::enrollment_status THEN true ELSE false END AS is_student FROM partners p LEFT JOIN superadmin_profiles sa ON p.partner_id = sa.partner_id LEFT JOIN professor_profiles pp ON p.partner_id = pp.partner_id LEFT JOIN tutor_profiles tp ON p.partner_id = tp.partner_id LEFT JOIN student_profiles sp ON p.partner_id = sp.partner_id; CREATE OR REPLACE VIEW public.vw_student_management_master AS SELECT sp.partner_id AS student_id, 'PARTNER'::text AS student_type, p.name AS student_name, p.surname AS student_surname, p.email AS student_email, p.phone AS student_phone, p.country AS student_country, p.zinzino_range AS student_zinzino_range, sp.level_id, sp.status AS enrollment_status, sp.remarks, sp.updated_at, sp.tutor_id, pt.name AS tutor_name, pt.surname AS tutor_surname, pt.email AS tutor_email FROM student_profiles sp JOIN partners p ON sp.partner_id = p.partner_id LEFT JOIN partners pt ON sp.tutor_id = pt.partner_id UNION ALL SELECT 0 AS student_id, 'GUEST'::text AS student_type, g.name AS student_name, g.surname AS student_surname, g.email AS student_email, g.phone AS student_phone, g.country AS student_country, 'Guest'::character varying AS student_zinzino_range, 0 AS level_id, 'ACTIVE'::text AS enrollment_status, NULL::character varying AS remarks, NULL::timestamp without time zone AS updated_at, g.tutor_id, pt.name AS tutor_name, pt.surname AS tutor_surname, pt.email AS tutor_email FROM guests g LEFT JOIN partners pt ON g.tutor_id = pt.partner_id;