-- Migration 006: Add uniqueness constraints to budgets and categories
-- Safe deduplication runs first inside the transaction before each constraint.

BEGIN;

-- Deduplicate budgets: keep the oldest row per (user_id, start_date)
DELETE FROM budgets
WHERE id NOT IN (
  SELECT DISTINCT ON (user_id, start_date) id
  FROM budgets
  ORDER BY user_id, start_date, created_at ASC
);

ALTER TABLE budgets
  ADD CONSTRAINT budgets_user_month_unique UNIQUE (user_id, start_date);

-- Deduplicate categories: keep the oldest row per (user_id, name)
DELETE FROM categories
WHERE id NOT IN (
  SELECT DISTINCT ON (user_id, name) id
  FROM categories
  ORDER BY user_id, name, created_at ASC
);

ALTER TABLE categories
  ADD CONSTRAINT categories_user_name_unique UNIQUE (user_id, name);

COMMIT;