migration.sql

begin;

create temp view fix_cancel as
select * from (values
    ( 10717, 'leggingbrasil', 'x77rxix', '9263355916', '2022-06-21', 'Migrate from legacy P2 (cancelled)'),
    ( 11306, 'yampa', 'wrcky48', '9784762736', '2022-10-08', 'Migrate from legacy P2 (cancelled)'),
    ( 12299, 'sharecare', 'qd2pvjb', '9943523200', '2023-08-30', 'Migrate from legacy P2 (cancelled)'),
    ( 13782, 'imobiliariamapa', 'qd2pvjb', '10574779039', '2024-10-18', 'Migrate from legacy P2 (cancelled)'),
    ( 14545, 'linkprime', 'rulld1h', '10930450850', '2023-09-02', 'Migrate from legacy P2 (cancelled)'),
    ( 14644, 'shoulder', 'evzsb33', '10985562756', '2024-11-30', 'Migrate from legacy P2 (cancelled)'),
    ( 15415, 'shoulder', 'qd2pvjb', '11454554165', '2024-11-30', 'Migrate from legacy P2 (cancelled)'),
    ( 15419, 'saudeweb', 'wrcky48', '11454733722', '2024-11-30', 'Migrate from legacy P2 (cancelled)'),
    ( 15662, 'solarvoltenergia', 'vyc6r0p', '11581347749', '2024-10-05', 'Migrate from legacy P2 (cancelled)'),
    ( 15892, 'belatam', 'qd2pvjb', '11674479642', '2024-12-31', 'Migrate from legacy P2 (cancelled)'),
    ( 16371, 'igdferramentas', 'wrcky48', '11969056903', '2024-12-24', 'Migrate from legacy P2 (cancelled)'),
    ( 16844, 'digitalviking', 'wrcky48', '12204119923', '2023-07-31', 'Migrate from legacy P2 (cancelled)'),
    ( 17233, 'foreverliss', 'evzsb33', '12463364422', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 18245, 'remaxhousy', 'wrcky48', '13059312660', '2024-11-30', 'Migrate from legacy P2 (cancelled)'),
    ( 19786, 'aespodonto', 's05ma1g', '13833021264', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 19886, 'vianet-telecomunicacoes', 'te8rcml', '13924953242', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 19921, 'shoulder', 'zlgrqwn', '13924117315', '2026-07-14', 'Migrate from legacy P2 (cancelled)'),
    ( 19929, 'vianet-telecomunicacoes', 'k513r7o', '13925016420', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 20046, 'sanar', 'k513r7o', '13983011193', '2024-09-30', 'Migrate from legacy P2 (cancelled)'),
    ( 20167, 'moveedu', 'ol3cy7d', '14451845263', '2024-06-27', 'Migrate from legacy P2 (cancelled)'),
    ( 20331, 'tahto', 'ygpfwwg', '15546817208', '2024-05-04', 'Migrate from legacy P2 (cancelled)'),
    ( 20510, 'vianet-telecomunicacoes', 'x5epboo', '14351287840', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 20597, 'buddhaspa', 'p28mk70', '14396106631', '2024-03-29', 'Migrate from legacy P2 (cancelled)'),
    ( 20650, 'vianet-telecomunicacoes', 'vyc6r0p', '14409254979', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 20675, 'celuladelta', 'p28mk70', '14423354429', '2023-11-30', 'Migrate from legacy P2 (cancelled)'),
    ( 21080, 'sicoobmetropolitano', 'ygpfwwg', '16101710111', '2024-03-31', 'Migrate from legacy P2 (cancelled)'),
    ( 21699, 'cda-tixwp', 'p28mk70', '19442982210', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 21811, 'ligaeducacional', 'vyc6r0p', '15222551981', '2024-04-08', 'Migrate from legacy P2 (cancelled)'),
    ( 21848, 'solarvoltenergia', 'crl7qdj', '15237846616', '2024-10-05', 'Migrate from legacy P2 (cancelled)'),
    ( 21891, 'grupohipercred', 'ntiqb7y', '15316063789', '2023-06-10', 'Migrate from legacy P2 (cancelled)'),
    ( 22182, 'suporteventos', 'zlgrqwn', '15450171726', '2024-06-14', 'Migrate from legacy P2 (cancelled)'),
    ( 22398, 'vianet-telecomunicacoes', 'etmpxaf', '15694574864', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 23043, 'renault', 'b5p1x9w', '16092343563', '2024-06-30', 'Migrate from legacy P2 (cancelled)'),
    ( 23120, 'ticketvibe', 'ec61kv6', '16165151334', '2024-06-30', 'Migrate from legacy P2 (cancelled)'),
    ( 23514, 'smartatacadors', 'ntiqb7y', '16808789880', '2024-06-30', 'Migrate from legacy P2 (cancelled)'),
    ( 23623, 'unimedjp', 'wrcky48', '16507744038', '2024-01-31', 'Migrate from legacy P2 (cancelled)'),
    ( 23680, 'srna', 'iy0r79l', '16574598180', '2024-11-28', 'Migrate from legacy P2 (cancelled)'),
    ( 23899, 'imobiliariamapa', 'xp2c99c', '16820204209', '2024-10-18', 'Migrate from legacy P2 (cancelled)'),
    ( 24188, 'fretebras', 'evzsb33', '17098665476', '2026-02-19', 'Migrate from legacy P2 (cancelled)'),
    ( 24197, 'active-campaign', 'wrcky48', '17100483505', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 24201, 'papadelivery', 'wrcky48', '17102750365', '2024-06-30', 'Migrate from legacy P2 (cancelled)'),
    ( 24447, 'worldheart', 'p28mk70', '17300339765', '2024-10-31', 'Migrate from legacy P2 (cancelled)'),
    ( 24453, 'worldheart', 'ygpfwwg', '17303793463', '2024-10-31', 'Migrate from legacy P2 (cancelled)'),
    ( 24785, 'karina', 'ntiqb7y', '18014556043', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 24903, 'uniodontosjc', 'reokqs2', '17761505669', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 25146, 'mundodosvistos', 'zlgrqwn', '17906495652', '2025-02-17', 'Migrate from legacy P2 (cancelled)'),
    ( 25294, 'checklistfacil', 'wu3t7mx', '18032182598', '2024-07-12', 'Migrate from legacy P2 (cancelled)'),
    ( 25433, 'consultibrasil', 'ntiqb7y', '18146305136', '2024-09-30', 'Migrate from legacy P2 (cancelled)'),
    ( 25445, 'abinbev', 'evzsb33', '18150497559', '2026-02-19', 'Migrate from legacy P2 (cancelled)'),
    ( 25695, 'luzoliveira', 's05ma1g', '18365891930', '2024-08-31', 'Migrate from legacy P2 (cancelled)'),
    ( 25832, 'srna', 'x5x822m', '18450891318', '2024-11-28', 'Migrate from legacy P2 (cancelled)'),
    ( 25978, 'overcome', 'tld61la', '18535704130', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 26133, '4uq', 'ntiqb7y', '18635539359', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 26233, 'kirvano', 'ec61kv6', '18708396469', '2024-06-30', 'Migrate from legacy P2 (cancelled)'),
    ( 26251, 'vianet-telecomunicacoes', 'trpbejm', '18727052686', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 26284, 'llzgarantidora', 'ntiqb7y', '18757280769', '2025-02-01', 'Migrate from legacy P2 (cancelled)'),
    ( 26286, 'llzgarantidora', 'sbcqxdd', '18757059337', '2025-02-01', 'Migrate from legacy P2 (cancelled)'),
    ( 26438, 'kirvano', 'reokqs2', '18876942400', '2024-06-30', 'Migrate from legacy P2 (cancelled)'),
    ( 26649, 'vianet-telecomunicacoes', 'b1lln7t', '19092608088', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 26755, 'anestesiacarioca', 'ntiqb7y', '19243464495', '2024-06-19', 'Migrate from legacy P2 (cancelled)'),
    ( 26850, 'vimotion', 'ec61kv6', '19340505256', '2024-07-31', 'Migrate from legacy P2 (cancelled)'),
    ( 27039, 'cicsm', 'p28mk70', '20058010792', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 27092, 'faculdadedunamis', 'wrcky48', '19521310496', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 27214, 'srna', 'rg4uccr', '19654765987', '2024-11-28', 'Migrate from legacy P2 (cancelled)'),
    ( 27419, 'vimotion', 'sbcqxdd', '19883623258', '2024-07-31', 'Migrate from legacy P2 (cancelled)'),
    ( 27512, 'llzgarantidora', 'zlgrqwn', '19985903449', '2025-02-01', 'Migrate from legacy P2 (cancelled)'),
    ( 27612, 'isopwall', 'reokqs2', '20058193787', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 28204, 'fleetdesk', 'p28mk70', '20620631621', '2025-01-04', 'Migrate from legacy P2 (cancelled)'),
    ( 28878, 'takeblip-paloma-lima', 'npoz0xe', '21196497548', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 29008, 'overcome', 'nusjqe0', '21290288308', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 29867, 'fcagroup', 'b5p1x9w', '22541147202', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 29960, 'bestbronze', 'w5guj3v', '22655703817', '2025-04-30', 'Migrate from legacy P2 (cancelled)'),
    ( 29991, 'srna', 'uuk9qxs', '22650233950', '2024-11-28', 'Migrate from legacy P2 (cancelled)'),
    ( 30009, 'fitofar', 'x5x822m', '22694689693', '2024-11-14', 'Migrate from legacy P2 (cancelled)'),
    ( 30140, 'juscash', 'reokqs2', '22868198175', '2024-12-10', 'Migrate from legacy P2 (cancelled)'),
    ( 30975, 'horizonplay', 'ntiqb7y', '28878372265', '2024-12-25', 'Migrate from legacy P2 (cancelled)'),
    ( 31568, 'grupoevandromonteironwj', 'reokqs2', '30162646385', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 31631, 'xcalotabrasil', 'nusjqe0', '30382785952', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 32186, 'florest', 'tld61la', '32680961589', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 32547, 'foreverliss', 'urkk37u', '33630206760', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 32895, 'isopwall', 'sbcqxdd', '34625778282', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 32898, 'unimedbh', 'wu3t7mx', '34618003490', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 32978, 'arbo', 'pv84el9', '34783196314', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 33093, 'fvlconsorcios', 'ec61kv6', '35105541015', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 33767, 'avantiopenbanking', 'wu3t7mx', '37120112432', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 33769, 'avantiopenbanking', 'spuu4ra', '37129719587', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 34342, 'takeblip-cosme-moreira', 'ygpfwwg', '38842789714', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 34497, 'avantiopenbanking', 'sbcqxdd', '39262931019', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 34498, 'avantiopenbanking', 'zlgrqwn', '39274225231', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 34505, 'avantiopenbanking', 'x5x822m', '39276651528', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 34746, 'avantiopenbanking', 'ec61kv6', '40234801208', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 34913, 'cda-tixwp', 'x5x822m', '40843868328', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 34914, 'cda-tixwp', 'zlgrqwn', '40850858425', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 34991, 'takeblip-lucasa', 'ub66f3x', '41144558346', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 35131, 'compliance-take', 'npoz0xe', '42021553161', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 35582, 'takerh', 'evzsb33', '43863997095', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 36107, 'camicado', 'ub66f3x', '47270352287', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)'),
    ( 38030, 'valuedesign', 'rg4uccr', '60325434140', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 38480, 'cda-demos-emea', 'rfm4j6j', '37542744164', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 38482, 'squad-proxima-centauri', 'rfm4j6j', '37537077089', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 38493, 'cda-demos-emea', 'ub66f3x', '38314525315', '2026-07-14', 'Migrate from legacy P2 (blip internal tests)'),
    ( 38520, 'squad-proxima-centauri', 'k513r7o', '40311951363', '2026-03-16', 'Migrate from legacy P2 (cancelled)'),
    ( 38648, 'machetazo', 'evzsb33', '29588715186', '2026-07-14', 'Migrate from legacy P2 (cancelled on hubspot)')) as t(subscription_id, tenant, plugin_id, deal_id, canceled_at, reason);

