CREATE TABLE suppliers (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug        TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    url         TEXT,
    is_organic  BOOLEAN NOT NULL DEFAULT FALSE,
    is_demeter  BOOLEAN NOT NULL DEFAULT FALSE,
    country     TEXT,
    notes       TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE cultivar_suppliers (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cultivar_id     UUID NOT NULL REFERENCES cultivars(id) ON DELETE CASCADE,
    supplier_id     UUID NOT NULL REFERENCES suppliers(id) ON DELETE CASCADE,
    article_number  TEXT,
    product_url     TEXT,
    price_eur       DOUBLE PRECISION,
    pack_size       DOUBLE PRECISION,
    pack_unit       TEXT,
    last_checked_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(cultivar_id, supplier_id, article_number)
);

CREATE INDEX idx_cs_cultivar ON cultivar_suppliers(cultivar_id);
CREATE INDEX idx_cs_supplier ON cultivar_suppliers(supplier_id);