-- Create price_history table
CREATE TABLE IF NOT EXISTS price_history (
  id SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  price NUMERIC(10,2) NOT NULL,
  feed_id INTEGER REFERENCES feeds(id),
  recorded_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Index for fast lookups by product + date
CREATE INDEX IF NOT EXISTS idx_price_history_product_date ON price_history (product_id, recorded_at DESC);

-- Seed initial data from current product_offers
INSERT INTO price_history (product_id, price, feed_id, recorded_at)
SELECT product_id, price, feed_id, now()
FROM product_offers
WHERE in_stock = true;
