USE cashless_db;

CREATE TABLE IF NOT EXISTS payment_checkouts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference_id VARCHAR(50) NOT NULL UNIQUE,
  customer_id INT UNSIGNED DEFAULT NULL,
  order_id INT UNSIGNED DEFAULT NULL UNIQUE,
  provider_request_id VARCHAR(80) DEFAULT NULL UNIQUE,
  provider_payment_id VARCHAR(80) DEFAULT NULL UNIQUE,
  provider_business_id VARCHAR(80) DEFAULT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'PHP',
  status ENUM('initiating','pending','paid','failed','canceled','expired','unknown') NOT NULL DEFAULT 'initiating',
  payment_url VARCHAR(2048) DEFAULT NULL,
  checkout_data JSON DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_checkouts_customer FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_payment_checkouts_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_payment_checkouts_customer_status (customer_id, status, created_at),
  INDEX idx_payment_checkouts_status_updated (status, updated_at)
) ENGINE=InnoDB;