-- Exchange rates table for daily currency conversion
CREATE TABLE app.exchange_rate (
  rate_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  base_currency TEXT NOT NULL DEFAULT 'EGP',
  target_currency TEXT NOT NULL,
  rate NUMERIC(12,6) NOT NULL,
  fetched_at DATE NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_exchange_rate_unique ON app.exchange_rate(base_currency, target_currency, fetched_at);
CREATE INDEX idx_exchange_rate_date ON app.exchange_rate(fetched_at DESC);