-- E-ticaret çekirdek şema (idempotent). prod-bootstrap ile uyumlu tablo adları: ecommerce_* .

CREATE TABLE IF NOT EXISTS ecommerce_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_number VARCHAR(32) NOT NULL,
  public_token CHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending_payment',
  currency CHAR(3) NOT NULL DEFAULT 'TRY',
  subtotal_try DECIMAL(12, 2) NOT NULL DEFAULT 0,
  shipping_try DECIMAL(12, 2) NOT NULL DEFAULT 0,
  discount_try DECIMAL(12, 2) NOT NULL DEFAULT 0,
  total_try DECIMAL(12, 2) NOT NULL DEFAULT 0,
  customer_email VARCHAR(255) NOT NULL,
  customer_name VARCHAR(255) NOT NULL,
  customer_phone VARCHAR(64) NOT NULL,
  buyer_identity_no VARCHAR(11) NOT NULL DEFAULT '',
  payment_status VARCHAR(32) NOT NULL DEFAULT 'pending',
  iyzico_conversation_id VARCHAR(64) NULL DEFAULT NULL,
  coupon_code VARCHAR(32) NULL DEFAULT NULL,
  coupon_percent_off TINYINT UNSIGNED NULL DEFAULT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ecommerce_order_number (order_number),
  UNIQUE KEY uq_ecommerce_public_token (public_token),
  KEY idx_ecommerce_orders_status (status),
  KEY idx_ecommerce_orders_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_order_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(32) NOT NULL,
  to_status VARCHAR(32) NOT NULL,
  source VARCHAR(32) NOT NULL DEFAULT 'system',
  note VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_order_events_order (order_id),
  CONSTRAINT fk_ecommerce_order_events_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  product_slug VARCHAR(191) NOT NULL,
  title VARCHAR(255) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price_try DECIMAL(12, 2) NOT NULL,
  line_total_try DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_ecommerce_order_items_order (order_id),
  CONSTRAINT fk_ecommerce_order_items_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_order_addresses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  kind ENUM('billing', 'shipping') NOT NULL DEFAULT 'shipping',
  full_name VARCHAR(255) NOT NULL,
  phone VARCHAR(64) NOT NULL,
  email VARCHAR(255) NOT NULL,
  national_id VARCHAR(11) NOT NULL DEFAULT '',
  country VARCHAR(64) NOT NULL DEFAULT 'TR',
  city VARCHAR(128) NOT NULL,
  district VARCHAR(128) NOT NULL DEFAULT '',
  address_line TEXT NOT NULL,
  postal_code VARCHAR(16) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY uq_ecommerce_order_addr (order_id, kind),
  CONSTRAINT fk_ecommerce_order_addr_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(32) NOT NULL DEFAULT 'iyzico',
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  amount_try DECIMAL(12, 2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'TRY',
  provider_payment_id VARCHAR(128) NULL DEFAULT NULL,
  provider_transaction_id VARCHAR(128) NULL DEFAULT NULL,
  raw_payload_json JSON NULL,
  idempotency_key VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ecommerce_payment_idempotency (idempotency_key),
  KEY idx_ecommerce_payments_order (order_id),
  CONSTRAINT fk_ecommerce_payments_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_stock_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id INT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL DEFAULT NULL,
  qty_delta INT NOT NULL,
  reason ENUM('sale', 'refund', 'adjust', 'reserve', 'release') NOT NULL,
  note VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_stock_product (product_id),
  KEY idx_ecommerce_stock_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_coupons (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(32) NOT NULL,
  percent_off TINYINT UNSIGNED NOT NULL,
  max_uses INT UNSIGNED NULL DEFAULT NULL,
  uses_count INT UNSIGNED NOT NULL DEFAULT 0,
  valid_from DATE NULL DEFAULT NULL,
  valid_to DATE NULL DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ecommerce_coupon_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_shipments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  carrier VARCHAR(128) NULL DEFAULT NULL,
  tracking_number VARCHAR(128) NULL DEFAULT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_shipments_order (order_id),
  CONSTRAINT fk_ecommerce_shipments_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_refunds (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  payment_id BIGINT UNSIGNED NOT NULL,
  amount_try DECIMAL(12, 2) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  reason VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_refunds_order (order_id),
  CONSTRAINT fk_ecommerce_refunds_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_ecommerce_refunds_payment FOREIGN KEY (payment_id) REFERENCES ecommerce_payments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
