-- Additive supplier resources. Existing contacts, mails and cases are preserved.
CREATE TABLE IF NOT EXISTS vendor_files (
    id SERIAL PRIMARY KEY,
    vendor_id INTEGER NOT NULL REFERENCES vendors(id),
    filename TEXT NOT NULL,
    content_type TEXT NOT NULL,
    size_bytes INTEGER NOT NULL,
    content_data BYTEA NOT NULL,
    uploaded_by_user_id INTEGER REFERENCES users(user_id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_vendor_files_vendor ON vendor_files(vendor_id, created_at DESC);
CREATE TABLE IF NOT EXISTS vendor_contacts (
    vendor_id INTEGER NOT NULL REFERENCES vendors(id),
    contact_id INTEGER NOT NULL REFERENCES contacts(id),
    role VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (vendor_id, contact_id)
);
CREATE TABLE IF NOT EXISTS vendor_case_links (
    vendor_id INTEGER NOT NULL REFERENCES vendors(id),
    sag_id INTEGER NOT NULL REFERENCES sag_sager(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (vendor_id, sag_id)
);