create temp view fix_activate as
select * from (values
    ('digitalbot',          'x5x822m', '16002266289', '2023-11-10 10:50'),
    ('ifoodbeneficios',     'evzsb33', '16163818884', '2023-11-21 12:07'),
    ('sicoobmetropolitano', 'ygpfwwg', '14708168590', '2023-08-17 11:53')
) as t(tenant, plugin_id, deal_id, subscribed_at);

-- before: 103 rows, all active, tenant_ok and plugin_ok true
select c.subscription_id, c.tenant, c.plugin_id, s.status,
       s.tenant_id = c.tenant as tenant_ok,
       v.plugin_id = c.plugin_id as plugin_ok
from fix_cancel c
left join store.plugin_subscriptions s on s.id = c.subscription_id
left join store.plugin_versions v on v.id = s.plugin_subscribed_version_id
order by c.subscription_id;

update store.plugin_subscriptions s
set status = 'canceled',
    canceled_by_identity = 'migration@blipstore',
    canceled_at = c.canceled_at::timestamptz,
    cancelation_reason = c.reason,
    hubspot_deal_id = c.deal_id,
    hubspot_synced_at = now(),
    updated_at = now()
from fix_cancel c, store.plugin_versions v
where s.id = c.subscription_id
  and s.tenant_id = c.tenant
  and v.id = s.plugin_subscribed_version_id
  and v.plugin_id = c.plugin_id
  and s.status in ('active', 'requested');

