CREATE TABLE IF NOT EXISTS sync_state (
  id TINYINT PRIMARY KEY,
  sequence BIGINT UNSIGNED NOT NULL DEFAULT 0,
  next_delivery_number INT UNSIGNED NOT NULL DEFAULT 1001
);
INSERT IGNORE INTO sync_state(id) VALUES (1);

CREATE TABLE IF NOT EXISTS customers (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at VARCHAR(24) NOT NULL,
  deleted_at VARCHAR(24) NULL
);
CREATE TABLE IF NOT EXISTS customer_addresses (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  customer_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  label VARCHAR(80) NOT NULL,
  address VARCHAR(300) NOT NULL,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at VARCHAR(24) NOT NULL,
  deleted_at VARCHAR(24) NULL,
  UNIQUE KEY address_customer (id, customer_id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE IF NOT EXISTS delivery_types (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  shipment_unit_hundredths INT UNSIGNED NOT NULL,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at VARCHAR(24) NOT NULL,
  deleted_at VARCHAR(24) NULL
);
CREATE TABLE IF NOT EXISTS deliveries (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  delivery_number INT UNSIGNED NOT NULL UNIQUE,
  customer_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  address_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  delivery_type_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  delivery_type_version INT UNSIGNED NOT NULL,
  shipment_unit_hundredths INT UNSIGNED NOT NULL,
  unit_price_agorot INT UNSIGNED NOT NULL,
  total_price_agorot BIGINT UNSIGNED NOT NULL,
  notes VARCHAR(500) NOT NULL DEFAULT '',
  status VARCHAR(20) NOT NULL DEFAULT 'created',
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created_at VARCHAR(24) NOT NULL,
  deleted_at VARCHAR(24) NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (address_id, customer_id) REFERENCES customer_addresses(id, customer_id),
  FOREIGN KEY (delivery_type_id) REFERENCES delivery_types(id)
);
CREATE TABLE IF NOT EXISTS sync_operations (
  operation_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  device_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  request_hash CHAR(64) NOT NULL,
  result_json JSON NOT NULL,
  created_at VARCHAR(24) NOT NULL
);
CREATE TABLE IF NOT EXISTS sync_changes (
  sequence BIGINT UNSIGNED PRIMARY KEY,
  entity_type VARCHAR(20) NOT NULL,
  entity_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  change_json JSON NOT NULL,
  INDEX entity_lookup (entity_type, entity_id, sequence)
);