-- Create our DB to work from
CREATE DATABASE collector;
\c collector

-- Modeling the businesses that we track overall
CREATE TABLE businesses (
    business_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_name VARCHAR(255) NOT NULL
);

-- Modeling the products
CREATE TABLE products (
    -- Internal product ID
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Store-specific Identifiers
    business_id UUID REFERENCES businesses (business_id) NOT NULL,

    -- Product Details
    product_name VARCHAR(255) NOT NULL,
    product_type VARCHAR(100), -- e.g., 'service', 'goods'

    -- Pricing (Numeric is preferred over Float for money)
    -- Also tracking in USD at the time
    price NUMERIC(12, 2) NOT NULL,
    currency VARCHAR(100) NOT NULL,

    -- Also tracking the price per unit
    unit_type VARCHAR(100) NOT NULL, -- e.g. 'item', 'weight', 'hour', etc.

    -- Tracking time that we checked
    track_time TIMESTAMPTZ DEFAULT current_timestamp
);