PostgreSQL Upsert ON CONFLICT Do Update

-- PostgreSQL 活跃订阅表：软删除行的唯一性由部分索引保证
CREATE TABLE subscriptions (
  customer_id bigint NOT NULL,
  plan text NOT NULL,
  seats int NOT NULL,
  status text NOT NULL DEFAULT 'active',
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_subs_customer_active
  ON subscriptions (customer_id)
  WHERE status = 'active';

-- 幂等升级座位数：冲突时累加而非覆盖
INSERT INTO subscriptions (customer_id, plan, seats)
VALUES (42, 'pro', 10)
ON CONFLICT (customer_id) WHERE status = 'active'
DO UPDATE SET
  seats = subscriptions.seats + EXCLUDED.seats,
  updated_at = now()
WHERE subscriptions.seats + EXCLUDED.seats <= 1000;  -- 上限守卫

-- 不更新版本：纯粹忽略重复插入
INSERT INTO subscriptions (customer_id, plan, seats)
VALUES (43, 'basic', 5)
ON CONFLICT (customer_id) WHERE status = 'active'
DO NOTHING;

-- 批量同步：EXCLUDED 引用每行的拟插入值
INSERT INTO subscriptions (customer_id, plan, seats)
SELECT customer_id, 'pro', seat_count
FROM staging_seats
ON CONFLICT (customer_id) WHERE status = 'active'
DO UPDATE SET
  seats = EXCLUDED.seats,
  plan = EXCLUDED.plan,
  updated_at = now()
RETURNING customer_id, seats;