WITH latest_org_membership AS (
  SELECT
    u.organization_id,
    ms.plan_id,
    ms.status,
    ms.start_date,
    ms.end_date,
    ms.renews_at,
    ms.cancelled_at,
    ms.stripe_customer_id,
    ms.stripe_subscription_id,
    mp.organization_type AS plan_organization_type,
    row_number() OVER (
      PARTITION BY u.organization_id
      ORDER BY
        CASE WHEN ms.status = 'ACTIVE' THEN 0 ELSE 1 END,
        ms.created_at DESC,
        ms.id DESC
    ) AS rn
  FROM membership_subscriptions ms
  INNER JOIN users u ON u.id = ms.user_id
  INNER JOIN membership_plans mp ON mp.id = ms.plan_id
  WHERE u.membership_type = 'ORGANIZATION'
    AND u.organization_id IS NOT NULL
    AND mp.membership_type = 'ORGANIZATION'
    AND ms.status IN ('PENDING', 'ACTIVE')
),
candidate AS (
  SELECT *
  FROM latest_org_membership
  WHERE rn = 1
)
UPDATE organization_subscriptions os
SET
  plan_id = c.plan_id,
  status = c.status,
  start_date = c.start_date,
  end_date = c.end_date,
  renews_at = c.renews_at,
  cancelled_at = c.cancelled_at,
  stripe_customer_id = c.stripe_customer_id,
  stripe_subscription_id = c.stripe_subscription_id,
  updated_at = now()
FROM candidate c
WHERE os.organization_id = c.organization_id
  AND os.status IN ('PENDING', 'ACTIVE');

WITH latest_org_membership AS (
  SELECT
    u.organization_id,
    ms.plan_id,
    ms.status,
    ms.start_date,
    ms.end_date,
    ms.renews_at,
    ms.cancelled_at,
    ms.stripe_customer_id,
    ms.stripe_subscription_id,
    row_number() OVER (
      PARTITION BY u.organization_id
      ORDER BY
        CASE WHEN ms.status = 'ACTIVE' THEN 0 ELSE 1 END,
        ms.created_at DESC,
        ms.id DESC
    ) AS rn
  FROM membership_subscriptions ms
  INNER JOIN users u ON u.id = ms.user_id
  INNER JOIN membership_plans mp ON mp.id = ms.plan_id
  WHERE u.membership_type = 'ORGANIZATION'
    AND u.organization_id IS NOT NULL
    AND mp.membership_type = 'ORGANIZATION'
    AND ms.status IN ('PENDING', 'ACTIVE')
),
candidate AS (
  SELECT *
  FROM latest_org_membership
  WHERE rn = 1
)
INSERT INTO organization_subscriptions (
  organization_id,
  plan_id,
  status,
  start_date,
  end_date,
  renews_at,
  cancelled_at,
  stripe_customer_id,
  stripe_subscription_id,
  updated_at
)
SELECT
  c.organization_id,
  c.plan_id,
  c.status,
  c.start_date,
  c.end_date,
  c.renews_at,
  c.cancelled_at,
  c.stripe_customer_id,
  c.stripe_subscription_id,
  now()
FROM candidate c
WHERE NOT EXISTS (
  SELECT 1
  FROM organization_subscriptions os
  WHERE os.organization_id = c.organization_id
    AND os.status IN ('PENDING', 'ACTIVE')
);

WITH latest_org_membership AS (
  SELECT
    u.organization_id,
    ms.status,
    mp.organization_type AS plan_organization_type,
    row_number() OVER (
      PARTITION BY u.organization_id
      ORDER BY
        CASE WHEN ms.status = 'ACTIVE' THEN 0 ELSE 1 END,
        ms.created_at DESC,
        ms.id DESC
    ) AS rn
  FROM membership_subscriptions ms
  INNER JOIN users u ON u.id = ms.user_id
  INNER JOIN membership_plans mp ON mp.id = ms.plan_id
  WHERE u.membership_type = 'ORGANIZATION'
    AND u.organization_id IS NOT NULL
    AND mp.membership_type = 'ORGANIZATION'
    AND ms.status IN ('PENDING', 'ACTIVE')
),
candidate AS (
  SELECT *
  FROM latest_org_membership
  WHERE rn = 1
)
UPDATE organizations o
SET
  organization_type = COALESCE(o.organization_type, c.plan_organization_type),
  operational_status = CASE
    WHEN c.status = 'ACTIVE' AND o.operational_status = 'PENDING' THEN 'ACTIVE'
    ELSE o.operational_status
  END,
  updated_at = now()
FROM candidate c
WHERE o.id = c.organization_id
  AND (
    (o.organization_type IS NULL AND c.plan_organization_type IS NOT NULL)
    OR (
      c.status = 'ACTIVE'
      AND o.operational_status = 'PENDING'
    )
  );
