CREATE TABLE sales (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  order_total DECIMAL NOT NULL DEFAULT 0,
  shipping_info JSON,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sale_line_items (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_type VARCHAR NOT NULL,
  product_id VARCHAR(64),
  unit_price DECIMAL NOT NULL,
  quantity INTEGER NOT NULL,
  free INTEGER NOT NULL,
  sales_tax DECIMAL,
  shipping_handling DECIMAL NOT NULL,
  recurly_plan_code VARCHAR NOT NULL,
  recurly_subscription_uuid VARCHAR,
  sale_id VARCHAR(64) NOT NULL REFERENCES sales(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE recurly_transaction_web_hooks (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  recurly_transaction_id VARCHAR NOT NULL,
  transaction_type VARCHAR NOT NULL,
  subscription_id VARCHAR NOT NULL,
  action VARCHAR NOT NULL,
  status VARCHAR NOT NULL,
  amount_in_cents INT,
  user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  invoice_id VARCHAR,
  invoice_number_prefix VARCHAR,
  invoice_number INTEGER,
  message VARCHAR,
  reference VARCHAR,
  transaction_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);



CREATE UNIQUE INDEX sale_line_items_recurly_subscription_uuid_ndx ON sale_line_items(recurly_subscription_uuid);
CREATE INDEX recurly_transaction_web_hooks_subscription_id_ndx ON recurly_transaction_web_hooks(subscription_id);
CREATE UNIQUE INDEX jam_track_rights_recurly_subscription_uuid_ndx ON jam_track_rights(recurly_subscription_uuid);