order identifier

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  order_number VARCHAR(50) UNIQUE NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  order_status VARCHAR(50) DEFAULT 'PENDING',
  subtotal_amount DECIMAL(18,2) NOT NULL,
  tax_amount DECIMAL(18,2) NOT NULL,
  shipping_amount DECIMAL(18,2) NOT NULL,
  total_amount DECIMAL(18,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  CONSTRAINT fk_customer 
    FOREIGN KEY (customer_id) 
    REFERENCES customers(customer_id),
    
  CONSTRAINT chk_status 
    CHECK (order_status IN (
      'PENDING', 'CONFIRMED', 'PROCESSING', 
      'SHIPPED', 'DELIVERED', 'CANCELLED', 'REFUNDED'
    ))
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date DESC);

CREATE TABLE order_items (
  order_item_id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(18,2) NOT NULL,
  discount_amount DECIMAL(18,2) DEFAULT 0.00,
  total_amount DECIMAL(18,2) NOT NULL,
  
  CONSTRAINT fk_order 
    FOREIGN KEY (order_id) 
    REFERENCES orders(order_id)
    ON DELETE CASCADE,
    
  CONSTRAINT fk_product 
    FOREIGN KEY (product_id) 
    REFERENCES products(product_id),
    
  CONSTRAINT chk_quantity 
    CHECK (quantity > 0)
);

CREATE INDEX idx_order_items_order ON order_items(order_id);