CREATE TABLE IF NOT EXISTS shops (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_domain VARCHAR(255) NOT NULL UNIQUE,
  shop_name VARCHAR(255) NULL,
  email VARCHAR(255) NULL,
  currency_code VARCHAR(10) NULL,
  plan_name VARCHAR(100) NULL,
  timezone_name VARCHAR(100) NULL,
  shopify_gid VARCHAR(255) NULL,
  access_token TEXT NULL,
  scopes TEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  last_synced_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sync_cursors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope_key VARCHAR(100) NOT NULL UNIQUE,
  cursor_value VARCHAR(255) NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sync_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(100) NOT NULL,
  payload_json LONGTEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  available_at DATETIME NOT NULL,
  locked_at DATETIME NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_status_available (status, available_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS webhook_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  topic VARCHAR(150) NOT NULL,
  shop_domain VARCHAR(255) NOT NULL,
  webhook_id VARCHAR(255) NULL,
  hmac_valid TINYINT(1) NOT NULL DEFAULT 0,
  payload_json LONGTEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'received',
  error_message TEXT NULL,
  processed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  KEY idx_topic (topic),
  KEY idx_shop (shop_domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel VARCHAR(50) NOT NULL,
  request_type VARCHAR(50) NOT NULL,
  endpoint VARCHAR(255) NOT NULL,
  request_body LONGTEXT NULL,
  response_code INT NULL,
  response_body LONGTEXT NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_channel (channel),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS locations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shopify_gid VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  address1 VARCHAR(255) NULL,
  city VARCHAR(255) NULL,
  country_code VARCHAR(20) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shopify_gid VARCHAR(255) NOT NULL UNIQUE,
  legacy_resource_id BIGINT NULL,
  title VARCHAR(255) NOT NULL,
  handle VARCHAR(255) NULL,
  vendor VARCHAR(255) NULL,
  product_type VARCHAR(255) NULL,
  status VARCHAR(50) NULL,
  tags TEXT NULL,
  featured_image_url TEXT NULL,
  created_at_shopify DATETIME NULL,
  updated_at_shopify DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_title (title),
  KEY idx_handle (handle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_variants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id BIGINT UNSIGNED NOT NULL,
  shopify_gid VARCHAR(255) NOT NULL UNIQUE,
  legacy_resource_id BIGINT NULL,
  title VARCHAR(255) NOT NULL,
  sku VARCHAR(255) NULL,
  barcode VARCHAR(255) NULL,
  price DECIMAL(18,4) NULL,
  compare_at_price DECIMAL(18,4) NULL,
  inventory_quantity INT NULL,
  taxable TINYINT(1) NOT NULL DEFAULT 0,
  requires_shipping TINYINT(1) NOT NULL DEFAULT 0,
  weight_value DECIMAL(12,3) NULL,
  weight_unit VARCHAR(20) NULL,
  created_at_shopify DATETIME NULL,
  updated_at_shopify DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_variants_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  KEY idx_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shopify_gid VARCHAR(255) NOT NULL UNIQUE,
  legacy_resource_id BIGINT NULL,
  first_name VARCHAR(255) NULL,
  last_name VARCHAR(255) NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  orders_count INT NULL,
  amount_spent DECIMAL(18,4) NULL,
  default_address_json LONGTEXT NULL,
  tags TEXT NULL,
  created_at_shopify DATETIME NULL,
  updated_at_shopify DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shopify_gid VARCHAR(255) NOT NULL UNIQUE,
  legacy_resource_id BIGINT NULL,
  customer_id BIGINT UNSIGNED NULL,
  order_number VARCHAR(100) NULL,
  name VARCHAR(100) NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(100) NULL,
  currency_code VARCHAR(10) NULL,
  display_financial_status VARCHAR(100) NULL,
  display_fulfillment_status VARCHAR(100) NULL,
  total_price DECIMAL(18,4) NULL,
  subtotal_price DECIMAL(18,4) NULL,
  total_tax DECIMAL(18,4) NULL,
  total_shipping DECIMAL(18,4) NULL,
  source_name VARCHAR(100) NULL,
  tags TEXT NULL,
  note TEXT NULL,
  shipping_address_json LONGTEXT NULL,
  billing_address_json LONGTEXT NULL,
  created_at_shopify DATETIME NULL,
  updated_at_shopify DATETIME NULL,
  cancelled_at_shopify DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  KEY idx_order_number (order_number),
  KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  variant_id BIGINT UNSIGNED NULL,
  shopify_gid VARCHAR(255) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  sku VARCHAR(255) NULL,
  quantity INT NOT NULL DEFAULT 0,
  unit_price DECIMAL(18,4) NULL,
  total_discount DECIMAL(18,4) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL,
  KEY idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;