-- Safe, repeatable MIPS tenant cleanup. Run after a backup.
START TRANSACTION;

INSERT INTO membership_types (name,description,renewal_month,grace_period_months,fee,prefix)
SELECT 'Full Member','Full individual membership',4,0,0,'FM'
WHERE NOT EXISTS (SELECT 1 FROM membership_types WHERE name='Full Member');
SET @full_member_id := (SELECT id FROM membership_types WHERE name='Full Member' ORDER BY id LIMIT 1);
UPDATE membership_subscriptions SET membership_type_id=@full_member_id WHERE membership_type_id<>@full_member_id;
UPDATE invoice_batch_recipients SET membership_type_id=@full_member_id WHERE membership_type_id IS NOT NULL AND membership_type_id<>@full_member_id;
DELETE FROM membership_types WHERE id<>@full_member_id;

INSERT INTO corporate_membership_types (name,description,renewal_month,grace_period_months,fee,is_active)
SELECT 'Corporate Member','Corporate membership',4,0,0,1
WHERE NOT EXISTS (SELECT 1 FROM corporate_membership_types WHERE name='Corporate Member');
SET @corporate_member_id := (SELECT id FROM corporate_membership_types WHERE name='Corporate Member' ORDER BY id LIMIT 1);
UPDATE company_corporate_subscriptions SET corporate_membership_type_id=@corporate_member_id WHERE corporate_membership_type_id<>@corporate_member_id;
UPDATE corporate_subscription_history SET corporate_membership_type_id=@corporate_member_id WHERE corporate_membership_type_id IS NOT NULL AND corporate_membership_type_id<>@corporate_member_id;
DELETE FROM corporate_membership_types WHERE id<>@corporate_member_id;

DELETE FROM users WHERE is_active=0;
DELETE FROM companies WHERE is_active=0;

INSERT INTO system_settings(setting_key,setting_value) VALUES
 ('system_name','MIPS Membersync'),('primary_color','#303171'),('secondary_color','#CE9B52')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);
UPDATE system_settings SET setting_value=REPLACE(REPLACE(setting_value,'MyIMM','MIPS'),'IMM','MIPS')
WHERE setting_value LIKE '%MyIMM%' OR setting_value LIKE '%IMM%';
UPDATE document_templates SET
 name=REPLACE(REPLACE(name,'MyIMM','MIPS'),'IMM','MIPS'),
 template_html=REPLACE(REPLACE(template_html,'MyIMM','MIPS'),'IMM','MIPS'),
 custom_css=REPLACE(REPLACE(COALESCE(custom_css,''),'MyIMM','MIPS'),'IMM','MIPS');

DELETE FROM payment_gateways WHERE UPPER(name) LIKE 'MALIPO%';

-- Clear legacy notifications whose old deep links no longer belong to MIPSApp.
DELETE FROM app_notification_reads;
DELETE FROM app_notifications;

COMMIT;