DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_type t
    INNER JOIN pg_enum e ON e.enumtypid = t.oid
    WHERE t.typname = 'organization_member_obligation_status_enum'
      AND e.enumlabel = 'PENDING'
  ) THEN
    CREATE TYPE organization_member_obligation_status_enum AS ENUM (
      'PENDING',
      'PAID',
      'CANCELLED'
    );
  END IF;

  IF NOT EXISTS (
    SELECT 1
    FROM pg_type t
    INNER JOIN pg_enum e ON e.enumtypid = t.oid
    WHERE t.typname = 'organization_member_obligation_kind_enum'
      AND e.enumlabel = 'TREASURY'
  ) THEN
    CREATE TYPE organization_member_obligation_kind_enum AS ENUM (
      'TREASURY',
      'ORG_SUSU'
    );
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS organization_member_obligations (
  id serial PRIMARY KEY,
  organization_id integer NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  member_user_id integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  assigned_by_user_id integer REFERENCES users(id) ON DELETE SET NULL,
  kind organization_member_obligation_kind_enum NOT NULL DEFAULT 'TREASURY',
  title text NOT NULL,
  description text,
  amount numeric(12, 2) NOT NULL,
  currency text NOT NULL DEFAULT 'CAD',
  status organization_member_obligation_status_enum NOT NULL DEFAULT 'PENDING',
  due_date timestamp,
  paid_at timestamp,
  wallet_transaction_id integer,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS org_member_obligation_org_member_status_idx
  ON organization_member_obligations (organization_id, member_user_id, status);

CREATE INDEX IF NOT EXISTS org_member_obligation_org_created_idx
  ON organization_member_obligations (organization_id, created_at);
