-- Migration 001: initial schema
-- Run with: psql $DATABASE_URL -f migrations/001_initial.sql

-- Enable the uuid-ossp extension for gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ── receipts ─────────────────────────────────────────────────────────────────
-- One row per submitted receipt image/form
CREATE TABLE IF NOT EXISTS receipts (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    store_name   TEXT,
    receipt_date DATE        NOT NULL,
    image_url    TEXT,                         -- S3/R2 URL after upload
    city         TEXT,
    submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS receipts_date_idx ON receipts (receipt_date);

-- ── line_items ────────────────────────────────────────────────────────────────
-- Individual products parsed from a receipt
CREATE TABLE IF NOT EXISTS line_items (
    id             UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
    receipt_id     UUID    NOT NULL REFERENCES receipts(id) ON DELETE CASCADE,
    raw_name       TEXT    NOT NULL,        -- exactly as on the receipt
    canonical_name TEXT,                   -- normalized key, e.g. "milk_whole_1gal"
    category       TEXT,                   -- "dairy", "produce", etc.
    price_cents    INT     NOT NULL CHECK (price_cents > 0),
    quantity       NUMERIC NOT NULL DEFAULT 1 CHECK (quantity > 0)
);

CREATE INDEX IF NOT EXISTS line_items_receipt_idx   ON line_items (receipt_id);
CREATE INDEX IF NOT EXISTS line_items_canonical_idx ON line_items (canonical_name);

-- ── price_snapshots ───────────────────────────────────────────────────────────
-- Pre-aggregated monthly averages — rebuilt by the Go worker every hour.
-- The frontend reads from here, never from line_items directly.
CREATE TABLE IF NOT EXISTS price_snapshots (
    canonical_name TEXT NOT NULL,
    year_month     DATE NOT NULL,           -- truncated to first of the month
    avg_price_cents INT  NOT NULL,
    sample_count    INT  NOT NULL DEFAULT 0,
    PRIMARY KEY (canonical_name, year_month)
);

-- ── canonical_items ───────────────────────────────────────────────────────────
-- Registry of known items and their human-readable display names.
-- Populated by hand / future admin UI.
CREATE TABLE IF NOT EXISTS canonical_items (
    id           TEXT  PRIMARY KEY,          -- e.g. "milk_whole_1gal"
    display_name TEXT  NOT NULL,             -- "Whole Milk (1 gal)"
    category     TEXT  NOT NULL,
    unit         TEXT,                       -- "gallon", "dozen", "lb"
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed some known items
INSERT INTO canonical_items (id, display_name, category, unit) VALUES
    ('milk_whole_1gal',    'Whole Milk',           'dairy',     'gallon'),
    ('milk_2pct_1gal',     '2% Milk',              'dairy',     'gallon'),
    ('eggs_large_dozen',   'Eggs (large, dozen)',  'dairy',     'dozen'),
    ('butter_salted_1lb',  'Butter (salted)',       'dairy',     'lb'),
    ('bread_white_loaf',   'White Bread',           'bakery',    'loaf'),
    ('ground_beef_1lb',    'Ground Beef',           'meat',      'lb'),
    ('chicken_breast_1lb', 'Chicken Breast',        'meat',      'lb'),
    ('olive_oil_16oz',     'Olive Oil',             'pantry',    '16oz'),
    ('rice_white_2lb',     'White Rice',            'pantry',    '2lb bag'),
    ('apples_bag',         'Apples',                'produce',   'bag'),
    ('bananas_1lb',        'Bananas',               'produce',   'lb'),
    ('potatoes_5lb',       'Potatoes',              'produce',   '5lb bag')
ON CONFLICT (id) DO NOTHING;