DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_type
    WHERE typname = 'organization_type_enum'
  ) THEN
    CREATE TYPE organization_type_enum AS ENUM (
      'SCHOOL',
      'GRASSROOTS_ROSCA',
      'NFP_ALUMNI_FAITH'
    );
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_type
    WHERE typname = 'organization_operational_status_enum'
  ) THEN
    CREATE TYPE organization_operational_status_enum AS ENUM (
      'PENDING',
      'ACTIVE',
      'SUSPENDED',
      'ARCHIVED'
    );
  END IF;
END $$;

ALTER TABLE organizations
  ADD COLUMN IF NOT EXISTS organization_type organization_type_enum,
  ADD COLUMN IF NOT EXISTS operational_status organization_operational_status_enum NOT NULL DEFAULT 'PENDING';

ALTER TABLE membership_plans
  ADD COLUMN IF NOT EXISTS organization_type organization_type_enum,
  ADD COLUMN IF NOT EXISTS entitlements json NOT NULL DEFAULT '{}'::json;

DROP INDEX IF EXISTS membership_plans_type_tier_uidx;
DROP INDEX IF EXISTS membership_plans_non_org_type_tier_uidx;
DROP INDEX IF EXISTS membership_plans_org_type_tier_uidx;
DROP INDEX IF EXISTS membership_plans_org_legacy_tier_uidx;

CREATE INDEX IF NOT EXISTS membership_plans_non_org_type_tier_uidx
ON membership_plans(membership_type, tier)
WHERE membership_type <> 'ORGANIZATION';

CREATE INDEX IF NOT EXISTS membership_plans_org_type_tier_uidx
ON membership_plans(organization_type, tier)
WHERE membership_type = 'ORGANIZATION' AND organization_type IS NOT NULL;

CREATE INDEX IF NOT EXISTS membership_plans_org_legacy_tier_uidx
ON membership_plans(tier)
WHERE membership_type = 'ORGANIZATION' AND organization_type IS NULL;

CREATE TABLE IF NOT EXISTS organization_subscriptions (
  id serial PRIMARY KEY,
  organization_id integer NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  plan_id integer NOT NULL REFERENCES membership_plans(id),
  status membership_status_enum NOT NULL DEFAULT 'PENDING',
  start_date timestamp,
  end_date timestamp,
  renews_at timestamp,
  cancelled_at timestamp,
  stripe_customer_id text,
  stripe_subscription_id text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS org_sub_org_status_created_idx
ON organization_subscriptions(organization_id, status, created_at);

CREATE UNIQUE INDEX IF NOT EXISTS org_sub_org_active_uidx
ON organization_subscriptions(organization_id)
WHERE status in ('PENDING', 'ACTIVE');
