-- Production checkout request lifecycle.
-- Distinct from the demo checkout_sessions table.

CREATE TABLE IF NOT EXISTS checkout_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_code VARCHAR(40) NOT NULL UNIQUE,
  idempotency_key VARCHAR(128) NULL UNIQUE,
  organization_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  created_by_user_id BIGINT UNSIGNED NOT NULL,
  payer_account_id BIGINT UNSIGNED NOT NULL,
  payer_payment_method_id BIGINT UNSIGNED NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  subtotal DECIMAL(12, 2) NOT NULL,
  tax DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  tip DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  discount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  total DECIMAL(12, 2) NOT NULL,
  status ENUM(
    'draft',
    'pending_delivery',
    'delivered',
    'pending_approval',
    'approved',
    'declined',
    'processing',
    'settled',
    'failed',
    'refunded',
    'expired',
    'canceled'
  ) NOT NULL DEFAULT 'draft',
  processor_authorization_code VARCHAR(255) NULL,
  processor_transaction_id VARCHAR(255) NULL,
  decline_reason VARCHAR(255) NULL,
  merchant_notes TEXT NULL,
  expires_at DATETIME NULL,
  approved_at DATETIME NULL,
  settled_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES merchant_organizations(id),
  FOREIGN KEY (location_id) REFERENCES merchant_locations(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by_user_id) REFERENCES merchant_users(id),
  FOREIGN KEY (payer_account_id) REFERENCES payer_accounts(id),
  FOREIGN KEY (payer_payment_method_id) REFERENCES payer_payment_methods(id) ON DELETE SET NULL,
  INDEX idx_cr_org (organization_id),
  INDEX idx_cr_payer (payer_account_id),
  INDEX idx_cr_status (status),
  INDEX idx_cr_created (created_at)
);

CREATE TABLE IF NOT EXISTS checkout_request_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  checkout_id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(180) NOT NULL,
  description VARCHAR(255) NULL,
  quantity DECIMAL(10, 3) NOT NULL,
  unit_price DECIMAL(12, 2) NOT NULL,
  line_total DECIMAL(12, 2) NOT NULL,
  FOREIGN KEY (checkout_id) REFERENCES checkout_requests(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payment_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  checkout_id BIGINT UNSIGNED NOT NULL,
  payer_payment_method_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(12, 2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  status ENUM('pending','authorized','settled','failed','voided') NOT NULL DEFAULT 'pending',
  processor VARCHAR(40) NOT NULL DEFAULT 'stripe',
  processor_payment_intent_id VARCHAR(255) NULL,
  processor_charge_id VARCHAR(255) NULL,
  processor_response JSON NULL,
  failure_code VARCHAR(60) NULL,
  failure_message VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (checkout_id) REFERENCES checkout_requests(id),
  FOREIGN KEY (payer_payment_method_id) REFERENCES payer_payment_methods(id),
  INDEX idx_pa_checkout (checkout_id)
);

CREATE TABLE IF NOT EXISTS refunds (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  checkout_id BIGINT UNSIGNED NOT NULL,
  payment_attempt_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(12, 2) NOT NULL,
  reason ENUM('duplicate','fraudulent','requested_by_customer','other') NOT NULL DEFAULT 'other',
  notes TEXT NULL,
  status ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
  processor_refund_id VARCHAR(255) NULL,
  initiated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (checkout_id) REFERENCES checkout_requests(id),
  FOREIGN KEY (payment_attempt_id) REFERENCES payment_attempts(id),
  FOREIGN KEY (initiated_by) REFERENCES merchant_users(id) ON DELETE SET NULL
)
