-- Run once by scripts/pg-up.sh as the superuser. Deterministic data: no random().
CREATE TABLE customers (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  email text NOT NULL,
  country text NOT NULL,
  created_at date NOT NULL
);
CREATE TABLE products (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  category text NOT NULL,
  price numeric(10,2) NOT NULL
);
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  status text NOT NULL CHECK (status IN ('paid','shipped','refunded','cancelled')),
  ordered_at date NOT NULL
);
CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders(id),
  product_id bigint NOT NULL REFERENCES products(id),
  quantity int NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
-- A table the assistant must never see.
CREATE TABLE api_keys (
  id serial PRIMARY KEY,
  owner text NOT NULL,
  secret text NOT NULL
);

COMMENT ON TABLE customers IS 'one row per customer';
COMMENT ON COLUMN customers.country IS 'ISO country code: US, DE, IN, GB or FR';
COMMENT ON TABLE products IS 'one row per product';
COMMENT ON COLUMN products.category IS 'one of: coffee, tea, gear';
COMMENT ON COLUMN products.price IS 'unit price in USD';
COMMENT ON TABLE orders IS 'one row per order; money lives in order_items x products.price';
COMMENT ON COLUMN orders.status IS 'one of: paid, shipped, refunded, cancelled';
COMMENT ON TABLE order_items IS 'one row per product on an order';

INSERT INTO customers
SELECT i, 'Customer ' || i, 'c' || i || '@example.test',
       (ARRAY['US','DE','IN','GB','FR'])[1 + i % 5], DATE '2024-01-01' + (i % 300)
FROM generate_series(1, 200) i;

INSERT INTO products
SELECT i, 'Product ' || i, (ARRAY['coffee','tea','gear'])[1 + i % 3], 5 + i
FROM generate_series(1, 20) i;

-- customers 191..200 never order, so "customers with no orders" has an answer
INSERT INTO orders
SELECT i, 1 + (i * 7) % 190, (ARRAY['paid','shipped','refunded','cancelled'])[1 + i % 4],
       DATE '2025-01-01' + (i % 365)
FROM generate_series(1, 1000) i;

INSERT INTO order_items
SELECT i, 1 + (i * 3) % 20, 1 + i % 3 FROM generate_series(1, 1000) i;
INSERT INTO order_items
SELECT i, 1 + (i * 3 + 7) % 20, 1 + (i + 1) % 3 FROM generate_series(1, 1000) i;
INSERT INTO order_items
SELECT i, 1 + (i * 3 + 13) % 20, 2 FROM generate_series(1, 1000) i WHERE i % 2 = 0;

INSERT INTO api_keys (owner, secret) VALUES ('billing', 'sk-live-0000-not-a-real-key');

-- The role the assistant connects as.
CREATE ROLE t2s_reader LOGIN PASSWORD 'reader' NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE t2s TO t2s_reader;
GRANT USAGE ON SCHEMA public TO t2s_reader;
-- customers.email is personal data: the assistant gets column-level access without it
GRANT SELECT (id, name, country, created_at) ON customers TO t2s_reader;
GRANT SELECT ON products, orders, order_items TO t2s_reader;
ALTER ROLE t2s_reader SET default_transaction_read_only = on;
ALTER ROLE t2s_reader SET statement_timeout = '2s';