-- ============================================================================
-- INSIGHTFUL SAGA SCHEMA EXPORT — POSTGRES DDL
-- Generated: 2026-11-12T16:10:34.099Z
-- ============================================================================

CREATE TABLE IF NOT EXISTS users (
  user_id              BIGINT NOT NULL /* Unique customer identifier */,
  email                VARCHAR(255) NOT NULL /* Primary login email */,
  full_name            VARCHAR(128) /* Customer display name */,
  is_active            BOOLEAN NOT NULL DEFAULT TRUE /* Account status */,
  created_at           TIMESTAMP NOT NULL /* Registration timestamp */,
  CONSTRAINT pk_users PRIMARY KEY (user_id)
);

COMMENT ON TABLE users IS 'Registered customer profiles and credentials';

CREATE TABLE IF NOT EXISTS products (
  product_id           BIGINT NOT NULL /* SKU identifier */,
  product_name         VARCHAR(255) NOT NULL /* Item title */,
  sku                  VARCHAR(64) NOT NULL /* Stock keeping unit */,
  unit_price           DECIMAL(10,2) NOT NULL /* Base retail price */,
  category             VARCHAR(100) /* Product taxonomy group */,
  CONSTRAINT pk_products PRIMARY KEY (product_id)
);

COMMENT ON TABLE products IS 'Catalog of merchandise, skus, and pricing';

CREATE TABLE IF NOT EXISTS orders (
  order_id             BIGINT NOT NULL /* Transaction ID */,
  user_id              BIGINT NOT NULL /* Buyer user ID (FK to users) */,
  order_date           DATE NOT NULL /* Checkout date */,
  total_amount         DECIMAL(12,2) NOT NULL /* Gross invoice sum */,
  order_status         VARCHAR(32) NOT NULL /* Pending/Shipped/Delivered */,
  CONSTRAINT pk_orders PRIMARY KEY (order_id)
);

COMMENT ON TABLE orders IS 'Customer checkout transactions';

CREATE TABLE IF NOT EXISTS order_items (
  order_item_id        BIGINT NOT NULL /* Line item ID */,
  order_id             BIGINT NOT NULL /* Parent order ID */,
  product_id           BIGINT NOT NULL /* Referenced product ID */,
  quantity             INT NOT NULL DEFAULT 1 /* Quantity ordered */,
  item_price           DECIMAL(10,2) NOT NULL /* Unit price at purchase */,
  CONSTRAINT pk_order_items PRIMARY KEY (order_item_id)
);

COMMENT ON TABLE order_items IS 'Line-item junction connecting orders to products';