-- Mobile prototype additions for shopping checklist and offline sync
CREATE TABLE IF NOT EXISTS shopping_checks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  projected_item_id INT NOT NULL,
  month_key VARCHAR(7) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  actual_amount DECIMAL(12,2) NULL,
  quantity DECIMAL(12,2) NULL,
  notes TEXT NULL,
  expense_id INT NULL,
  checked_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_projected_item (projected_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS offline_sync_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payload JSON NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
