-- RetailOS PostgreSQL schema
-- Safe to re-run: uses CREATE TABLE IF NOT EXISTS

CREATE TABLE IF NOT EXISTS categories (
  id text PRIMARY KEY,
  name text NOT NULL,
  emoji text NOT NULL DEFAULT '',
  tax real
);

CREATE TABLE IF NOT EXISTS products (
  id serial PRIMARY KEY,
  name text NOT NULL,
  sku text NOT NULL UNIQUE,
  category text NOT NULL,
  price real NOT NULL,
  cost real NOT NULL DEFAULT 0,
  stock integer NOT NULL DEFAULT 0,
  emoji text NOT NULL DEFAULT '📦'
);

CREATE TABLE IF NOT EXISTS customers (
  id serial PRIMARY KEY,
  name text NOT NULL,
  phone text NOT NULL,
  email text,
  points integer NOT NULL DEFAULT 0,
  spent real NOT NULL DEFAULT 0,
  visits integer NOT NULL DEFAULT 0,
  joined_at timestamp NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS suppliers (
  id serial PRIMARY KEY,
  name text NOT NULL,
  emoji text NOT NULL DEFAULT '🏭',
  contact text NOT NULL DEFAULT '',
  email text NOT NULL DEFAULT '',
  phone text NOT NULL DEFAULT '',
  categories text NOT NULL DEFAULT '',
  terms text NOT NULL DEFAULT 'Net 30',
  status text NOT NULL DEFAULT 'active',
  orders_count integer NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS staff (
  id serial PRIMARY KEY,
  name text NOT NULL,
  role text NOT NULL DEFAULT 'Cashier',
  email text NOT NULL UNIQUE,
  username text NOT NULL UNIQUE,
  password_hash text NOT NULL,
  status text NOT NULL DEFAULT 'active',
  last_active_at timestamp NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchase_orders (
  id serial PRIMARY KEY,
  po_number text NOT NULL UNIQUE,
  supplier_id integer,
  supplier_name text NOT NULL,
  items jsonb NOT NULL DEFAULT '[]'::jsonb,
  total real NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'draft',
  notes text NOT NULL DEFAULT '',
  order_date timestamp NOT NULL DEFAULT now(),
  expected_date timestamp
);

CREATE TABLE IF NOT EXISTS orders (
  id serial PRIMARY KEY,
  order_number text NOT NULL UNIQUE,
  customer_id integer,
  customer_name text,
  items jsonb NOT NULL DEFAULT '[]'::jsonb,
  subtotal real NOT NULL DEFAULT 0,
  discount real NOT NULL DEFAULT 0,
  tax real NOT NULL DEFAULT 0,
  total real NOT NULL DEFAULT 0,
  payment text NOT NULL DEFAULT 'cash',
  points_earned integer NOT NULL DEFAULT 0,
  note text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'completed',
  register_id integer,
  created_at timestamp NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS registers (
  id serial PRIMARY KEY,
  name text NOT NULL,
  location text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'closed',
  cashier text,
  shift text,
  sales real NOT NULL DEFAULT 0,
  txns integer NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS shifts (
  id serial PRIMARY KEY,
  register_id integer NOT NULL,
  register_name text NOT NULL,
  cashier text NOT NULL,
  shift_name text NOT NULL,
  opened_at timestamp NOT NULL DEFAULT now(),
  closed_at timestamp,
  sales real NOT NULL DEFAULT 0,
  txns integer NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'open',
  opening_cash real NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS held_orders (
  id serial PRIMARY KEY,
  customer_id integer,
  customer_name text,
  items jsonb NOT NULL DEFAULT '[]'::jsonb,
  total real NOT NULL DEFAULT 0,
  note text NOT NULL DEFAULT '',
  created_at timestamp NOT NULL DEFAULT now()
);