insert into store.plugin_subscriptions (
    plugin_subscribed_version_id, billing_plan_id, status, tenant_id,
    requested_by_identity, requested_at, subscribed_by_identity, subscribed_at,
    currency, hubspot_deal_id, hubspot_synced_at
)
select plan.version_id, plan.billing_plan_id, 'active', a.tenant,
       'migration@blipstore', a.subscribed_at::timestamptz, 'migration@blipstore', a.subscribed_at::timestamptz,
       case when plan.charge_type = 'free' then null else 'BRL' end,
       a.deal_id, now()
from fix_activate a
cross join lateral (
    select v.id as version_id, bp.id as billing_plan_id, bp.charge_type
    from store.plugin_versions v
    join store.plugin_versions_billing_plans bp on bp.plugin_version_id = v.id
    where v.plugin_id = a.plugin_id and v.status = 'approved'
    order by v.version desc, bp.is_recommended desc, bp.id
    limit 1
) plan;

update store.plugin_installations pi
set uninstalled_by_identity = 'migration@blipstore',
    uninstalled_at = c.canceled_at::timestamptz,
    updated_at = now()
from fix_cancel c
where pi.plugin_id = c.plugin_id
  and pi.tenant_id = c.tenant
  and pi.uninstalled_at is null;

-- after: 103 canceled + 3 active
select s.status, count(*)
from store.plugin_subscriptions s
where s.hubspot_deal_id in (select deal_id from fix_cancel union all select deal_id from fix_activate)
group by s.status;

rollback;