-- ============================================================
-- Finance records
-- ============================================================

CREATE TABLE app.finance_record (
  finance_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  amount NUMERIC(12,2) NOT NULL,
  currency TEXT NOT NULL DEFAULT 'EUR',
  record_type TEXT NOT NULL DEFAULT 'expense', -- expense, income, reimbursement
  purchased_by_user_id UUID REFERENCES app."user"(user_id),
  purchased_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  category TEXT NOT NULL DEFAULT 'operations', -- same enum as tasks/inventory
  created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_finance_record_category ON app.finance_record(category);
CREATE INDEX idx_finance_record_purchased_at ON app.finance_record(purchased_at);
CREATE INDEX idx_finance_record_purchased_by ON app.finance_record(purchased_by_user_id);

-- ============================================================
-- Finance links (polymorphic — links finance records to any entity)
-- ============================================================

CREATE TABLE app.finance_link (
  link_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  finance_id UUID NOT NULL REFERENCES app.finance_record(finance_id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL, -- inventory_item, inventory_request, stay, boat_trip, retreat, task, room
  entity_id UUID NOT NULL,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_finance_link_finance ON app.finance_link(finance_id);
CREATE INDEX idx_finance_link_entity ON app.finance_link(entity_type, entity_id);