cloud-host/backend/migrations/007_optional_service_pricing_matrix.sql

-- Optional services: resource profile + pricing matrix (same model as application runtimes)

CREATE TABLE IF NOT EXISTS optional_service_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  service VARCHAR NOT NULL UNIQUE,
  cpu_limit VARCHAR NOT NULL DEFAULT '200m',
  memory_limit VARCHAR NOT NULL DEFAULT '256Mi',
  storage_gi DECIMAL(10, 2) NOT NULL DEFAULT 0,
  log_shipper_cpu_limit VARCHAR,
  log_shipper_memory_limit VARCHAR,
  "createdAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  "updatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS optional_service_rates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  service VARCHAR NOT NULL,
  resource_type VARCHAR NOT NULL,
  hourly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
  monthly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
  yearly_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  "createdAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  "updatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (service, resource_type)
);

INSERT INTO optional_service_profiles (service, cpu_limit, memory_limit, storage_gi, log_shipper_cpu_limit, log_shipper_memory_limit)
VALUES
  ('redis', '200m', '256Mi', 1, NULL, NULL),
  ('rabbitmq', '500m', '512Mi', 2, NULL, NULL),
  ('elasticsearch', '50m', '64Mi', 0, '50m', '64Mi')
ON CONFLICT (service) DO NOTHING;

UPDATE optional_service_profiles SET
  log_shipper_cpu_limit = '50m',
  log_shipper_memory_limit = '64Mi'
WHERE service = 'elasticsearch' AND log_shipper_cpu_limit IS NULL;

INSERT INTO optional_service_rates (service, resource_type, hourly_price, monthly_price, yearly_price)
SELECT s.service, t.resource_type, 0, 0, 0
FROM (VALUES ('redis'), ('rabbitmq'), ('elasticsearch')) AS s(service)
CROSS JOIN (
  VALUES ('base_fee'), ('cpu_per_core'), ('memory_per_gb'), ('storage_per_gb')
) AS t(resource_type)
ON CONFLICT (service, resource_type) DO NOTHING;

-- Migrate legacy per-resource addon prices into optional_service_rates (if columns exist)
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'addon_rates' AND column_name = 'cpu_hourly_price'
  ) THEN
    UPDATE optional_service_rates osr SET
      hourly_price = COALESCE(ar.cpu_hourly_price, 0),
      monthly_price = COALESCE(ar.cpu_monthly_price, 0),
      yearly_price = COALESCE(ar.cpu_yearly_price, 0)
    FROM addon_rates ar
    WHERE ar.resource_type = 'redis_addon' AND osr.service = 'redis' AND osr.resource_type = 'cpu_per_core'
      AND (ar.cpu_hourly_price > 0 OR ar.cpu_monthly_price > 0);

    UPDATE optional_service_rates osr SET
      hourly_price = COALESCE(ar.memory_hourly_price, 0),
      monthly_price = COALESCE(ar.memory_monthly_price, 0),
      yearly_price = COALESCE(ar.memory_yearly_price, 0)
    FROM addon_rates ar
    WHERE ar.resource_type = 'redis_addon' AND osr.service = 'redis' AND osr.resource_type = 'memory_per_gb'
      AND (ar.memory_hourly_price > 0 OR ar.memory_monthly_price > 0);

    UPDATE optional_service_rates osr SET
      hourly_price = COALESCE(ar.storage_hourly_price, 0),
      monthly_price = COALESCE(ar.storage_monthly_price, 0),
      yearly_price = COALESCE(ar.storage_yearly_price, 0)
    FROM addon_rates ar
    WHERE ar.resource_type = 'redis_addon' AND osr.service = 'redis' AND osr.resource_type = 'storage_per_gb'
      AND (ar.storage_hourly_price > 0 OR ar.storage_monthly_price > 0);

    UPDATE optional_service_profiles osp SET
      cpu_limit = COALESCE(ar.cpu_limit, osp.cpu_limit),
      memory_limit = COALESCE(ar.memory_limit, osp.memory_limit),
      storage_gi = COALESCE(ar.storage_gi, osp.storage_gi)
    FROM addon_rates ar WHERE ar.resource_type = 'redis_addon' AND osp.service = 'redis';

    UPDATE optional_service_profiles osp SET
      cpu_limit = COALESCE(ar.cpu_limit, osp.cpu_limit),
      memory_limit = COALESCE(ar.memory_limit, osp.memory_limit),
      storage_gi = COALESCE(ar.storage_gi, osp.storage_gi)
    FROM addon_rates ar WHERE ar.resource_type = 'rabbitmq_addon' AND osp.service = 'rabbitmq';

    UPDATE optional_service_profiles osp SET
      cpu_limit = COALESCE(ar.cpu_limit, osp.cpu_limit),
      memory_limit = COALESCE(ar.memory_limit, osp.memory_limit),
      log_shipper_cpu_limit = COALESCE(ar.log_shipper_cpu_limit, osp.log_shipper_cpu_limit),
      log_shipper_memory_limit = COALESCE(ar.log_shipper_memory_limit, osp.log_shipper_memory_limit)
    FROM addon_rates ar WHERE ar.resource_type = 'elasticsearch_addon' AND osp.service = 'elasticsearch';
  END IF;
END $$;